Visual Basic Editor

Ce tutoriel montre comment manipuler l'éditeur de macros Excel ... par macro.
Vous y trouverez quelques méthodes pour gérer dynamiquement:
     Les références et les macros complémentaires
     Les modules
     Les macros
     Les objets (feuilles, UserForm, contrôles...)

Toutes les procédures de ce document ont été testées en utilisant Excel2002.

Article lu fois.

L'auteur

SilkyRoad

L'article

Publié le 12 septembre 2006 - Mis à jour le 18 juin 2007

Version PDF Version hors-ligne

ePub, Azw et Mobi

Liens sociaux

I. Introduction▲

L'éditeur de macros, aussi appelé Visual Basic Editor ou VBE, est l'environnement dans lequel vous pouvez modifier les macros que vous avez enregistré, écrire de nouvelles macros et de nouveaux programmes VBA.

L'éditeur VBE peut être modifié dynamiquement par macro. C'est l'objet de ce tutoriel. Vous pouvez de cette manière automatiser:
* L'activation des références (Librairies).
* L'importation et l'exportation des modules.
* La création, modification ou suppression des macros dans vos classeurs.
* La création des objets par macros et leur associer des procédures évènementielles.
* Le déclenchement de macros.

La majeur partie des procédures utilisées nécessite d'activer la référence Microsoft Visual Basic for Applications Extensibility 5.3.

Dans l'éditeur de macros (Alt+F11):
Menu Outils
Références
Cochez la ligne "Microsoft Visual Basic for Applications Extensibility 5.3"
Cliquez sur "OK" pour valider.

II. Informations générales▲

II-A. Quelques manipulations de base ▲

Pour commencer, voici quelques exemples simples pour manipuler l'éditeur de macros.

Récupérer la version VBE installée.

Vba

Sélectionnez

Sub afficherLaVersionVBE()
MsgBox Application.VBE.Version
End Sub

Ouvrir l'éditeur de macros (L'équivalent de Alt+F11).

Vba

Sélectionnez

Sub OuvreVBA()
Application.VBE.MainWindow.Visible = True
End Sub

Fermer l'éditeur de macros.

Vba

Sélectionnez

Sub FermeVBA()
Application.VBE.MainWindow.Visible = False
End Sub

Modifier le nom du projet (Le nom par défaut est "VBAProject").

Vba

Sélectionnez

Application.VBE.ActiveVBProject.Name = "MonProjet"

II-B. La propriété VBComponents▲

La propriété VBComponents renvoie la collection de composants contenue dans un projet.
* ThisWorkbook.
* L'ensemble des feuilles (CodeName).
* L'ensemble des modules Standards et modules de Classe.
* Les UserForm.

Voici deux méthodes pour lister les composants du classeur actif.

Vba

Sélectionnez

Sub boucleVBComponents_V01()
    Dim i As Integer
    
    For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
        Debug.Print ActiveWorkbook.VBProject.VBComponents(i).Name
    Next
End Sub

Vba

Sélectionnez

Sub boucleVBComponents_V02()
    Dim VBComp As VBComponent
    
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        Debug.Print VBComp.Name
    Next
End Sub

Remarque:
Lorsque vous devez spécifier le nom d'une feuille dans la propriété VBComponents, il s'agit du CodeName et pas le nom de l'onglet.
Image non disponible

II-C. La propriété CodeModule▲

La propriété CodeModule permet de modifier, ajouter, supprimer ou renvoyer des informations sur le contenu des procédures, pour chacun des composants.

Un exemple pour compter les lignes de macros dans chaque composant du classeur actif.

Vba

Sélectionnez

    Dim VBComp As VBComponent
    Dim Mdl As CodeModule
    
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        Set Mdl = VBComp.CodeModule
        Debug.Print VBComp.Name & ": " & Mdl.CountOfLines
    Next

II-D. Automatiser la numérotation des lignes▲

La fonction ERL permet de récupérer le numéro de ligne qui a provoqué une erreur à condition d'avoir préalablement numéroté les lignes dans l'éditeur de macros.

L'exemple suivant renvoie 8.

Vba

Sélectionnez

Option Explicit
Sub laProcedure()
4 Dim x As Integer

6 On Error GoTo errHandler

8 x = 5 / 0 'provoque une erreur (division par zéro)

10 Exit Sub
11 errHandler:
12 MsgBox "Une erreur est survenue , Ligne : " & Erl() & _
    vbCrLf & "Numéro d'erreur : " & Err.Number & vbCrLf & Err.Description
14 End Sub

Remarque:
* Si la ligne x = 5 / 0 n'est pas numérotée, la fonction ERL renvoie le numéro de la derniere ligne précédant l'erreur (6).
* ERL renvoie 0 s'il n'y a pas d'erreur dans la procédure.
* ERL renvoie 0 si aucune ligne n'est numérotée dans la macro.

Vous allez me dire, Comment faire pour numéroter rapidement les lignes d'une macro et ne pas être obligé de le faire manuellement?

Vous pouvez installer l'Add-In MZ-Tools qui fait cela très bien.

Sinon, juste pour le fun, la procédure suivante Numérote les lignes de la macro "laProcedure" dans le "Module1".

Vba

Sélectionnez

Option Explicit
Option Compare Text

