In VBA, there are several ways to run a macro from a macro, and even run a macro from another workbook. On forums, these methods are used interchangeably depending on the commenter’s personal preferences. This can lead to confusion for anybody trying to learn VBA. Depending on the method for running/calling the macro, the syntax is slightly different.
This post shows these common methods to run and macro from a macro and how to use them.
Table of Contents
Run a macro from a macro in the same workbook
There are 3 common methods for calling a macro contained in the same workbook.
- Using the macro’s name only
- Calling the macro
- Run a macro
These 3 methods are detailed below.
Use the macro’s name
In this code example, only the macro’s name is required.
Sub CallAnotherMacro()
NameOfMacro
End Sub
When using this method with parameters/arguments, the first argument follows the name of the macro, each subsequent argument is separated by a comma.
Sub CallAnotherMacro()
Dim argument1 As String
Dim argument2 As Integer
argument1 = "ExampleText"
argument2 = 1
NameOfMacro argument1, argument2
End Sub
Calling a macro
Using the Call statement is my preferred method. It is easy to read because it is clear that another macro is being run.
Sub CallAnotherMacro()
Call NameOfMacro
End Sub
Parameters used with this method are enclosed in parentheses and separated by a comma.
Sub CallAnotherMacro()
Dim argument1 As String
Dim argument2 As Integer
argument1 = "ExampleText"
argument2 = 1
Call NameOfMacro(argument1, argument2)
End Sub
Run a macro
The Run command is similar to Call, but requires the name of the macro to be in the form of a text string.
Sub CallAnotherMacro()
Run "NameOfMacro"
End Sub
To use arguments, the text string is followed by a comma, each argument is separated by a comma.
Sub CallAnotherMacro()
Dim argument1 As String
Dim argument2 As Integer
argument1 = "ExampleText"
argument2 = 1
Run "NameOfMacro", argument1, argument2
End Sub
As the name of the macro is a text string, this can be treated like a variable. Therefore the code can run different macros depending on the scenario. This is detailed in the section below.
Run a macro based on a variable
It is possible to Run a macro based on the value of a string variable. This enables a different macro to be triggered depending on the value of the string variable at that point.
In the example below:
- NameOfMacro is the name of the Sub procedure (the same as the examples above)
- MacroName is a string variable, which holds the text “NameOfMacro”
Sub CallAnotherMacro()
Dim MacroName As String
MacroName = "NameOfMacro"
Run MacroName
End Sub
In the code above, the Run command is not followed by a text string, but by a variable. The Run function works because the variable is a text string.
To use parameters using this method the syntax is as follows:
Sub CallAnotherMacro()
Dim MacroName As String
Dim argument1 As String
Dim argument2 As Integer
MacroName = "NameOfMacro"
argument1 = "ExampleText"
argument2 = 1
Run MacroName, argument1, argument2
End Sub
Run a macro contained in another workbook
Note: The workbook which contains the macro must be open. If you need to open the workbook first, check out the code in this post.
To run a macro contained in another workbook, use the Application.Run command as follows:
Sub CallAnotherMacro()
Application.Run "'Another Workbook.xlsm'!NameOfMacro"
End Sub
The single quotation marks around the workbook name are required when the workbook name contains a space.
If there is no space, the single quotation marks are optional. The code will still run correctly if they are included. I prefer to use them for consistency and reducing risk if the code were changed.
Sub CallAnotherMacro()
Application.Run "AnotherWorkbook.xlsm!NameOfMacro"
End Sub
Run a macro contained in another workbook based on a variable
Using the text string variable, we can build the name of the workbook and macro. This enables us to call any macro from any open workbook.
Sub CallAnotherMacro()
Dim WorkbookName As String
Dim MacroName As String
WorkbookName = "AnotherWorkbook.xlsm"
MacroName = "NameOfMacro"
Run "'" & WorkbookName & "'!" & MacroName
End Sub
If using parameters, these can also be included as follows:
Sub CallAnotherMacro()
Dim WorkbookName As String
Dim MacroName As String
Dim argument1 As String
Dim argument2 As Integer
WorkbookName = "AnotherWorkbook.xlsm"
MacroName = "NameOfMacro"
argument1 = "ExampleText"
argument2 = 1
Run "'" & WorkbookName & "'!" & MacroName, argument1, argument2
End Sub
Calling and running macros from Workbook, Worksheet or UserForm Modules
All the examples above assume the VBA code is stored within a Standard Module. But, VBA code can be held within Workbook, Worksheet, and UserForm Modules too. To call a macro from one of these types of modules, it requires a small adjustment; the name of the object must precede the name of the macro.
Using the same Run method as above, to run a macro contained within the Workbook Module the code would be as follows:
Sub CallAnotherMacro()
Run "ThisWorkbook.NameOfMacro"
End Sub
Notice that NameOfMacro has now become ThisWorkbook.NameOfMacro.
If the VBA code is contained within the Workbook Module of another workbook, the code would be adjusted as follows:
Sub CallAnotherMacro()
Run "'AnotherWorkbook.xlsm'!ThisWorkbook.NameOfMacro"
End Sub
Use the code name of the object to reference a Worksheet Module or UserForm Module.
Conclusion
There are many ways to run a macro from a macro. The variety of methods can cause a lot of confusion as different users prefer different methods. Hopefully, this post has given you the knowledge to do this for yourself.
Related Posts:
- Automatically run a Macro when opening a workbook
- Private vs Public Subs, Variables & Functions in VBA
- Useful VBA codes for Excel (30 example macros + Free ebook)
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
Hello,
My English is not so good, so please ignore my mistakes.
we have online erp software and we download invoice in excel format from erp.
each time invoice file need to do some fixed formatting, so i have recorded a macro on one file and saved on my desktop. so each time i open that macro file and run that on invoice file… this running fine without any issue…
now my issue is i have multiple macros saved on that file, so i want a message box comes whenever i run wrong macro on wrong file…
for example.
if i run packing macro on invoice then a message will show that this macro is not for invoice it is for packing list file.
and if i by mistake run invoice macro on packing list then message shows same way…
i hope this is clear…
I added below codes in my macro, but it is not working…
With Sheet1
If .Range(“A1”).Value = “Invoice” Then
MsgBox “Please run Invoice Macro, this is Packing Macro”
Exit Sub
End If
End With
please help both file have sheet1 so how to select invoice file sheet1 here ?
please help me
thanks and regards,
manohar jangid
Your code looks OK to me. In what way is it not working?