Run Macro In Another Workbook Using VBA

If you want to run a macro in a different Excel workbook using VBA, you can use the ‘Application.Run‘ method.

This method allows you to specify the workbook and macro name you want to run, and you can also pass arguments to the macro if it needs any. The method will then take care of executing the macro for you. 

In this tutorial, I will show you some scenarios for running a macro in another workbook using VBA.

Note: If the other workbook is from an external source, inspect the macro before running it to ensure it doesn’t contain malicious instructions.

Application.Run’ Method Syntax

The Application.Run method in VBA allows you to run a macro by specifying its name as a string, along with optional arguments.

The syntax of Application.Run’ Method is as follows:

Application.Run(MacroName, [Arg1], [Arg2], …, [Arg30])

Here’s a breakdown of the syntax components:

  • MacroName: This is a required parameter. It’s a string specifying the name of the macro to run. The macro name can include the module name or workbook name if it’s in a different module or workbook. For example, “ModuleName.MacroName” or “WorkbookName.xlsm!MacroName“.
  • [Arg1], [Arg2], …, [Arg30]: These are optional parameters. You can specify up to 30 arguments to pass to the macro. These arguments can be of any data type supported by VBA and are passed to the macro in the order they are specified.

Example 1: Run Macro From Another Open Workbook

Suppose you have an open workbook named ‘Example Workbook.xlsm’ and want to run a macro named ‘ExampleMacro’ contained in one of the workbook’s modules.

Below is a VBA code you can use to do this:

Sub RunMacroInAnotherOpenWorkbook()

    ' Executes the "ExampleMacro" macro from "Example Workbook.xlsm"
    Application.Run "'Example Workbook.xlsm'!ExampleMacro"

End Sub

This VBA script runs a macro named ExampleMacro located in an open workbook titled Example Workbook.xlsm.

It utilizes the Application.Run method to execute the specified macro, enabling actions across multiple workbooks without direct integration of their VBA code.

You must enclose the workbook name in single quotation marks if it contains spaces. 

Remember, for this to work, the workbook containing the macro (“VBA Code Snippets.xlsm” in this case) needs to be open. If it’s not open, Excel won’t be able to find and run the macro, and you’ll likely encounter an error.

The code will also trigger runtime error 1004 if macros are disabled in the other workbook. 

VBA runtime error 1004 - Run Macro In Another Excel Workbook

You must enable the macros in the other workbook to avoid this error.

Also read: How to Open Excel Workbook Using VBA

Example 2: Run Macro In Another Closed Workbook

Assume you have a closed workbook named ‘Utility Codes.xlsm’ in the ‘Reports’ folder on F drive, and you want to run a macro named ‘FormatWorksheets’ contained in one of the workbook’s modules.

Here is a VBA code that you can use to accomplish this task:

Sub RunMacroInClosedWorkbook()

    ' Disables screen updating to speed up macro execution
    Application.ScreenUpdating = False

    ' Declares variables for the workbook and macro
    Dim otherWorkbook As Workbook
    Dim macroName As String
    Dim workbookPath As String

    ' Specifies the path of the workbook and the macro name
    workbookPath = "F:\Reports\Utility Codes.xlsm"
    macroName = "FormatWorksheets"

    ' Opens the workbook at the specified path
    Set otherWorkbook = Workbooks.Open(workbookPath)

    ' Runs the specified macro from the opened workbook
    Application.Run "'" & otherWorkbook.Name & "'!" & macroName

    ' Closes the opened workbook without saving changes
    otherWorkbook.Close SaveChanges:=False

    ' Re-enables screen updating
    Application.ScreenUpdating = True

End Sub

This above VBA script automates the process of running a macro from a closed workbook.

Initially, it turns off screen updating for efficiency. It then defines and opens a workbook located at a specified path (F:\Reports\Utility Codes.xlsm).

After opening the workbook, the script executes a macro named FormatWorksheets from this workbook.

