Visual Basic Editor



Image non disponible

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

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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 : Viadeo Twitter Facebook Share on Google+   

Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur. La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.