Excel – Run a macro or function from another workbook

Description:

This code can be used to run a macro from another [gs workbook]. It must open the file first, and then the other [gs workbook]'s macro can be executed.

Discussion:

When running macros from other [gs workbook]s, I highly recommend reviewing your macros to ensure that they will operate on the sheets that they are supposed to.

If using ActiveSheet, Activecell, etc... in your code, make sure that you do explicitly set the correct sheet (cell) as active, or you could encounter unexpected results. Following are two [gs macro]s; one to run a sub with no arguments, and one to run a sub which does require arguments.

[tab:How to Use]

How to use:

Go to the workbook that holds the routine you want to run and note the exact name of the macro/function, and any arguements required.

• Copy the appropriate sub below (in The Code tab).
• In the Workbook that you want to trigger your macro from (not the one that holds the code you want to execute)...
• In Excel press Alt + F11 to enter the VBE.
• Press Ctrl + R to show the Project Explorer.
• Right-click desired file on left (in bold).
• Choose Insert -> Module.
• Paste code into the right pane.
• Modify the NameOfFile and PathToFile variables to the appropriate workbook name and path of the file that holds the code you want to run.
• Update the Application.run line to list your target macro's name, and arguments (If necessary).
• Press Alt + Q to close the VBE.
• Save [gs workbook] before any other changes.

[tab:The Code]

Option Explicit
Sub RunMacro_NoArgs()
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook

Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
CloseIt As Boolean

'Set file name and location. You will need to update this info!
NameOfFile = "MyMacroLivesHere.xls"
PathToFile = "C:\temp"

'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = Workbooks(NameOfFile)

If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
CloseIt = True
End If

'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If
On Error Goto 0

'Run the macro! (You will need to update "MacroName" to the
'name of the macro you wish to run)
Application.Run (wbTarget.Name & "!MacroName")

If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If

End Sub

Sub RunMacro_WithArgs()
'Macro purpose: To use the application.run method to execute
'a function or macro (with arguments) from another workbook

Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
MyResult As Variant, _
CloseIt As Boolean

'Set file name and location. You will need to update this info!
NameOfFile = "MyFunctionLivesHere.xls"
PathToFile = "C:\temp"

'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = Workbooks(NameOfFile)

If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
CloseIt = True
End If

'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If

On Error Goto 0

'Run the function. Update the "FunctionName" to the name of your function
'and change 1 & 2 to the arguments you need to pass to the function
MyResult = Application.Run(wbTarget.Name & "!Functionname", 1, 2)

'Give user the results
MsgBox MyResult

If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If
End Sub

[tab:END]