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 SubThis 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.
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
ProcessData
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:
- Right-click the module and choose ‘VBAProject Properties’ on the context menu.
- 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.
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:
