vba 从不同的工作簿调用 Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28741828/
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
Call an Excel macro from a different Workbook
提问by Envite
I have a workbook HasMacros.xlsm and a workbook HasData.xlsm
我有一个工作簿 HasMacros.xlsm 和一个工作簿 HasData.xlsm
In HasMacros.xlsm I add a module named Mod1 with this code only:
在 HasMacros.xlsm 中，我仅使用以下代码添加了一个名为 Mod1 的模块：
Sub testmacro()
MsgBox ("Test")
End Sub
In HasData.xlsm I add a module named Mod2 with this code only:
在 HasData.xlsm 中，我仅使用以下代码添加了一个名为 Mod2 的模块：
Sub testmacro2()
'XXX
End Sub
Nothing else opened in Excel.
在 Excel 中没有打开任何其他内容。
I want to call testmacro from testmacro2 where XXX is written.
我想从写入 XXX 的 testmacro2 调用 testmacro。
This fails with immediate compilation error:
这失败并立即出现编译错误：
Sub testmacro2()
testmacro()
End Sub
This fails with compilation error "Sub of function not defined" on execute:
这在执行时因编译错误“未定义函数子集”而失败：
Sub testmacro2()
Call testmacro
End Sub
This fails with immediate compilation error:
这失败并立即出现编译错误：
Sub testmacro2()
Mod1.testmacro()
End Sub
This fails on execute:
这在执行时失败：
Sub testmacro2()
Call Mod1.testmacro
End Sub
I tried How to call function from another specific workbook in VBA?but I get
我试过如何从 VBA 中的另一个特定工作簿调用函数？但我明白了
Name conflicts with existing module, project, or object library
名称与现有模块、项目或对象库冲突
How do I call a macro in HasMacros.xlsm from VBA code in HasData.xlsm
如何从 HasData.xlsm 中的 VBA 代码调用 HasMacros.xlsm 中的宏
回答by Rory
You can also change the name of the VBA project in Workbook HasMacros.xlsmto something other than VBAProjectand then set a reference (Tools - References in the VB Editor) to that project from the HasData.xlsm project. That will then allow you to directly call the other macro without using Run.
您还可以在改变VBA项目的名称Workbook HasMacros.xlsm比其他的东西VBAProject，然后设置一个引用（工具-在VB编辑器引用）从HasData.xlsm项目该项目。这将允许您直接调用另一个宏而不使用Run.
回答by TheBear
Application.Run("'Workbook HasMacros.xlsm'!testmacro")
Application.Run("'Workbook HasMacros.xlsm'!testmacro")
回答by BjH
I didn't want to have to hardcode the name of the master workbook that contained my subroutine into my slave workbook. I had already used the registry to store key information about my master workbook, and had routines that would extract that information.
我不想将包含我的子程序的主工作簿的名称硬编码到我的从属工作簿中。我已经使用注册表来存储有关我的主工作簿的关键信息，并且具有提取该信息的例程。
When I went to use Application.Run I only found examples which showed a hardcoded string for the slave workbook to access the Master workbook method. I was able to come up with a solution which built the string on the fly.
当我去使用 Application.Run 时，我只找到了一些示例，这些示例显示了从工作簿访问主工作簿方法的硬编码字符串。我能够想出一个解决方案，可以即时构建字符串。
This solution still hard codes the the Module and Sub name, but you get the general idea and can adapt it to meet your needs.
此解决方案仍然对模块和子名称进行硬编码，但您已了解总体思路并可以对其进行调整以满足您的需求。
============
Master Workbook: Master.xlsm
Modules: GlobalMethods
Sub: SetThePath(string)
Slave Workbook: Slave.xlsm
Modules: Init
Sub: SetUpMyWorld
==============
Sub SetUpMyWorld() ' Defined in the Slave Workbook
Dim myMaster as string
Dim myMasterWB as Workbook
Dim SlaveWB as Workbook
Set SlaveWB = ThisWorkbook
myMaster = GetMyMasterWBName() ' This is another module in Slave
If myMaster <> "" Then
Set myMasterWB = Workbooks.Open(myMaster)
On Error GoTo Complaint1
Dim sInvoke as String
sInvoke = Chr(39) & myMaster & Chr(39) & "!GlobalMethods.SetThePath"
Application.Run sInvoke, SlaveWB.Path
Exit Sub
End If
Complaint1:
MsgBox "Could not open your Master workbook: " + myMaster + ". Oops!"
End Sub
