VBA does not support class polymorphism, so I recommend changing the way you think about the Employee and Manager classes.
You cannot have an Employee class as a base class, and then a separate Manager class that derives from Employee . They can be 2 separate classes that implement a common interface.
I will talk about this in detail. Now let's look at a few examples ...
↓ A simple approach ↓
A base class ( Person ) and child classes that come from a base class. (applies to C #, VB.NET, etc.)but in VBA you should think of it like this:
The base class that provides the enum property that describes the position.
Something like
This is the easiest way to have a class displaying some properties. It allows you to add Person objects to the collection and iterate over them with a simple for each loop with Intellisense!
The property comparison system will be very simple.
note: the same applies to an enumeration as its implicitly converted to a number
↓ A more complex approach ↓
Two separate classes that display public properties. For example, you have Employee and Manager classes that implement the Person Interface and an additional Comparer class by exposing the Compare() method
In your VBA project you need 4 class modules and a standard module
Person (this is your interface)
Public Property Get Name() As String End Property Public Property Get Age() As Long End Property
this class is an interface that both types of Employee and Manager must implement in order to share some common functions (getters for names and ages). Having an interface allows you to use a variable of an interface type as a counter for each cycle. You will see in a minute.
Employee and Manager identical. Obviously, you can change them to suit your decision in real life.
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
ComparerCls you will use an instance of this class to compare two properties or object references. You do not need to have a class for this, but I prefer it that way.
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 Module1 code
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() sublink and check the results in the immediate viewer
The best part of the code above is that you create a reference variable for the Person interface. It allows you to scroll through all the elements of the collection that implement the interface. In addition, you can use Intellisense, which is great if you had many other properties and functions.
Comparison
Take another look at ComparerCls class code
Hopefully now you see why I shared this as a class. Its purpose is simply to take care of how objects are compared. You can specify the order of Enum and change the Compare() method to compare in different ways. Note the optional parameter, which allows you to call the comparison method without the comparison method.
Now you can play around passing various parameters to the comparison function. See how the results look.
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 else is unclear, refer to this answer about the Implements keyword in VBA