如何从 VBA 中的另一个特定工作簿调用函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10012463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call function from another specific workbook in VBA?
提问by Gutti
I would like to know if there is a way to call a VBA function or method from another specified workbook's module as it is possible for a specific worksheet without using the Application.Run
我想知道是否有办法从另一个指定的工作簿的模块调用 VBA 函数或方法，因为可以在不使用 Application.Run 的情况下对特定工作表进行调用
For the worksheet I can call for example :
对于工作表，我可以调用例如：
ActiveSheet.MyTest()
if MyTest is defined in the sheet module
如果在工作表模块中定义了 MyTest
But I would like to call a function which is defined in a module
但我想调用一个在模块中定义的函数
I tried :
我试过 ：
ActiveWorkbook.MyTestModule()
ActiveWorkbook.VBProject.VBComponents("MyModule").MyTestModule(myArg)
which don't work generating an error Object does not support this method
这不起作用产生错误对象不支持此方法
I could call
我可以打电话
Application.Run(ActiveWorkbook.name & "!MyTestModule", myArg)
But I am not sure of the error handling of the Application.Run and I would find cleaner to run directly the method
但我不确定 Application.Run 的错误处理，我会发现直接运行该方法会更干净
回答by Brian Camire
In the workbook you want to call from (I'll call this A), you could add a reference to the workbook that you want to call to (I'll call this B) as follows:
在您要调用的工作簿中（我将其称为 A），您可以添加对要调用的工作簿的引用（我将其称为 B），如下所示：
- In workbook A, open the Microsoft Visual Basic for Applications window (for example, by pressing Alt+F11).
- Select Tools, References.
- In the References dialog that appears, choose Browse.
- In the Add Reference dialog that appears, choose Microsoft Excel Files from the Files of type box, select the file that you want to call (B), and choose Open.
- Choose OK to close the References dialog.
- 在工作簿 A 中，打开 Microsoft Visual Basic for Applications 窗口（例如，按Alt+ F11）。
- 选择工具、参考。
- 在出现的“引用”对话框中，选择“浏览”。
- 在出现的添加引用对话框中，从文件类型框中选择 Microsoft Excel 文件，选择要调用的文件 (B)，然后选择打开。
- 选择确定关闭引用对话框。
In file A, you should then be able to call public module-level functions in file B as if they were in file A. To resolve any naming conflicts, you can prefix calls by the "Project Name" for file B as specified in the General tab of the Project Properties dialog (accessible via the Properties command in the Microsoft Visual Basic for Applications Tools menu). For example, if the "Project Name" for file B was "VBAProjectB", you could call function F from file A using the syntax VBAProjectB.F.
在文件 A 中，您应该能够像在文件 A 中一样调用文件 B 中的公共模块级函数。要解决任何命名冲突，您可以使用文件 B 中指定的“项目名称”作为调用前缀项目属性对话框的常规选项卡（可通过 Microsoft Visual Basic for Applications 工具菜单中的属性命令访问）。例如，如果文件 B 的“项目名称”是“VBAProjectB”，则可以使用语法 VBAProjectB.F 从文件 A 调用函数 F。
回答by RIck_R
By the way, this also works if you want to access a custom data type in another workbook:
顺便说一句，如果您想访问另一个工作簿中的自定义数据类型，这也适用：
' In workbook ABC, project name Library
Public Type Book_Data
Title As String
Pub_Date As Date
Pub_City As String
End Type
' In workbook DEF (after a ref to Library)
Dim Book_Info As Library.Book_Data
Book_Info.Title = "War and Peace"
Debug.Print Book_Info.Title
