In VBA, there are several ways to run a macro within 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 and how to use them.
Run a macro in the same workbook
The standard method for calling a macro contained in the same workbook 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. As it is easier 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
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 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
Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.
Therefore, what most people like you need is lots of examples that you can practice. That is why the 100 Excel VBA Macros eBook exists. It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers. The book contains:
- 100 example codes to practice reading and writing macros that will embed the language into your thinking.
- An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
- Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
- Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.
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 are needed when the workbook name contains a space.
If there is no space, the single quotation marks are not required. The code will still run correctly if they are included, so I prefer to use them for consistency.
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 a 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.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: