When you want to run a macro from an event or from another macro in the
same workbook you can call the macro like this in your code :
You do not have to use Call but I think it is clearer when you read the code that another macro is called.
But what if you want to run a macro that is in another workbook or Add-In(File or add-in must be open).
We can use Application.Run if we want that like this :
If the workbook name includes spaces or some other particular characters it is necessary to enclose the name with single quotes, like this :
Application.Run "'Book 1.xls'!MyMacroName"
It does not do any harm to use the single quotes even if not needed, and always include them if the workbook name is not known in advance, for example if the workbook name is a variable like this
Application.Run "'" & strFileName & "'!MyMacroName"
Note: If your workbook name contains apostrophe (') characters, such as in "Joe's Workbook.xls", then you need to double-up the apostrophes like Application.Run "'Joe''s Workbook'!MyMacroName"
But what if you use Excel 2007-2013 and use custom Ribbon controls with
A normal macro looks like this :
Sub TestMe() MsgBox "Hi there" End Sub
And a callback looks like this :
Sub TestMe(control As IRibbonControl) MsgBox "Hi there" End Sub
You will notice that the Application.Run examples above will not work
when you want to run a callback in another workbook or Add-in. Also
Call MyMacroName will not work to call a callback in the same
But we can do this to call a callback in the same workbook :
Sub test1() Dim obj As Object TestMe obj End Sub
Or to call a callback in an add-in or another workbook use :
Sub test2() Dim obj As Object Application.Run "'RDBMerge.xlam'!RunRDBMergeForm", obj End Sub
Note: Instead of a object you can also use IRibbonControl like this :
Sub test3() Dim IRCdummy As IRibbonControl TestMe IRCdummy End Sub
Before you try to run the Application.Run line that call a macro or callback in another workbook or add-in you can test if the workbook or add-in is open with the code below.
Sub ErrorTest() Dim TestWkbk As Workbook Dim obj As Object Set TestWkbk = Nothing On Error Resume Next Set TestWkbk = Workbooks("RDBTestAdd-in.xlam") On Error GoTo 0 If TestWkbk Is Nothing Then MsgBox "Sorry the File is not open, it is not possible to run the macro." & _ " But you can add code here to open the workbook or add-in." Else MsgBox "Use one of the two lines below to call the callback or macro" 'Run a callback in a Excel 2007-2013 workbook/add-in 'Application.Run "'" & TestWkbk.Name & "'!RunMyMacro", obj 'If you want to run a macro in a Excel 97-2013 workbook/add-in use 'Application.Run "'" & TestWkbk.Name & "'!RunMyMacro" End If End Sub
Tip: You could replace the MsgBox that says that the file is not open
with code that opens the workbook/add-in. Set TestWkbk
Do not forget to check if opening the file was succesful in the code before you try to call the macro or callback.
Another way to test if a workbook/add-in is open is to call a function like this with as argument the workbook name that you want to check.
Function IsOpen(WBname As String) As Boolean Dim wb As Workbook On Error Resume Next Set wb = Workbooks(WBname) If Err = 0 Then IsOpen = True End Function
But how do we use this function if it is not in the same workbook, you
can use this to check if there is a file named RDBMerge.xlam open.
Dim MyResult As Boolean
MyResult = Application.Run("'" & TestWkbk.Name & "'!IsOpen", "RDBMerge.xlam")
Or use this to call a macro/function with arguments
Application.Run "'" & TestWkbk.Name & "'!MacroNameHere", "parm1", "parm2"
Many thanks to Peter Thornton, Jim Rech, Dave Peterson and Mike Rosenblum for their useful comments.