How do I use Application.Run in Excel
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 : Call MyMacroName
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 :
Application.Run "MyCodeWorkbook.xlsm!MyMacroName"
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 "'MyCodeWorkbook.xlsm'!MyMacroName"
It does not do any harm to use the single quotes even if not needed, always include them if the workbook name is not known in advance is a good habit. 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.xlsm", then you need to double-up the apostrophes like Application.Run "'Joe''s Workbook.xlsm'!MyMacroName"
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 :
Application.Run "MyCodeWorkbook.xlsm!MyMacroName"
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 "'MyCodeWorkbook.xlsm'!MyMacroName"
It does not do any harm to use the single quotes even if not needed, always include them if the workbook name is not known in advance is a good habit. 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.xlsm", then you need to double-up the apostrophes like Application.Run "'Joe''s Workbook.xlsm'!MyMacroName"
Callbacks instead of macros in Excel 2016 or higher
But what if you use Excel 2016 or higher and use custom Ribbon controls with callbacks.
See the differents below between a normal macro and a callback:
See the differents below between a normal macro and a callback:
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 workbook.
But we can do this to call a callback in the same or another workbook:
But we can do this to call a callback in the same or another workbook:
Check if file or add-in is open
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.
Tip: You could replace the MsgBox that says that the file is not open with code that opens the workbook/add-in. Set TestWkbk = Workbooks.Open("/Users/rondebruin/Desktop/MyCodeWorkbook.xlsm")
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. MsgBox bIsBookOpen("MyCodeWorkbook.xlsm")
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. MsgBox bIsBookOpen("MyCodeWorkbook.xlsm")