Run a macro from a macro (from another workbook)

VBA Code Snippets

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

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

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.

Don’t forget:

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

16 thoughts on “Run a macro from a macro (from another workbook)

  1. fretta says:

    What if I want to share a macro with my team to execute a certain task where everybody is using their own ID and might be in a different network all across the country, even the world.

  2. Excel Off The Grid says:

    Hi Fretta,

    Since the workbook must be open to run a macro from another workbook, this becomes more a challenge to open the correct workbook.

    I suggest you need to think about the workflow, which will then provide the best answer. If you are on different networks, how will you distribute the file? Where will the file be saved? Who will access the file and from where?

  3. Dan says:

    Could an Excel add-in instantiate another Excel and call a sub in the same add-in that instantiated it – thereby working around Excel’s inability to multi-thread VBA?

  4. Alphonse says:

    Thank you!
    All the variations in one sweet explanation will totally erase any doubts as to “How’s the code for…?” that I find myself asking more times than I care to. Learning by example is good, but sometimes not enough, and that is where learning by comparisons drives it all home.
    Thank you once again for your time and willingness to share.
    Cheers.

  5. Terry Cline says:

    This is great stuff – but having a problem getting it to work. VBA returns a message “run time error 1004: Cannot run the macro [big path, file name and macro name]. The macro may not be available in this workbook or all macros may be disabled.” I can assure you the macro is in the file, that the spreadsheet is open. If I close the target workbook and run this macro manually, it works just fine, no errors. Not sure why the macro won’t run. here’s the calling code:
    Dim tmpTrackerName As String
    Dim MacroName As String

    tmpTrackerName = ApprovalEmails.tbTrackerPath
    MacroName = “GetCNInformation”

    Application.Run “‘” & tmpTrackerName & “‘!” & MacroName

    • Excel Off The Grid says:

      Hi Terry,

      I’m guessing that “ApprovalEmails” is the name of a worksheet, and “tbTrackerPath” is a named range. If that is the case then you need square brackets around the named range.

      tmpTrackerName = ApprovalEmails.[tbTrackerPath]

      Let me know if that solves the problem.

  6. Jennifer says:

    Is it possible to run a macro that can also open other documents?
    I have to repetitively;
    export a report from the web (spotfire)
    (3x different reports)
    and make a report with calculations (two of the three)
    Right now I have a macro for each one, and I do the following;
    Export all 3. save
    Run the macro on the first, save
    Run the macro on the next, save.
    Run a macro to create a query between the two.
    Run a macro to make the necessary calculations between the two
    Run a macro to add a query to the third document; (This one just needs to be included.)
    Any thoughts?

    The intention is to automate the population of this document as quickly as possible, (so i would normally query to the web, but spotfire and my organizations security features prohibit this.

    (I was considering powerquery, but I haven’t had the time to learn how to do that entirely… (I did however add a concatenation to my macro to solve the “many to one” error that would come up when I would make a relationship between the two),

  7. Ken Murphy says:

    Very Nice Rundown of all possible options however, I’m still receiving and error. Running a macro from a user form to open a workbook (this works) but then calling the macro from the newly open workbook gives runtime error 1004. The macro may not be available in this workbook or all macros are disabled. I’m following the example:
    Sub CallAnotherMacro()
    Run “‘AnotherWorkbook.xlsm’!ThisWorkbook.NameOfMacro”
    End Sub
    and know macros are enabled because one ran to get me this far and if I run the macro by switching to the open workbook it executes.

    Any Ideas

    • Excel Off The Grid says:

      My best guess is due to Macro security settings.
      When the workbook opens does it have the Enable Content button at the top? I don’t think you can run a Macro until that has been clicked. Changing settings to allow all Macros would get around that, but would be a big security risk.

  8. Aaron says:

    Thank you for putting this together!

    How do I capture a returned value from the macro I call? This works correctly for me on it’s own:

    Run “‘OtherFileName.xlsm’!ModuleName.MacroName”

    However, this causes a compile error:

    Dim newWS as Worksheet
    Set newWS = Run “‘OtherFileName.xlsm’!ModuleName.MacroName”

    • Excel Off The Grid says:

      To capture a result in the way you want, there are two options:

      1) Use a VBA Function, rather than a Sub.

      2) Use a public variable, so that the value of that variable is available everywhere.

Leave a Reply

Your email address will not be published. Required fields are marked *