Sub testAjout()
'"laProcedure" est la macro dont vous souhaitez numéroter les lignes
NumerotationLignesProcedure "Module1", "laProcedure"
End Sub


Sub NumerotationLignesProcedure(nomModule As String, nomMacro As String)
'
'Cet exemple ne gère pas les erreurs s'il existe déja une numérotation
'
Dim Debut As Integer, Lignes As Integer, x As Integer
Dim Texte As String, strVar As String

With ThisWorkbook.VBProject.VBComponents(nomModule).CodeModule
    Debut = .ProcStartLine(nomMacro, 0)
    Lignes = .ProcCountLines(nomMacro, 0)
End With

For x = Debut + 2 To Debut + Lignes - 1
    With ThisWorkbook.VBProject.VBComponents(nomModule).CodeModule
        Texte = .Lines(x, 1)
        strVar = Application.WorksheetFunction.Substitute(Texte, " ", "")
        strVar = Application.WorksheetFunction.Substitute(strVar, vbCrLf, "")
        strVar = Application.WorksheetFunction.Substitute(strVar, vbTab, "")
        
        'Adaptez les filtres en fonction de vos projets.
        'Remarque: les arguments PrivateFunction et PublicFunction sont volontairement accolés.
        '
        If strVar <> "" And _
        Left(strVar, 3) <> "Sub" And _
        Left(strVar, 10) <> "PrivateSub" And _
        Left(strVar, 9) <> "PublicSub" And _
        Left(strVar, 8) <> "Function" And _
        Left(strVar, 15) <> "PrivateFunction" And _
        Left(strVar, 14) <> "PublicFunction" And _
        Right(ThisWorkbook.VBProject.VBComponents(nomModule). _
                CodeModule.Lines(x - 1, 1), 1) <> "_" _
        Then .ReplaceLine x, x & " " & Texte
    End With
Next
End Sub

Et une procédure pour supprimer la numérotation:

Vba

Sélectionnez

Sub testSuppression()
supprimeNumerotationLignes "Module1", "laProcedure"
End Sub


Sub supprimeNumerotationLignes(nomModule As String, nomMacro As String)
Dim Debut As Integer, Lignes As Integer, x As Integer
Dim Texte As String, strVar As String
Dim Valeur As Integer

With ThisWorkbook.VBProject.VBComponents(nomModule).CodeModule
    Debut = .ProcStartLine(nomMacro, 0)
    Lignes = .ProcCountLines(nomMacro, 0)
End With

For x = Debut + 2 To Debut + Lignes - 1
    With ThisWorkbook.VBProject.VBComponents(nomModule).CodeModule
        Texte = .Lines(x, 1)
        
            Valeur = Val(Texte)
            If Valeur <> 0 Then
                strVar = Mid(Texte, Len(CStr(Valeur)) + 2)
                Else
                strVar = Texte
            End If
            
            .ReplaceLine x, strVar
    End With
Next
End Sub

III. Les références et macros complémentaires▲

III-A. Les références▲

Ce chapitre montre comment manipuler les références par macros.
Les références, aussi appelées bibliothèques ou librairies, offrent de nombreuses possibilités lorsqu'elles sont activées.

Quelques exemples d'utilisation parmi les nombreuses bibliothèques disponibles:
* Le modèle ADO (ActiveX Data Objects) pour manipuler les bases de données.
* La librairie DSO pour lire et modifier les propriétés des documents Office.
* La librairie Windows Image Acquisition pour manipuler les images et gérer les WebCams.
* ...etc...
Vous pouvez aussi utiliser les références pour piloter d'autres applications: par exemple Word, Windows Media Player, Outlook, Power Point...

Un exemple de procédure pour lister les références actives.

Vba

Sélectionnez

Dim Ref As Reference
For Each Ref In ThisWorkbook.VBProject.References
    Debug.Print Ref.Name 'Nom
    Debug.Print Ref.FullPath 'Chemin complet
    Debug.Print Ref.Description 'Description de la référence
    Debug.Print Ref.IsBroken 'Indique si la référence est manquante
    Debug.Print Ref.Major & "." & Ref.Minor 'Version
    Debug.Print "---"
Next Ref

Ajouter une référence dans votre projet.

Vba

Sélectionnez

'Ajoute la référence Outlook pour OfficeXP
Dim x As String
x = "C:\Program Files\Microsoft Office\Office10\MSOUTL.OLB"
ThisWorkbook.VBProject.References.AddFromFile x

Désactiver les références manquantes.

Vba

Sélectionnez

Dim Ref As Reference

'La procédure boucle sur la collection de références et supprime celles qui sont
'spécifiées manquantes.  
For Each Ref In ThisWorkbook.VBProject.References
    If Ref.IsBroken = True Then _
        ThisWorkbook.VBProject.References.Remove Ref
Next Ref

III-B. Les macros complémentaires▲

Une macro complémentaire (.xla), aussi appelée AddIn, est un classeur Excel dont les feuilles sont masquées et que vous pouvez charger dès l'ouverture d'Excel. Les classeurs .xla sont principalement utilisés pour stocker les procédures et les fonctions VBA personnelles que vous utilisez régulièrement dans Excel.

Un exemple pour automatiser l'installation d'une macro complémentaire.

Vba

Sélectionnez

