vba 如何从另一个 Excel 工作簿中调用特定于工作表的宏？

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

提问by Lance Roberts

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:

我正在尝试调用另一个工作簿中的 Excel 宏。这是一个特定于工作表的宏，但是 Microsoft 文档和网络研究给出的语法仅提供了一种仅通过工作簿访问宏的方法。该语法是：

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.

编辑：通过所有线索和大量测试，我找到了答案。您可以访问特定于工作表的子项，但必须使用诸如“Sheet1”之类的规范名称，而不能使用实际的工作表名称。显然其他工作簿无法访问该信息。

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).

因此，只要您不尝试使用工作表名称（并且您可能必须单引号工作簿名称（通过连接CHR(39)到任一端），上述格式就可以工作。

回答by Todd Main

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 propertyof Namewhich is different, for example, "MySheet1" (or it also may be the same).

首先，您想要的工作表名称与代码模块名称不同。因此，在您VBE，你看到的代码模块的名称是“工作表Sheet1”，但如果可以有不同性质的Name是不同的，例如，“ MySheet1”（或者它也可以是相同的）。

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 TimesTenand 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 testand you should see 100in the Immediate window.

1. 更改您的安全设置以信任对 VBA 项目的编程访问。在 Excel 2007 中，转到 Orb | Excel 选项 | 信托中心 | 信任中心设置 | 宏设置，然后启用“信任对 VBA 项目模型的访问”

2. 用一张工作表创建一个工作簿。将其重命名为“ MySheet1”。打开 VBE (Alt+F11) 并在“ Sheet1 (MySheet1)”中创建一个子例程，调用它TimesTen并在代码中放置Debug.Print 10 * 10. 像这样：

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

3. 将该文件另存为启用宏的文档并将其命名为“ MacroXSLX.xlsm”。让它打开。

4. 打开一个新的 Excel 文档，导航到它的 VBE 并在任何地方的新宏中，创建一个名为 Test. 在该代码的正文中，输入：

   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. 按 F5 运行test，您应该在“立即”窗口中看到100。

You can see in #4 that I'm looping through all the components (Modules, Classes, etc.) looking for the one that has a Nameproperty that has a value of MySheet1. Once I have that, I can then get the name of that component, which in this case is Sheet1and 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.

您可以在 #4 中看到，我正在遍历所有组件（模块、类等），寻找Name具有值为MySheet1的属性的组件。一旦我有了它，我就可以获得该组件的名称，在这种情况下是Sheet1并使用它来构造我的字符串，该字符串将在MacroXSLX.xlsm 中运行工作表的宏。当你找到你想要的东西时，可以进一步清理代码以退出 For 语句等。

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.

如上所述，唯一真正的缺点是安全设置部分并确保您可以通过编程访问 VBAProject - 在一到十台计算机上很好，但如果您必须确保始终设置多于这些，则可能会很麻烦正确。

回答by Fink

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.

您可以调整您的宏以接受工作表名称作为参数吗？然后，当您在原始工作簿的工作表中调用它时，您可以将其称为 MyMacro(me.name)？然后，当您从其他工作簿调用它时，您可以将其称为 application.run("testworkbook.xls!MyMacro","sheetname") 其中“sheetname”是您的参数。

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

我不确定是否还有其他方法可以做到。

回答by guitarthrower

Does the VBA project have password protection? Also, is the sub a private sub? If so, then I don't believe the macro will be found.

VBA 项目有密码保护吗？另外，sub是私人sub吗？如果是这样，那么我不相信会找到该宏。

回答by jveirasv

You can use this:

你可以使用这个：

Run "'" & MySheet.Parent.name & "'!" & MySheet.CodeName & ".macroName"