Once the macro has run, the script closes the workbook without saving any changes and re-enables screen updating.

Also read: How to Close Workbook Using VBA

Scenario #3: Run Macro In Another Closed Workbook Requiring a Password to Open

Assuming you have a closed workbook named ‘Central Command.xlsm’ in the ‘Global Views’ folder on the E drive that requires a password to open, and you want to run a macro named ‘CompanyColors.xlsm’ contained in one of the workbook’s modules.

You can use the following sub-routine to complete the task:

Sub RunMacroInPasswordProtectedClosedWorkbook()

    ' Disables screen updating for efficiency
    Application.ScreenUpdating = False

    ' Declares variables for the workbook path, workbook object, password, and macro name
    Dim closedWorkbookPath As String
    Dim closedWorkbook As Workbook
    Dim password As String
    Dim macroName As String

    ' Sets the path, password, and macro name
    closedWorkbookPath = "E:\Global Views\Central Command.xlsm"
    password = "Qh!s#6My"
    macroName = "CompanyColors"

    ' Attempts to open the password-protected workbook, resumes on error
    On Error Resume Next
    Set closedWorkbook = Workbooks.Open(closedWorkbookPath, , False, , password)
    On Error GoTo 0

    ' Runs the specified macro in the opened workbook
    Application.Run "'" & closedWorkbook.Name & "'!" & macroName

    ' Closes the workbook without saving changes and clears the workbook variable
    closedWorkbook.Close SaveChanges:=False
    Set closedWorkbook = Nothing

    ' Re-enables screen updating
    Application.ScreenUpdating = True

End Sub

This above VBA code runs a macro from a password-protected, closed workbook.

It temporarily disables screen updating for smoother execution. The script sets up variables for the workbook’s path, its password, and the name of the macro to be executed.

It then opens the specified workbook using the provided password, handling any errors that might occur during this process.

After opening the workbook, it executes the designated macro CompanyColors. Finally, it closes the workbook without saving any changes, clears the reference to the workbook, and re-enables screen updating.

Also read: How to Save Workbook Using VBA

Example 4: Running a VBA Macro in Another Module

To run a VBA macro located in another module, you can call the macro directly by its name, provided it is declared as Public (which is the default for macros). This is a common practice in VBA to organize code into separate modules and then control or trigger actions from a central place, such as another module or a user form.

Here’s an example scenario to illustrate this:

Imagine you have a macro named ProcessData in a module named Module1. To call this macro from another module, say Module2, you just need to use the macro name like this:

Sub CallProcessData()

    ' Call the ProcessData macro from Module1

End Sub

This VBA code consists of a simple subroutine CallProcessData that calls another macro named ProcessData.

This method of calling a macro is particularly useful when the ProcessData macro needs to be triggered from multiple places or under specific conditions.

Also read: Check If Workbook Is Open Using VBA

Protecting Modules in VBA in Excel

Storing passwords in code is not secure; unauthorized people can access it. 

You can use the following steps to protect the module and prevent unauthorized users from viewing the code in it:

  1. Right-click the module and choose ‘VBAProject Properties’ on the context menu.
Select VBAProject Properties
  1. On the ‘Protection’ tab of the ‘VBAProject-Project Properties’ dialog box, select the ‘Lock project for viewing’ checkbox, enter a password to view project properties, enter the password again in the ‘Confirm password’ text box, and click OK.
select the 'Lock project for viewing' checkbox

While protecting the VBA module works, it can be broken by skilled intermediate or advanced Excel users.

Think of protecting your VBA modules like putting a basic lock on your door. It’s great for keeping out your average person who might accidentally mess things up. But, if you’re dealing with someone who really knows their way around locks and is determined to get in, that simple lock won’t hold up for long. So, it’s smart to use this as just one part of your overall security plan, not the whole thing.

In this tutorial, I showed you four scenarios for using VBA to run macros in another Excel workbook.

I hope you found the tutorial helpful.

Other Excel VBA articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment