Dans certains cas, il peut être intéressant d'exécuter une macro dont la nom est conservé dans une chaine de caractères.
C'est la méthode Run de l'objet Application qui se charge d'appeler la macro-commande conservée sous forme de texte.
C'est indispensable quand par exemple la macro doit être associée à un contrôle de formulaire créé dynamiquement ou sur un timer.
La syntaxe ne pose aucun problème particulier.
Application.Run "WorkBookName.xlsm!MacroName"
Il n'est pas obligatoire de préciser l'objet Application et l'instruction Run "WorkBookName.xlsm!MacroName" est tout aussi valide.
Nom du classeur
Le nom du classeur est optionnel si la macro à exécuter se trouve dans le même classeur. Donc la syntaxe Run "MacroName" est acceptée.
On donne le nom du classeur avec éventuellement le chemin d'accès complet si le classeur contenant la macro n'est pas dans le même dossier que le classeur appelant.
Application.Run "D:\Personnel\UserName\Documents\WorkBookName.xlsm!MacroName".
Si le nom du classeur contient une espace, il faut décorer le nom du classeur avec une simple quotte.
Application.Run "'WorkBook Name.xlsm'!MacroName".
Si le nom du classeur contient déjà une quotte comme dans le nom WorkBook'Name.xlsm, il faut les doubler.
Application.Run "'WorkBook''Name.xlsm'!MyMacroName".
Sur un contrôle de formulaire créé dynamiquement, on utilise la méthode OnAction.
myButton.OnAction = "MacroName".
Le principe est le même sur un timer.
Application.OnTime = "MacroName".
Nom de macro dans une variable
L'avantage est que le nom de la macro peut-être stocké dans une variable de type chaine.
On pourrait par exemple prévoir un tableau avec la liste des macros à enchainer.
Je peux "piocher" dans ma bibliothèque de macro celles à exécuter et ainsi construire des squelettes d'applications dynamiques.
Sub RunMacro()
Dim MyMacro As String
MyMacro = "MacroName"
Run MyMacro
End Sub
Nom du classeur en variable
Il est tout aussi possible de mettre le chemin d'accès et le nom du classeur en variable.
La variable du nom du classeur est mise entre quotte.
Dim myWorkBook As String, MyMacro As String
myWorkBook = "C:\WorkBookName.xlsm"
MyMacro = "MacroName"
Application.Run "'" & myWorkBook & "'" & "!" & MyMacro
Ajouter des paramètres
Rajouter de paramètres se fait de façon classique.
Affecter une macro à un objet graphique sur la feuille de calcul
Il est tout à fait possible de rajouter des paramètres quand on affecte une macro à un objet graphique.
L'ensemble de la chaine est entourée par une apostrophe et on écrit simplement les paramètres comme un appel classique en VBA.
myWorkBook.xlsm!'MacroName "StringParameter", 10'
Sub MacroName(ByVal argString As String, ByVal argInt As Integer)
MsgBox argString & " " & CStr(argInt)
End Sub
C'est exactement la même chose avec la méthode Run au détail près qu'il n'y a pas besoin des apostrophes.
Remarquez toutefois que le nom de la macro est suivi d'une virgule.
Run "MacroName", "MyParameter", 10
Concaténation de paramètres
La méthode Run, accepte jusqu'à 30 paramètres.
Il n'est pas possible d'utiliser des paramètres nommés.
La syntaxe se complique singulièrement quand il faut rajouter des paramètres et l'instruction Run "WorkBookName.xlsm!MacroName(myParameter)", ne fonctionne pas.
L'ensemble de la syntaxe doit être encadrée de guillemets et d'apostrophes :
Run "'"MacroName"'"
On vient ensuite rajouter une espace entre guillemets après le nom de la macro :
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & MyMacro & " " & "'"
Rajouter le nom du classeur
Si la macro-commande est située dans un autre classeur, il faut donner le chemin complet. On vient ensuite concaténer le nom du classeur avec le nom de la macro.
Dim myWorkBook As String, myMacro As String
myWorkBook = "WorkBookName.xlsm"
myMacro = "MacroName"
Commande = myWorkBook & "!" & myMacro
Run Commande
Paramètre de type numérique
On concatène simplement la valeur du paramètre.
Ainsi la macro qui possède la signature Sub MacroName(ByVal argNumber As Byte) sera appelée à l'aide de la syntaxe :
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & MyMacro & " " & 10 & "'"
Plusieurs paramètres de type numérique
On concatène au premier paramètre le séparateur d'arguments (virgule) et la seconde valeur.
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & MyMacro & " " & 10 & "," & 20 & "'"
Paramètre de type chaine
On rajoute et on double des guillemets autour du nom du paramètre.
La macro de prototype Sub MacroName(ByVal argString As String) est exécutée avec l'instruction :
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & MyMacro & " ""myParameter""'"
Enchainer les paramètres de type chaine
Il suffit de séparer les paramètres par une virgule sans oublier de doubler les guillemets autour de la valeur du paramètre.
Ainsi la macro Sub MacroName(ByVal argStringA As String, ByVal argStringB As String) s'appelle par :
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & MyMacro & " ""FirstParameter"", ""SecondParameter"" '"
Mélanger les types de paramètres
Il est possible de mélanger les types de paramètres.
Avec la macro Sub MacroName(ByVal argByte As Byte, ByVal argString As String), cela donne :
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & MyMacro & " " & 10 & ",""StringParameter""" & "'"
Et si c'est la paramètre de type chaine qui est déclaré en premier comme dans la macro Sub MacroName(ByVal argStringA As String, ByVal argByte As Byte) :
Dim MyMacro As String
MyMacro = "MacroName"
Run "'" & NomMacro & " ""toto""" & "," & 10 & "'"
Paramètres en variables
Paramètres par valeur
Même si les paramètres sont déclarés par références (ByRef), ils sont passés par valeur (ByVal).
Variable de type chaine
Dim MyMacro As String, Commande As String
Dim varStr As String
MyMacro = "MacroName"
varStr = "MyParameter"
Commande = "'" & MyMacro & " """ & varStr & """'"
Run Commande
Variable de type numérique
Dim MyMacro As String, Commande As String
Dim varInt As Integer
MyMacro = "MacroName"
varInt = 10
Commande = "'" & MyMacro & " " & varInt & "'"
Run Commande
Paramètre de type objet
Si effectivement une variable scalaire (chaine, nombre, booléen, etc.) ne peut être transmise par référence, il en va tout autrement d'une variable objet.
Quand un objet est transmis par valeur, il n'y a pas copie de l'objet (donc ses membres), comme avec un scalaire.
C'est une copie de l'adresse mémoire de l'objet, ce qu'on appelle un pointeur ou plus précisément référence, qui est passée en paramètre.
Donc quand on modifie un membre du paramètre, c'est bien l'objet source qui est modifié.
Dans un module de classe (clsSample)
Private m_Value As Byte
Property Get myvalue() As Byte
myvalue = m_Value
End Property
Property Let myvalue(ByVal argValue As Byte)
m_Value = argValue
End Property
Dans un module standard
La procédure à appeler avec la méthode Run.
Sub MacroName(ByVal argSample As clsSample)
argSample.myvalue = 20
End Sub
Appel à l'aide de la méthode Run
Dim mySample As clsSample
Set mySample = New clsSample
mySample.myvalue = 10 ' Initialiser la propriété de l'objet.
Run "MacroName", mySample
Debug.Print mySample.myvalue ' La propriété prend bien la valeur 20.
Fonction et méthode Run
La syntaxe est légèrement différente avec un appel de fonction.
Function MacroName(ByVal argInt As Byte, ByVal argStr As String) As String
MacroName = argStr & " " & argInt
End Function
Lors de l'usage de la méthode Run, il faut entourer le nom de la macro à exécuter et les paramètres de parenthèses.
Dim myMacro As String
Dim Response As String
myMacro = "MacroName"
Response = Run(myMacro, 10, "myParameter")
Debug.Print Response
Conclusion
Même si l'usage de la méthode Run peut paraître inutile, n'oublions pas que ses concepts sont utilisables avec les méthodes OnAction et OnTimer.
Le principe n'est pas très compliqué et demande juste un peu de rigueur pour ne pas se mélanger avec les apostrophes (quottes) et guillemets.
Sans avoir fait de test de rapidité, je subodore qu'une procédure appelée avec la méthode Run sera plus lente à l'exécution qu'un appel direct.
Il est peut-être à déconseiller de l'utiliser dans une boucle qui s'exécute un nombre important de fois.
Achetez mon ouvrage !
Mon PDF « Créer un planning perpétuel sur Microsoft Excel sans macro » est disponible à la vente.
Pour plus d’informations, rendez-vous sur la page dédiée.