As you proceed with VBA development, you will often face situations where you want to use the same logic across multiple files. Copying and pasting code every time is inefficient. Furthermore, if you need to make corrections, you have to update every file, which makes maintenance difficult.
You can solve this problem by using the Application.Run method in VBA. This method allows you to call a macro located in another open workbook as if it were a macro in your own workbook.
This article explains the basic usage of Application.Run, including how to pass arguments.
Preparation: Two Excel Workbooks
To try this feature, first prepare two .xlsm workbooks.
1. The Called Side (Workbook containing the macros)
This is the workbook that will serve as the macro library. Save the file as MacroLibrary.xlsm and write the following two macros in a Standard Module.
' File Name: MacroLibrary.xlsm
' Macro without arguments
Public Sub SayHello()
MsgBox "Hello from another workbook!"
End Sub
' Macro with arguments
Public Sub SaySomething(message As String)
MsgBox "Received message: " & message
End Sub
2. The Caller Side (Workbook where you write the code)
This is the workbook used to call the macros in MacroLibrary.xlsm. The filename can be anything (e.g., DataFile.xlsm).
Basic Syntax of Application.Run
The syntax for Application.Run is as follows:
Application.Run "'WorkbookName'!MacroName", [Arg1], [Arg2], ...
- ‘WorkbookName’!MacroName: Specify the location of the macro you want to run as a single string in the format
'WorkbookName'!MacroName. It is important to enclose the workbook name in single quotes ('). - [Arg…]:: If the macro requires arguments, add them after the string, separated by commas.
Complete VBA Code (Caller)
Write the following code in a Standard Module of DataFile.xlsm. Run this macro while MacroLibrary.xlsm is open.
' File Name: DataFile.xlsm
Sub CallAnotherBooksMacro()
' --- Call a macro without arguments ---
' Syntax: Application.Run "'WorkbookName.xlsm'!MacroName"
Application.Run "'MacroLibrary.xlsm'!SayHello"
' --- Call a macro with arguments ---
Dim messageToPass As String
messageToPass = "This is a string passed as an argument."
' Syntax: Application.Run "'WorkbookName.xlsm'!MacroName", Arg1, Arg2, ...
Application.Run "'MacroLibrary.xlsm'!SaySomething", messageToPass
End Sub
When you run this, a message box saying “Hello from another workbook!” will appear first, followed by a message box saying “Received message: This is a string passed as an argument.”
Key Points and Precautions
1. The Target Workbook Must Be Open
Application.Run can only execute macros in workbooks that are currently open in Excel. If the target workbook is closed, it will result in an error.
2. Importance of Single Quotes
It is strongly recommended to enclose the workbook name in single quotes ('), like 'MacroLibrary.xlsm'. If the filename contains spaces or hyphens, it will not be recognized correctly without the single quotes, causing an error. It is a safe habit to always include them.
3. Must be a Public Macro in a Standard Module
Basically, the macro you want to call must be declared as a Public Sub within a Standard Module. Macros written as Private Sub or inside Sheet Modules may not be callable using this method.
Summary
By using the Application.Run method, you can group VBA code into separate workbooks by function to “libraryize” them. This enables efficient development where you can call and use these libraries from various files.
Application.Run "'WorkbookName'!MacroName", Argument
If you remember this syntax and the rule to “keep the target workbook open,” code reusability will improve significantly, greatly expanding the scope of your VBA development.
