如何以与 VB.NET 类似的方式在 VBA 中的类对象模块之间使用比较方法？

How to use comparison methods between class object modules in VBA in a similar manner as VB.NET?

提问by Arie

Due to new project in VBA I moved from VB.NET, to be honest don't really know how to deal between objects classes here. What I want to reach is comparing objects between different class object modules.

由于 VBA 中的新项目，我从 VB.NET 迁移过来，老实说，我真的不知道如何处理这里的对象类。我想要达到的是比较不同类对象模块之间的对象。

e.g

例如

class Employee
     properties: Name, Age
     point is: compare Names between two Employees

class Employee
     properties: Name, Age
     point is: 比较Name两个Employees之间的s

classes: Employeeand Manager
point is: compare Namefrom Employeewith Nameof Manager

类：员工和经理
一点是：比较Name从员工用Name的经理

I know how to in VB.NET, but how do I compare properties of different class module objects in VBA?

我知道如何在 VB.NET 中，但如何比较 VBA 中不同类模块对象的属性？

回答by

VBA doesn't support class polymorphismso I recommend to change the way you're thinking about the Employeeand Managerclasses.

VBA 不支持类多态性，因此我建议更改您对Employee和Manager类的思考方式。

You can't have an Employeeclass as a base classand then a separate Managerclass that derivesfrom Employee. They could be 2 separateclasses implementing a common interface.

你不能有一个Employee类的基类，然后一个单独的Manager类导出的Employee。它们可以是实现通用接口的2 个单独的类。

I will talk about it in detail in a bit. Let's now go through a few examples...

稍后我将详细讨论它。现在让我们通过几个例子...

---

---

↓ Easy approach ↓

↓ 简单的方法 ↓

---

---

A baseclass (Person) and child classes which derive from the base class. (applies to C#, VB.NET, etc)一个base类 ( Person) 和从基类派生的子类。（适用于 C#、VB.NET 等）

but in VBA you have to think of it like this:

但在 VBA 中，你必须这样想：

A base class which exposes an enum property describing the position.

公开描述位置的枚举属性的基类。

Something like

就像是

This is the easiest way to have a class exposing some properties. It allows you to add your Personobjects into a collection and iterate over using the easy for eachloop with Intellisense!

这是让类公开某些属性的最简单方法。它允许您将Person对象添加到集合中，并使用Intellisense的简单for each循环进行迭代！

Properties comparison system would be very very easy

属性比较系统将非常容易

note: same applies to enum as its implicitly converted to a number

注意：同样适用于枚举，因为它隐式转换为数字

---

---

↓ More complex approach ↓

↓ 更复杂的方法 ↓

---

---

Two separate classes which both expose public properties. For instance you have an Employeeand Managerclasses which both implement a PersonInterfaceand an additional Comparerclass exposing a Compare()method

两个单独的类，它们都公开公共属性。例如，您有一个Employee和Manager类，它们都实现了一个Person接口和一个Comparer公开Compare()方法的附加类

In your VBA Project you need 4 class modules and a standard module

在您的 VBA 项目中，您需要 4 个类模块和一个标准模块

Person(this is your Interface)

Person（这是你的界面）

Public Property Get Name() As String
End Property

Public Property Get Age() As Long
End Property

this class is the interface which both the Employeeand Managerboth need to implement to share some common functions (getters for Names and Ages). Having the interface allows you to do the for each loop using the interface type variable as the enumerator. You will see in a minute.

此类是Employee和Manager都需要实现以共享一些公共功能（名称和年龄的getter）的接口。拥有接口允许您使用接口类型变量作为枚举器执行 for each 循环。你会在一分钟内看到。

Employeeand Managerare identical. Obviously you may modify them to suit your real life solution.

Employee并且Manager是相同的。显然，您可以修改它们以适合您现实生活中的解决方案。

Implements Person

Private name_ As String
Private age_ As Long

Public Property Get Name() As String
    Name = name_
End Property

Public Property Let Name(ByVal Value As String)
    name_ = Value
End Property

Public Property Get Age() As Long
    Age = age_
End Property

Public Property Let Age(ByVal Value As Long)
    age_ = Value
End Property

Private Property Get Person_Name() As String
    Person_Name = Name
End Property

Private Property Get Person_Age() As Long
    Person_Age = Age
End Property

ComparerClsyou will use an instance of this class to compare two objects properties or references. You do not necessarily need to have a class for this but I prefer it that way.

ComparerCls您将使用此类的一个实例来比较两个对象的属性或引用。您不一定需要为此开设课程，但我更喜欢那样。

Public Enum ComparisonMethod
    Names = 0 ' default
    Ages = 1
    References = 2
End Enum

' makes names the default comparison method
Public Function Compare(ByRef obj1 As Person, _
                        ByRef obj2 As Person, _
                        Optional method As ComparisonMethod = 0) _
                        As Boolean

    Select Case method
        Case Ages
            Compare = IIf(obj1.Age = obj2.Age, True, False)
        Case References
            Compare = IIf(obj1 Is obj2, True, False)
        Case Else
            Compare = IIf(obj1.Name = obj2.Name, True, False)
    End Select

End Function

And your Module1code

还有你的Module1代码

Option Explicit

Sub Main()

    Dim emp As New Employee
    emp.Name = "person"
    emp.Age = 25

    Dim man As New Manager
    man.Name = "manager"
    man.Age = 25

    Dim People As New Collection
    People.Add emp
    People.Add man

    Dim individual As Person
    For Each individual In People
        Debug.Print TypeName(individual), individual.Name, individual.Age
    Next

End Sub

run the Main()sub and check out the results in the Immediate Window

运行Main()sub 并在Immediate Window 中查看结果

The best part of the above code is the fact that you are creating a reference variable of the Personinterface. It allows you to loop through all items in the collection that implement the interface. Also, you can use the Intellisense which is great if you have had many more properties and functions.

上面代码最好的部分是您正在创建Person接口的引用变量。它允许您遍历集合中实现该接口的所有项目。此外，如果您有更多的属性和功能，您可以使用 Intellisense，这非常棒。

---

---

Comparison

比较

---

---

Take a look again at the code in the ComparerClsclass

在代码再看一看ComparerCls类

I hope you see now why I have separated this to be a class. Its purpose is just to take care of the way the objects are being compared. You can specify the Enum order and modify the Compare()method itself to compare differently. Note the Optional parameter which allows you to call the Compare method without the method of comparison.

我希望你现在明白为什么我把它分开来成为一个类。它的目的只是照顾比较对象的方式。您可以指定 Enum 顺序并修改Compare()方法本身以进行不同的比较。请注意 Optional 参数，它允许您在没有比较方法的情况下调用 Compare 方法。

Now you can play around passing different parameters to the compare function. See what the results are like.

现在您可以尝试将不同的参数传递给比较函数。看看结果如何。

Try combinations:

尝试组合：

emp.Name = "name"
man.Name = "name"

Comparer.Compare(emp, name, Names)
Comparer.Compare(emp, name, References)

Comparer.Compare(emp, emp, References)

---

---

If something is still unclear refer to this answer about the Implementskeyword in VBA

如果仍有不清楚的地方，请参阅有关ImplementsVBA 中关键字的答案