Calling a Macro from another workbook

[Ricky Morris]

What's the syntax to call a macro in another workbook?

Thanks,
Ricardo

 

[johnywhy]

Sorry to bring up an old thread... but I'm trying to do this too, but the macro is contained on a worksheet

Hi Mark

Never apologize for continuing a still-relevant thread, especially if you're adding new content not already answered.

The macro you're calling is hidden-- specifying the sheet codename gives you access to the hidden macro.

Application.Run "'My Workbook.xlsb'!shtMySheet.MyMacro"

Be sure to use the sheet's VBA codename ("shtMySheet" in pic below), not it's front-end sheet-name ("My Sheet" in pic below):

The same trick can be used to execute a macro in a standard module marked Option Private Module

Application.Run "'My Workbook.xlsb'!MyModule.MyMacro"

Note, you can use the same method to call a hidden macro in a separate Excel instance:

OtherExcelApplication.Run "'My Workbook.xlsb'!shtMySheet.MyMacro"

(Calling a macro in a class instance in another book is yet another scenario, not addressed by this thread).

 

[mikeymay]

I seem to having a problem with utilising this thread

I have tried both the following

strMacro = "'" & wbPlan.Name & "'!Module4.Activity_Type_Validation"
strMacro = "'" & wbPlan.Name & "'!Activity_Type_Validation"

and then both

Application.Run strMacro
Application.Run (strMacro)

And all combinations return a run time error 449 Argument not optional.

The name macro I want to run is in Module4 and is called Activity_Type_Validation with a sub name of

Sub Activity_Type_Validation(Ws As Worksheet)

 

[johnywhy]

Iall combinations return a run time error 449 Argument not optional.

Try this:
Prove to yourself you can call this procedure the normal way, from inside the same module, same workbook, without using `Application.Run`.

Are you sure you know how?

 

[mark hansen]

thanks for the tips John, I'll give them a try.

 

[GoodMD]

I know it has been a long time since this issue was resolved, but just wanted to thank Storm8 for the suggestion of adding the apostrophe. That saved me a lot of time and frustration as I was having the same issues and not thinking of the little apostrophe.

Cheers,
GoodMD

 

[rikuouduou]

"""""""

Workbookname = Book.xlsm (need to be XLSM)
macroname = Macro (add macro name, the one after "SUB" no module name)

Correct form in case workbook and macro name are variables = Application.Run "" & workbookname & "" & "!" & "" & macroname & ""

Hello, is there a workaround if workbook is XLSB? Thank you in advance!

 

[RoryA]

xlsb will work - it does not have to be xlsm specifically, just needs to be macro-enabled, obviously.

 

[rikuouduou]

xlsb will work - it does not have to be xlsm specifically, just needs to be macro-enabled, obviously.

It worked just fine. Thanks!

 

[Doug Mutzig]

Good morning all,

I have been researching an issue I have and came across this thread. I think I am close to working the issue out, but have encounted on hiccup.

Background:
I have a workbook that you can save selected worksheets to a new workbook with (code from Dante Amor - Here: https://www.mrexcel.com/forum/excel...g-multiple-selected-saved-new-workbook-2.html). I am now working to remove all of the named ranges from the newly created workbook using code from here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=219.

The issue is that no macros are copied over to the new workbook (done on purpose) so I have to call a macro in the originating workbook to run during the new workbook creation process. I have successfully done this with the following code

Application.Run ("'MBU_Tracking_Workbook-v62-unlocked.xlsm'!GlobalRemNamedRanges")

Issue: The problem I am facing is the the primary workbook will be saved and used by several different end user groups. Each group is supposed to add their unit abbreviation to the name of the workbook (i.e. TrackingWorkbook.xlsm will become MBU-TrackingWorkbook.xlsm, SPD-TrackingWorkbook.xlsm, etc.) so the code that calls the originating workbook needs to somehow know or get the correct name. For example if I am in MBU-TrackingWorkbook.xlsm and want to export sheets the macro needs to know to call MBU-TrackingWorkbook.xlsm and not some other workbook that will not be available or open. The name of the workbook is not a dynamic variable, but I do want to have the macro able to tell which workbook it should call instead of hardcoding a name that will cause issues or make administration a nightmare.

I have tried the code:

[COLOR=#333333][I]Application.Run ("'" & workbookname & "'!macroname")[/I][/COLOR]

mycode:

Application.Run "'" & TrackingWorbook.Name & "'!GlobalRemNameRanges"

but received errors.

Does anyone know how I can set it up so that the call to the macro in the newly created workbook goes to the correct (i.e. open and originating workbook) workbook?

 

[RoryA]

That syntax is correct. What errors did you receive?