Sub installationMacroComplementaire()
    Dim oAddIn As AddIn
    
    'L'argument TRUE: de type Variant facultatif. Ignoré si le fichier complémentaire est
    'sur le disque dur. Affectez-lui la valeur True pour copier la macro complémentaire sur
    'votre disque dur si elle est sur support amovible (disquette ou CD-ROM).
    'Affectez-lui la valeur False pour la laisser sur le support amovible.
    'Si vous ne spécifiez pas cet argument, une boîte de dialogue s'affiche et vous
    'demande de choisir.
    Set oAddIn = Application.AddIns.Add("F:\leFichier.xla", True)
    oAddIn.Installed = True
End Sub

Lister les macros complémentaires.

Vba

Sélectionnez

Sub listeMacrosComplementaires()
    Dim i As Integer
    
    For i = 1 To Application.AddIns.Count
        Debug.Print Application.AddIns(i).Title
        Debug.Print Application.AddIns(i).Name
        Debug.Print Application.AddIns(i).FullName
        Debug.Print Application.AddIns(i).Installed
        Debug.Print "---"
    Next
End Sub

Vérifier si une macro complémentaire est installée.

Vba

Sélectionnez

Option Explicit
Option Compare Text

Sub controler_Si_MacroComplementaire_Installee()
    Dim X As AddIn
    Dim laMacro As String
    
    laMacro = "solver.xla"
    
    For Each X In Application.AddIns
        If laMacro = X.Name Then
            If X.Installed = True Then
                MsgBox "la macro complémentaire " & laMacro & " est installée."
                Else
                MsgBox "la macro complémentaire " & laMacro & " n'est pas installée."
            End If
            Exit Sub
        End If
    Next X
    
    MsgBox "la macro complémentaire " & laMacro & " n'a pas été trouvée."
End Sub

Imprimer la Feuil1 d'un classeur xla.

Vba

Sélectionnez

With Workbooks("Test.xla")
    .IsAddin = False
    .Worksheets("Feuil1").PrintOut Copies:=1, Collate:=True
    .IsAddin = True
End With

Activer l'utilitaire d'analyse (Funcres.xla) et l'utilitaire d'analyse-VBA (atpvbaen.xls)

Vba

Sélectionnez

Dim oAddIn As AddIn

Set oAddIn = Application.AddIns.Add _
    (Filename:=Application.LibraryPath & "\analyse\analys32.xll")
oAddIn.Installed = True
Application.RegisterXLL "Analys32.xll"

Workbooks.Open Application.LibraryPath & "\analyse\atpvbaen.xla"
AddIns("Utilitaire d'analyse - VBA").Installed = True

IV. Gérer les modules et les procédures▲

Ce chapitre propose des exemples pour manipuler les modules et les procédures contenus dans les classeurs.

IV-A. Créer▲

Créer un nouveau module, le renommer et y insérer une macro.

Vba

Sélectionnez

Sub creationModule()
    'Nécéssite d'activer la référence
    '"Visual basic For Application Extensibility 5.3"
    '
    Dim Wb As Workbook
    Dim VBComp As VBComponent
    Dim X As Integer
    
    'Définit le classeur cible
    Set Wb = Workbooks("Classeur1.xls")
    
    'Ajoute un module standard dans le classeur
    Set VBComp = Wb.VBProject.VBComponents.Add(1)
    'Renomme le module
    VBComp.Name = "NouveauModule"
    
    'Ajoute une macro dans le module
    With VBComp.CodeModule
        X = .CountOfLines
        .InsertLines X + 1, "Sub laMacro()"
        .InsertLines X + 2, "Range(""A1"").Value = ""Coucou"""
        .InsertLines X + 3, "End Sub"
    End With
End Sub

Utilisez Wb.VBProject.VBComponents.Add(2) pour créer un module de classe.

Utilisez Wb.VBProject.VBComponents.Add(3) pour créer un UserForm.

Créer une macro dynamiquement.
Cet exemple permet d'ajouter une procédure évènementielle SelectionChange dans la Feuil1 du classeur actif.

Vba

Sélectionnez

Sub creationMacro()
Dim X As Integer

With ActiveWorkbook.VBProject.VBComponents("Feuil1").CodeModule
    X = .CountOfLines
    .InsertLines X + 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
    
    'Chr(38) permet d'insérer le symbole & dans la procédure.
    .InsertLines X + 2, _
        "MsgBox ""La cellule sélectionnée: """ & Chr(38) & " Target.Address,,""Message"" "
    .InsertLines X + 3, "End Sub"
End With
End Sub

Un autre exemple complet:
Récupérer toutes les procédures d'un classeur endommagé.

IV-B. Lire▲

Lister toutes les procédures d'un classeur dans la feuille de calcul.

Vba

Sélectionnez

Sub listeMacros()
'Nécéssite d'activer la référence
'"Visual basic For Application Extensibility 5.3"
'
Dim i As Integer, Ajout As Integer, x As Integer
Dim Msg As String
Dim VBCmp As VBComponent
Dim Wb As Workbook

'Indiquez le nom du classeur ouvert
'Set Wb = ThisWorkbook
Set Wb = Workbooks("Classeur1.xls")

Ajout = 1
For Each VBCmp In Wb.VBProject.VBComponents
    Msg = VBCmp.Name
    
    With Cells(Ajout, 1)
        .Interior.ColorIndex = 6
        .Value = Msg
    End With
    
    x = Wb.VBProject.VBComponents(Msg).CodeModule.CountOfLines
    For i = 1 To x
        Cells(Ajout + i, 1) = _
            Wb.VBProject.VBComponents(Msg).CodeModule.Lines(i, 1)
    Next
    
    Ajout = Ajout + x + 2
