什么是 VBA 中的公共对象模块？

What is a public object module in VBA?

提问by leeand00

I'm trying to get as close to function pointers / abstract classes as I can in VBA.

我试图在 VBA 中尽可能接近函数指针/抽象类。

I have a class called VerificationManagerand verifies a bunch of cells in a couple of spreadsheets match up. This will be done in different ways depending on the information and spreadsheets being used with it.

我有一个名为VerificationManager并验证几个电子表格中的一堆单元格匹配的类。这将根据所使用的信息和电子表格以不同的方式完成。

I'd like to be able to make the code reusable by specifying a method to be called in a string using the Application.Runfunction. So I can rewrite the function that changes.

我希望能够通过使用Application.Run函数指定要在字符串中调用的方法来使代码可重用。所以我可以重写改变的函数。

Now if I was using Java or C# I would be able to extend an abstract class and rewrite the internals to the function. If I was using JavaScript I could store a function in a variable and pass the variable to the class and call it from there.

现在，如果我使用 Java 或 C#，我将能够扩展抽象类并将内部结构重写为函数。如果我使用 JavaScript，我可以将一个函数存储在一个变量中并将该变量传递给类并从那里调用它。

Inside my class I have a public property called "verificationModule" which I set to the name of the function I want it to call.

在我的班级中，我有一个名为“verificationModule”的公共属性，我将其设置为我希望它调用的函数的名称。

Sub VerifyWorkLocations(empLoc As EmployerLocation)
...
    For i = 0 To empLoc.numOfEmp
        Application.Run verificationModule, empLoc.taxdescmatch, empLoc.employees(i)
    Next i
...
End Sub

However, when I try to call Application.RunI receive the following error:

但是，当我尝试拨打电话时Application.Run，收到以下错误：

Compile Error:

"Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions"

编译错误：

“只有在公共对象模块中定义的用户定义类型才能被强制传入或传出变体或传递给后期绑定函数”

I already tried placing my User Defined Types in a Class Modulebut it basically said that a class module was the wrong place for a type.

我已经尝试将我的用户定义类型放在 a 中，Class Module但它基本上说类模块是类型的错误位置。

采纳答案by GSerg

The error comes from full-fledged VB, where you can create an ActiveX dll project, create a public class there and put a UDT into that class.

该错误来自成熟的 VB，您可以在其中创建 ActiveX dll 项目，在那里创建一个公共类并将 UDT 放入该类中。

In VBA, you use classes instead of UDTs when you need to coerce to or from a variant.

在 VBA 中，当您需要强制转换为变体或从变体强制转换时，您可以使用类而不是 UDT。

So just declare a class with all the fields you have in your UDT, and delete the UDT.

因此，只需声明一个包含 UDT 中所有字段的类，然后删除 UDT。

Alternatively, create a DLL in VB6 that would only contain the declaration of the type, and reference that dll from VBA. Or, if you're comfortable with IDL, just create a TLB file directly.

或者，在 VB6 中创建一个仅包含类型声明的 DLL，并从 VBA 引用该 dll。或者，如果您对 IDL 感到满意，只需直接创建一个 TLB 文件。

回答by David W

To add a module to a VBA application, try the following steps (assuming you've already entered the VBA IDE):

要将模块添加到 VBA 应用程序，请尝试以下步骤（假设您已经进入 VBA IDE）：

1. From the Project Explorer, right-click on your project.
2. Click on "Insert..."
3. Select "Module."
4. If no modules exist, a new folder within the VBA/Excel project will be created, named "Modules," and a module with a default name of "Module1" will be created.
5. Double-click the module to open it in the source editor.

1. 在 Project Explorer 中，右键单击您的项目。
2. 点击“插入...”
3. 选择“模块”。
4. 如果不存在模块，将在 VBA/Excel 项目中创建一个名为“Modules”的新文件夹，并创建一个默认名称为“Module1”的模块。
5. 双击模块以在源代码编辑器中打开它。

This is where you have to place UDT's. I believe this is because of the difference in the way such types are stored internally by VBA relative to the COM-style structure of elements/objects declared/manipulated as Classes...

这是您必须放置 UDT 的地方。我相信这是因为 VBA 在内部存储此类类型的方式与声明/操作为类的元素/对象的 COM 样式结构不同...