How to call sheet-specific macro from one Excel workbook in another?

Question

I'm trying to call an Excel macro that's in another workbook. It's a sheet-specific macro, but the syntax given by Microsoft documentation and researching on the web, only gives a way to access a macro by workbook only. That syntax is:

Application.Run ("testworkbook.xls!macroname")

What I need to do is have a sheet reference in there also, something like:

Application.Run ("testworkbook.xls!Sheet1.macroname")

I've tried this and MANY other variations, including having single or double quotes in there, but I always get the message that the macro cannot be found.

Edit: With all the clues and much testing I found the answer. You can access Sheet-specific subs, but you have to use the canonical name like 'Sheet1', you can't use the actual sheetname. Apparently other workbooks don't access that information.

So the format above works as long as you don't try to use the sheetname (and you may have to single quote the workbook name (by concatenating CHR(39) to either end).

Answer 1

I know you've figured this out, probably after much hair-tearing and coffee, but I wanted to:

• Give you more details on why this is
• Provide you with a way you could use your sheet's name to get what you want.

First of all, the worksheet name you are wanting is not the same thing as the code module name. So in your VBE, you see that the name of the code module is "Sheet1", but if may have a different property of Name which is different, for example, "MySheet1" (or it also may be the same).

In order to get it by name, you'll have to do some loops, change security settings, etc. If that's what you're after (this works well in smaller environments because of the security setting issue), here you go as an example:

1. Change your security settings to trust programmatic access to VBA Projects. In Excel 2007, go to Orb | Excel Options | Trust Center | Trust Center Settings | Macro Settings and then enable "Trust access to the VBA project model"

2. Create a workbook with one worksheet. Rename it "MySheet1". Open the VBE (Alt+F11) and in "Sheet1 (MySheet1)" create a sub routine, call it TimesTen and in the code just put Debug.Print 10 * 10. Like this:

   Sub TimesTen()
       Debug.Print 10 * 10
   End Sub
   

3. Save the file as an macro-enabled document and call it "MacroXSLX.xlsm". Leave it open.

4. Open a new Excel document, navigate to it's VBE and in a new macro anywhere, create a sub called Test. In the body of that code, put this: .

   Sub test()
   Dim SheetName As String
   SheetName = "MySheet1"
   Dim wb As Workbook
   Set wb = Workbooks("MacroXSLX.xlsm")
   For Each VBComp In wb.VBProject.VBComponents
       If VBComp.Properties.Item("Name").Value = SheetName Then
           Application.Run (wb.Name & "!" & VBComp.Name & ".TimesTen")
       End If
   Next
   End Sub
   

5. Press F5 to run test and you should see 100 in the Immediate window.

You can see in #4 that I'm looping through all the components (Modules, Classes, etc.) looking for the one that has a Name property that has a value of MySheet1. Once I have that, I can then get the name of that component, which in this case is Sheet1 and use that to construct my string that will run the sheet's macro in MacroXSLX.xlsm. The code can be cleaned up further to exit the For statement when you've found what you want, etc.

As mentioned above, the only real draw-back to this is the security settings piece and ensuring you have programmatic access to the VBAProject - fine on one to ten computers, but could be a hassle if you have to ensure more than that are always set correctly.

Answer 2

can you adjust your macro to accept a sheetname as a parameter? Then when you call it within your original workbook's sheet you could just call it as MyMacro(me.name)? Then when you call it from your other workbook, you could just call it as application.run("testworkbook.xls!MyMacro","sheetname") where "sheetname" is your parameter.

I'm not sure if there is any other way to do it.