Next VBCmp
End Sub

Afficher le contenu d'une macro précise.

Vba

Sélectionnez

Sub Test()
'Nécéssite d'activer la référence
'"Visual basic For Application Extensibility 5.3"
'
'Affiche la macro "testMacro" contenue dans la Module1 du Classeur1.xls
MsgBox recupereContenuMacro(Workbooks("Classeur1.xls"), "Module1", "testMacro")

'exemple pour récupérer la procédure évènementielle "Workbook_Open" dans le module objet ThisWorkbook:
MsgBox recupereContenuMacro(Workbooks("Classeur1.xls"), "ThisWorkbook", "Workbook_Open")
End Sub


Function recupereContenuMacro(Wb As Workbook, Mdl As String, NomMacro As String)
    Dim Debut As Integer, Fin As Integer, i As Integer
    Dim Resultat As String
    
    With Wb.VBProject.VBComponents(Mdl).CodeModule
        Debut = .ProcStartLine(NomMacro, vbext_pk_Proc)
        Fin = .ProcCountLines(NomMacro, vbext_pk_Proc) + Debut
            For i = Debut To Fin
                Resultat = Resultat & .Lines(i, 1) & vbCr
            Next
    End With
    
    recupereContenuMacro = Resultat
End Function

Vérifier l'existence d'un module dans le classeur.

Vba

Sélectionnez

Sub Test()
    'Nécéssite d'activer la référence
    '"Visual basic For Application Extensibility 5.3"
    '
    MsgBox VerifierExistenceModule(Workbooks("NomClasseur.xls"), "Module2")
End Sub


Function VerifierExistenceModule(Wb As Workbook, Mdl As String) As Boolean
    Dim VBComp As VBComponent
    
    On Error Resume Next
    Set VBComp = Wb.VBProject.VBComponents(Mdl)
    
    If VBComp Is Nothing Then
        VerifierExistenceModule = False
        Else
        VerifierExistenceModule = True
    End If
End Function

Vérifier l'existence d'une macro dans le classeur.

Vba

Sélectionnez

Sub Test()
    'Nécéssite d'activer la référence
    '"Visual basic For Application Extensibility 5.3"
    '
    MsgBox VerifierExistenceMacro(Workbooks("NomClasseur.xls"), "testMacro")

End Sub


Function VerifierExistenceMacro(Wb As Workbook, NomMacro As String) As Boolean
    Dim x As Integer
    Dim vbcomp As VBComponent
    
    For Each vbcomp In Wb.VBProject.VBComponents
        On Error Resume Next
        x = vbcomp.CodeModule.ProcStartLine(NomMacro, 0)
        
        If x > 0 Then Exit For
    Next vbcomp
    
    If x = 0 Then
        VerifierExistenceMacro = False
        Else
        VerifierExistenceMacro = True
    End If
    
End Function

IV-C. Modifier▲

Remplacer un mot dans toutes les procédures d'un classeur.
Cet exemple remplace la chaîne "Feuil1" par "Feuil3".

Vba

Sélectionnez

Sub RemplacementMotDansProcedure()
    'Nécéssite d'activer la référence
    '"Visual basic For Application Extensibility 5.3"
    '
    Dim Ancien As String, Nouveau As String, Cible As String
    Dim VBComp As VBComponent
    Dim i As Integer
    Dim Wb As Workbook
    
    Set Wb = Workbooks("NomClasseur.xls")
    
    Ancien = "Feuil1"
    Nouveau = "Feuil3"
    
    For Each VBComp In Wb.VBProject.VBComponents
        For i = 1 To VBComp.CodeModule.CountOfLines
            Cible = VBComp.CodeModule.Lines(i, 1)
            Cible = Replace(Cible, Ancien, Nouveau)
            VBComp.CodeModule.ReplaceLine i, Cible
        Next i
    Next VBComp
End Sub

Renommer un module.

Vba

Sélectionnez

Sub Test()
    Dim Wb As Workbook
    
    Set Wb = Workbooks("NomClasseur.xls")
    RenommeModule Wb, "Module1", "NouveauNom"
End Sub


Sub RenommeModule(Wb As Workbook, Ancien As String, Nouveau As String)
    Dim VBComp As VBComponent
    
    Set VBComp = Wb.VBProject.VBComponents(Ancien)
    VBComp.Name = Nouveau
End Sub

Remplacer une macro dans tous les modèles Word .DOT d'un répertoire.
(Procédure VBA Word).

Vba

Sélectionnez

Sub remplacement_Macro_WordDot() 
'http://www.developpez.net/forums/viewtopic.php?p=2011706#2011706
Dim Debut As Integer, Lignes As Integer, X As Integer 
Dim Fichier As String, Direction As String 
Dim Doc As Document 

Application.ScreenUpdating = False 

'boucle sur tous les fichiers .dot du repertoire 
Direction = "C:\Documents and Settings\michel\dossier\general\excel" 
Fichier = Dir(Direction & "\*.dot") 
Do While Fichier <> "" 

