如何访问另一个Excel工作簿中定义的常量？
问题
如何访问另一个 Excel 工作簿中定义的公共常量？
安装
Workbook.xls
我的 VBA 代码在名为“MyWorkbook.xls”的当前 Excel 工作簿中运行。 它包含一些 VBA 代码，用于从单元格“A1”读取文件名（本例中为“OtherWorkbook.xls”）。然后，该代码应读取该其他工作簿中定义的常量并将其写入单元格“A2”我的代码如下所示：
Sub GetValueFromOtherWorkbook()
otherWorkbook = Range("A1").Value ' Value = "OtherWorkbook.xls"
Dim returnedValue As String
' Idea 1: Doesn't work
Set returnedValue = OtherWorkbook.xls!Module1.MY_CONSTANT
' Idea 2: Doesn't work either
Set returnedValue = Application.Run(otherWorkbook & "!Module1.MY_CONSTANT")
Range("A2").Value = returnedValue ' MY_CONSTANT = "testValue"
End Sub
OtherWorkbook.xls
第二个 Excel 工作簿名为“OtherWorkbook.xls”，如上所述。 它有一个名为 Module1 的 VBA 模块。 Module1 定义了一个公共常量。代码如下所示：
Public Const MY_CONSTANT As String = "testValue"
问题
MyWorkbook.xls 中的代码无法运行，它返回以下错误
运行时错误“424”
需要对象
我什至不知道该怎么处理它阅读帮助文档后。在另一个上下文中，我设法使用以下代码调用另一个工作簿中的例程：
otherWorkbookName = "OtherWorkbook.xls"
Application.Run (otherWorkbookName & "!Module1.SomeRoutine")
因此，调用例程有效，但访问公共常量则无效。
有什么想法我接下来可以尝试吗？
Question
How do I access a public constant defined in another Excel workbook?
Setup
Workbook.xls
My VBA code runs in the current Excel workbook called "MyWorkbook.xls".
It has some VBA code in it which reads a file name --"OtherWorkbook.xls in this case-- from cell "A1". Then, the code should read a constant defined in that other workbook and write it to cell "A2". Here is what my code looks like:
Sub GetValueFromOtherWorkbook()
otherWorkbook = Range("A1").Value ' Value = "OtherWorkbook.xls"
Dim returnedValue As String
' Idea 1: Doesn't work
Set returnedValue = OtherWorkbook.xls!Module1.MY_CONSTANT
' Idea 2: Doesn't work either
Set returnedValue = Application.Run(otherWorkbook & "!Module1.MY_CONSTANT")
Range("A2").Value = returnedValue ' MY_CONSTANT = "testValue"
End Sub
OtherWorkbook.xls
The second Excel workbook is called "OtherWorkbook.xls" as mentioned above.
It has a VBA module called Module1.
Module1 defines a public constant. The code looks like this:
Public Const MY_CONSTANT As String = "testValue"
Problem
The code from MyWorkbook.xls does not run, it returns the following error
Run-time error '424'
Object required
I don't quite know what to do with it even after reading the help documentation. In another context, I managed to call a routine in another workbook with this code:
otherWorkbookName = "OtherWorkbook.xls"
Application.Run (otherWorkbookName & "!Module1.SomeRoutine")
So, calling a routine works, but accessing a public constant does not.
Any ideas what I could try next?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
代码模块不是可以像工作表一样访问的对象。
您可以访问和操作它，但为此，您需要引用
Microsoft Visual Basic for Applications Extensibility库并允许访问 VBA 项目（工具 - 选项 - 安全性 - 宏安全性 - 受信任）发布者 - 信任对 VBA 项目的访问），之后您可以使用第二个工作簿的Workbook.VBProject属性。你不需要这样做。相反，您可以创建对其他工作簿的引用：在 VBA IDE 中，转到“工具”-“引用”，单击“浏览”并找到包含常量的工作簿。
如果这不好，您可以在该工作簿中创建一个仅返回常量值的方法，并按照您所演示的方式调用它。
A code module is not an object that can be accessed like a worksheet.
You can access and manipulate it, but in order to do that you need to reference the
Microsoft Visual Basic for Applications Extensibilitylibrary and allow access to the VBA project (Tools - Options - Security - Macro security - Trusted publishers - Trust access to VBA project), after which you can use theWorkbook.VBProjectproperty of the second workbook.You don't need to do this. Instead you can create a reference to the other workbook: in VBA IDE, go Tools - References, click
Browseand locate your workbook with the constant.If that's no good, you can create a method in that workbook that only returns the value of the constant, and call it as you have demonstrated.