Set Doc = Documents.Open(Direction & "\" & Fichier) 

    'suppression de la macro nommée "essai" dans Module1 
    With Doc.VBProject.VBComponents("Module1").CodeModule 
    Debut = .ProcStartLine("essai", 0) 
    Lignes = .ProcCountLines("essai", 0) 
    .DeleteLines Debut, Lignes 
    End With 
    
    'ajoute la macro nommée "MaNouvelleMacro" dans Module1 
    With Doc.VBProject.VBComponents("Module1").CodeModule 
    X = .CountOfLines 
    .InsertLines X + 1, "Sub MaNouvelleMacro()" 
    .InsertLines X + 2, "MsgBox ""Coucou"",VBinformation " 
    .InsertLines X + 3, "End Sub" 
    End With 
    
DoEvents 
Doc.Close True 
Set Doc = Nothing 

Fichier = Dir 
Loop 
Application.ScreenUpdating = True 
End Sub

IV-D. Supprimer▲

Supprimer le module nommé "Module2" dans le classeur contenant cette macro.

Vba

Sélectionnez

Sub supprimerUnModule()
    With ThisWorkbook.VBProject.VBComponents
        .Remove .Item("Module2")
    End With
End Sub

Supprimer tous les modules vides dans le classeur actif.

Vba

Sélectionnez

Sub supprimerTousModulesVides()
    Dim vbComp As VBComponent
    Dim i As Integer, j As Integer
    
    For Each vbComp In ActiveWorkbook.VBProject.VBComponents
        If vbComp.Type = 1 Then
            i = vbComp.CodeModule.CountOfDeclarationLines + 1
            j = vbComp.CodeModule.CountOfLines
            If j < i Then ActiveWorkbook.VBProject.VBComponents.Remove vbComp
        End If
    Next
End Sub

Sauvegarder le classeur contenant cette macro, puis supprimer la totalité des procédures.
La macro "SupprimeTout" est aussi détruite.

Vba

Sélectionnez

Sub SupprimeTout()
    'Enregistre le classeur et supprime la totalité des procédures
    Dim VbComp As VBComponent
    
    'Enregistre le classeur
    ThisWorkbook.SaveAs "C:\Sauvegarde.xls"
    
    For Each VbComp In ThisWorkbook.VBProject.VBComponents
        Select Case VbComp.Type
            Case 1 To 3
                ThisWorkbook.VBProject.VBComponents.Remove VbComp
            Case Else
                With VbComp.CodeModule
                .DeleteLines 1, .CountOfLines
                End With
        End Select
    Next VbComp
    
    'Sauvegarde les modifications
    ThisWorkbook.Save
End Sub

Supprimer une macro précise nommée "Macro1" dans le "Module3".

Vba

Sélectionnez

Sub test()
    'Nécéssite d'activer la référence
    '"Visual basic For Application Extensibility 5.3"
    '
    Dim Wb As Workbook
    
    Set Wb = Workbooks("Classeur1.xls")
    SupprimerMacroPrecise Wb, "Module3", "Macro1"

    'Un autre exemple pour supprimer un procédure évènementielle
    'SupprimerMacroPrecise Wb, "ThisWorkbook", "Workbook_Open"

End Sub


Sub SupprimerMacroPrecise(Wb As Workbook, Mdl As String, NomMacro As String)
    Dim Debut As Integer, Lignes As Integer
    
    With Wb.VBProject.VBComponents(Mdl).CodeModule
        Debut = .ProcStartLine(NomMacro, 0)
        Lignes = .ProcCountLines(NomMacro, 0)
        .DeleteLines Debut, Lignes
    End With
End Sub

IV-E. Importer et Exporter▲

Excel possède une méthode d'export pour enregistrer les composants dans des fichiers séparés: au format .bas pour les modules et .frm pour les UserForm. Vous pouvez ensuite utiliser la méthode d'import pour réutiliser les composants dans d'autres classeurs.

L'exemple suivant exporte 3 modules et 1 UserForm du classeur contenant la macro. Ensuite la procédure boucle sur tous les classeurs d'un répertoire cible afin d'y importer ces modules et UserForm.

Vba

Sélectionnez

Sub Export_Import_Module_Et_Userform()
    Dim Fichier As String, Repertoire As String
    Dim Wb As Workbook
    Dim i As Byte
    
    Application.ScreenUpdating = False
    
    '-----------------------------------
    'Export du UserForm1 et de 3 Modules nommés Module1, Module2 et Module3
    'qui sont dans le classeur contenant cette macro.
    ThisWorkbook.VBProject.VBComponents("UserForm1").Export "C:\copieUSF.frm"
    
    For i = 1 To 3
    ThisWorkbook.VBProject.VBComponents("Module" & i).Export "C:\copieModule" & i & ".bas"
    Next i
    '-----------------------------------
    
    
    'Adaptez le répertoire des classeurs à modifier
    Repertoire = "C:\Documents and Settings\michel\dossier"
    Fichier = Dir(Repertoire & "\*.xls")
    
    'Boucle sur les classeurs du répertoire cible
    Do While Fichier <> ""
        Set Wb = Workbooks.Open(Repertoire & "\" & Fichier)
        
        '----------------------------------
        'La procédure ne gère pas les erreurs si le nom des modules existe déja
        'dans les classeurs.
        With Wb.VBProject 'Transfert l'USF et les modules dans les classeurs
            .VBComponents.Import "C:\copieUSF.frm"
            
            For i = 1 To 3
                .VBComponents.Import "C:\copieModule" & i & ".bas"
            Next i
        End With
        
        Wb.Close True
        Fichier = Dir
    Loop
    
    Application.ScreenUpdating = True
    
    MsgBox "Opération terminée."
End Sub

L'exemple suivant montre comment importer et remplacer les procédures du module ThisWorkbook.

Il est possible de sauvegarder la procédure d'un module ThisWorkbook en l'enregistrant sur le disque dur (extension de fichier .cls). Vous pouvez ensuite réimporter ce fichier dans un autre classeur, pour par exemple effectuer la mise d'une procédure. Lors de l'import, le fichier va être créé comme un module de classe et non en lieu et place du module ThisWorkbook.

La procédure ci dessous permet d'automatiser le chargement du fichier .cls et le remplacement des macros dans le module objet ThisWorkbook.

Vba

Sélectionnez

'Nécessite d'activer la référence "Microsoft Visual Basic for Applications Extensibility 5.3"
Dim Wb As Workbook
Dim oModule As CodeModule
Dim VbComp As VBComponent
Dim x As Integer
Dim Cible As String

Cible = "NomModule"
'Définit le classeur de destination (qui doit être préalablement ouvert).
Set Wb = Workbooks("NomClasseur.xls")

'Charge le module dans le classeur
Set VbComp = Wb.VBProject.VBComponents.Import("C:\ThisWorkbook.cls")
'Le renomme (pour le supprimer plus facilement ultérieurement
VbComp.Name = Cible

Set oModule = VbComp.CodeModule

'Transfère les données chargées dans ThisWorkbook.
'Attention les données existantes dans "ThisWorkbook" sont écrasées.
With Wb.VBProject.VBComponents("ThisWorkbook").CodeModule
    x = .CountOfLines
    .DeleteLines 1, x
    .InsertLines 1, oModule.Lines(1, oModule.CountOfLines)
End With

'Suppression du module précédemment chargé
With Wb.VBProject.VBComponents
    .Remove .Item(Cible)
End With

V. Gérer les objets▲

Les objets peuvent être les feuilles de calcul, les UserForm, les contrôles...

Ce chapitre montre comment manipuler les objets et leurs propriétés. Vous y trouverez aussi deux méthodes pour associer des procédures évènementielles à ces objets:
* En "dur": Ecriture des nouvelles macros directement dans l'éditeur VBE, comme cela a été vu dans le chapitre IV-A.
* En utilisant un module de classe.

V-A. Créer▲

Il est parfois utile de créer des objets dynamiquement, par exemple pour afficher une série de contrôles dont vous ne connaissez pas à l'avance le nombre nécessaire.

Voici une solution pour ajouter une série de CheckBox dynamiquement dans un UserForm, et gérer les évènements de ces nouveaux contrôles à partir d'un module de classe.

Vba

Sélectionnez

'--------------------------------------
'Procédure de création des CheckBoxs à placer dans l'UserForm.
'L'UserForm doit préalablement contenir un bouton nommé CommandButton1

Option Explicit

Private Sub CommandButton1_Click()
Dim Obj As Control
Dim Cl As Classe1
Dim i As Integer

Set Collect = New Collection

For i = 1 To 3 'boucle pour la création des CheckBox
    Set Obj = Me.Controls.Add("forms.Checkbox.1")
    With Obj
        .Name = "moncheckbox" & i
        .Object.Caption = "le texte" & i
        .Left = 140
        .Top = 30 * i + 10
        .Width = 50
        .Height = 20
    End With
    
    'ajout de l'objet dans la classe
    Set Cl = New Classe1
    Set Cl.ChkBx = Obj
    Collect.Add Cl
Next i

End Sub
'--------------------------------------

Vba

Sélectionnez

'--------------------------------------
'A placer dans un module standard
Option Explicit

Public Collect As Collection
'--------------------------------------

Vba

Sélectionnez

'--------------------------------------
'A placer dans un module de classe nommé "Classe1"
'
Option Explicit

Public WithEvents ChkBx As MSForms.CheckBox

'Exemple pour gérer l'évènement clic sur les objets type CheckBox
Private Sub ChkBx_Click()
    'cet exemple affiche le nom et la valeur de l'objet cliqué
    MsgBox ChkBx.Name & ": " & ChkBx.Value
End Sub
'--------------------------------------

Cet exemple montre comment créer un UserForm et une ListBox par macro ainsi qu'une macro évènementielle "Click" pour la ListBox.

Vba

Sélectionnez

Option Explicit
Dim Usf As Object

Sub lancementProcedure()
Dim X As Object
Dim i As Integer
Dim strList As String

strList = "ListBox1"
Set X = creationUserForm_Et_listBox_Dynamique(strList)

For i = 1 To 10
X.Controls(strList).AddItem "Donnee " & i
Next i

X.Show

ThisWorkbook.VBProject.VBComponents.Remove Usf
Set Usf = Nothing
End Sub


Function creationUserForm_Et_listBox_Dynamique(nomListe As String) As Object
Dim ObjListBox As Object
Dim j As Integer

  Set Usf = ThisWorkbook.VBProject.VBComponents.Add(3)
  With Usf
    .Properties("Caption") = "Mon UserForm"
    .Properties("Width") = 300
    .Properties("Height") = 200
  End With
  
Set ObjListBox = Usf.Designer.Controls.Add("Forms.ListBox.1")
      
With ObjListBox
    .Left = 20: .Top = 10: .Width = 90: .Height = 140
    .Name = nomListe
    .Object.ColumnCount = 1
    .Object.ColumnWidths = 70
End With

With Usf.CodeModule
    j = .CountOfLines
    .InsertLines j + 1, "Sub " & nomListe & "_Click()"
    .InsertLines j + 2, "If Not " & nomListe & ".ListIndex = -1 Then MsgBox " & nomListe
    .InsertLines j + 3, "End Sub"
End With

VBA.UserForms.Add (Usf.Name)
Set creationUserForm_Et_listBox_Dynamique = UserForms(UserForms.Count - 1)
End Function

Cet exemple permet de créer une nouvelle feuille de calcul et d'y ajouter un bouton.
La procédure associée au nouveau bouton supprime le contenu des cellules.

Vba

Sélectionnez

Dim Ws As Worksheet
Dim Obj As OLEObject
Dim laMacro As String
Dim x As Integer

Set Ws = Sheets.Add 'Ajoute une nouvelle feuille
    
'ajoute un CommandButton dans la nouvelle feuille
Set Obj = Ws.OLEObjects.Add("Forms.CommandButton.1")
With Obj
    .Name = "monBouton" 'renomme le bouton
    .Left = 50 'position horizontale par rapport au bord gauche de la feuille
    .Top = 50 'position verticale par rapport au bord haut de la feuille
    .Width = 150 'largeur
    .Height = 30 'hauteur
    .Object.Caption = "Supprimer données feuille"
End With

'Spécifie le contenu de la macro qui sera associée au bouton
laMacro = "Sub monBouton_Click()" & vbCrLf
laMacro = laMacro & "Cells.Clear" & vbCrLf
laMacro = laMacro & "End Sub"

'Ajoute la procédure dans la feuille
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
    x = .CountOfLines + 1
    .InsertLines x, laMacro
End With

Il existe une deuxième méthode pour associer la procédure évènementielle au bouton.

Vba

Sélectionnez

Dim Ws As Worksheet
Dim Obj As OLEObject
Dim laMacro As String
Dim x As Integer

Set Ws = Sheets.Add 'Ajoute une nouvelle feuille
    
'ajoute un CommandButton dans la nouvelle feuille
Set Obj = Ws.OLEObjects.Add("Forms.CommandButton.1")
With Obj
    .Name = "monBouton" 'renomme le bouton
    .Left = 50 'position horizontale par rapport au bord gauche de la feuille
    .Top = 50 'position verticale par rapport au bord haut de la feuille
    .Width = 150 'largeur
    .Height = 30 'hauteur
    .Object.Caption = "Supprimer données feuille"
End With

'Ajoute la procédure dans la feuille
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
    .CreateEventProc "Click", "monBouton"
    x = .ProcStartLine("monBouton_Click", vbext_pk_Proc)
    .InsertLines x + 2, "Cells.Clear"
End With

V-B. Lire▲

Boucler sur la collection de contrôles dans la feuille de calcul "Feuil1".

Vba

Sélectionnez

Sub boucleControlesFeuille()
    Dim Obj As OLEObject
    
    For Each Obj In Feuil1.OLEObjects
        Debug.Print Obj.Name 'Nom
        Debug.Print TypeName(Obj.Object) 'Type de contrôle
    Next Obj
End Sub

Boucler sur la collection de contrôles dans un UserForm.

Vba

Sélectionnez

    Dim Ctrl As Control
    
    For Each Ctrl In Me.Controls
        Debug.Print Ctrl.Name 'Nom
        Debug.Print TypeName(Ctrl) 'Type de contrôle
    Next Ctrl

Compter le nombre de UserForm dans un classeur.

Vba

Sélectionnez

Sub CompteUserForm()
    Dim Wb As Workbook
    Dim VBComp As VBComponent
    Dim Valeur As Byte
    
    Set Wb = Workbooks("leClasseur.xls")
    
    For Each VBComp In Wb.VBProject.VBComponents
        If VBComp.Type = 3 Then Valeur = Valeur + 1
    Next VBComp
    
    MsgBox " Il y a  " & Valeur & " UserForm dans le fichier " & Wb.Name
End Sub

Lister les propriétés d'un UserForm.

Vba

Sélectionnez

Dim i As Integer
Dim VBcomp As Object

On Error Resume Next
Set VBcomp = ThisWorkbook.VBProject.VBComponents("UserForm1")

For i = 1 To VBcomp.Properties.Count
    Debug.Print VBcomp.Properties(i).Name & ": " & VBcomp.Properties(i).Value
Next i

V-C. Modifier▲

Cet exemple permet de modifier les propriétés d'un UserForm
Celui ci ne doit pas être en cours d'affichage.

Vba

Sélectionnez

'Change la couleur de fond du UserForm.
ThisWorkbook.VBProject.VBComponents("UserForm1").Properties("backcolor") = RGB(200, 200, 200)

Modifier les propriétés d'un objet CommandButton contenu dans un UserForm.
Celui ci ne doit pas être en cours d'affichage.

Vba

Sélectionnez

'Change la couleur de fond du CommandButton
ThisWorkbook.VBProject.VBComponents("UserForm1"). _
        Designer.Controls("CommandButton1").BackColor = RGB(225, 0, 0)

Changer le CodeName d'une Feuille.
Le CodeName correspond à la propriété Name de la feuille de calcul, à ne pas confondre avec le nom de l'onglet.
L'utilisation du CodeName est particulièrement pratique lors de la rédaction de vos procédures car Il permet:
* l'affichage de la saisie semi automatique.
* de faire référence à une feuille dans la macro, sans nécéssité de modification lorsque le nom de l'onglet est changé.
Le CodeName et le nom de l'onglet sont identiques lors de l'ouverture d'un classeur vierge mais la modification du nom de l'un n'entraine pas la modification de l'autre.

Image non disponible

Un exemple pour modifier le CodeName de la 1ere feuille du classeur.
Remarque: Le codeName ne doit pas contenir d'espace.

Vba

Sélectionnez

Sub RenommerCodeName()
    Dim Ws As Worksheet
    
    'Spécifie la 1ere feuille dans le classeur contenant cette macro.
    Set Ws = ThisWorkbook.Sheets(1)
    
    'Attention: le nouveau nom ne doit pas contenir d'espace.
    ThisWorkbook.VBProject.VBComponents(Ws.CodeName).Name = "NouveauNom"
End Sub

V-D. Supprimer▲

Supprimer un UserForm par macro.
L'UserForm ne doit pas être en cours d'affichage.

Vba

Sélectionnez

Sub suppressionUSF()
    'Nécéssite d'activer la référence
    'Microsoft Visual Basic for Applications Extensibility 5.3
    
    'L'UserForm ne doit pas être en cours d'affichage
    Dim VBComp As VBComponent
    
    Set VBComp = ThisWorkbook.VBProject.VBComponents("UserForm1")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

Supprimer un CommandButton placé dans la feuille de calcul "Feuil1".

Vba

Sélectionnez

Dim Obj As OLEObject

Set Obj = Sheets("Feuil1").OLEObjects("monBouton")
Obj.Delete

Supprimer les contrôles qui ont été créés dynamiquement dans un UserForm.
(Voir l'exemple des CheckBox dans le 1er exemple du chapitre V-V-A)

Vba

Sélectionnez

Private Sub CommandButton2_Click()
    Dim i As Integer
    
    For i = Me.Controls.Count - 1 To 0 Step -1
        'Attention la procédure est sensible à la casse.
        If TypeName(Me.Controls(i)) = "CheckBox" Then _
                        Me.Controls.Remove Me.Controls(i).Name
    Next i
End Sub

VI. L'éxécution des macros▲

Ce dernier chapitre propose quelques procédures pour déclencher des macros lorsque l'instruction Call ne peut pas être utilisée:
* Lancer une macro dont le nom ou les arguments sont contenus dans des variables.
* Déclencher les procédures évènementielles des UserForm.
* Déclencher les procédures contenues dans d'autres classeurs.

La méthode Run permet de déclencher des macros, et notamment évènementielles.

Vba

Sélectionnez

'Déclenche l'évènement Open du module objet ThisWorkbook
Application.Run "ThisWorkbook.Workbook_Open"

'Déclenche l'évènement Click d'un CommandButton placé dans la Feuil1
Application.Run "Feuil1.CommandButton1_Click"

Les anciennes macros XL4 peuvent aussi être utilisées pour lancer les macros.
Cet exemple éxécute une macro dont le nom est contenu dans une variable String.

Vba

Sélectionnez

Dim sVar As String
'http://support.microsoft.com/kb/q108519/
sVar = "NomMacro"
Application.ExecuteExcel4Macro "RUN(""" & sVar & """)"

Utilisez la fonction CallByName pour déclencher une procédure évènementielle d'un contrôle contenu dans un UserForm.
Important: Le préfixe "Private" doit être préalablement supprimé.

Vba

Sélectionnez

CallByName UserForm1, "CommandButton1_Click", VbMethod

Utilisez la propriété Caller pour renvoyer le nom de l'objet appelant la macro.

Vba

Sélectionnez

MsgBox Application.Caller

Cette propriété est particulièrement intéressante lorsqu'une même macro est attachée à plusieurs objets (par exemple des formes automatiques) et que vous souhaitez retrouver la source du déclenchement.

Quelques exemples pour éxécuter une macro contenue dans un autre classeur ouvert.
Il suffit d'utiliser la méthode Run en précisant le nom du classeur.

Vba

Sélectionnez

Application.Run "NomClasseur.xls!Module1.NomMacro"

'déclenche une procédure évènementielle ThisWorkbook
'Application.Run "NomClasseur.xls!ThisWorkbook.Workbook_Open"

'déclenche l'évènement Click d'un CommandButton placé dans la Feuil1
'Application.Run "NomClasseur.xls!Feuil1.CommandButton1_Click"

Vous pouvez aussi spécifier les arguments d'une procédure.

Vba

Sélectionnez

Application.Run "NomClasseur.xls!Module1.NomMacro", "Argument1", "Argument2"

Remarque:
Si le nom du classeur contient des espaces ou des caractères spéciaux, le nom doit être encadré par des quotes.

Vba

Sélectionnez

Application.Run "'Nom du Classeur.xls'!Module1.NomMacro"

VII. Téléchargement▲

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants :