VBA class module: get property from an object using another property

Question

All, I am setting-up a class module structure in VBA to add plans that have multiple milestones, but I'm quite new to it. I did the following:

• A class module called 'Plan' that contains a 'name' property (string) and a 'Milestones' property (class Milestones).
• This milestones class module is a collection of objects of a class module called 'Milestone'.
• The 'Milestone' class has a 'name' property and a 'value' property.

So in my module I am now specifying the milestones for a specific plan:

Plan.Milestones.Add "MilestoneA", Cells(i, 5)
Plan.Milestones.Add "MilestoneB", Cells(i, 7)
...

Until now everything is fine. Now for MilestoneC I would like to know the value of MilestoneA. How do I get the value for the Milestone with name 'MilestoneA'.

I know the below code would give me the answer, but I don't want to hardcode 'item(1)' (I want to use the name):

Plan.Milestones.Item(1).Value

In the clsMilestones class:

Private prvt_Milestones As New Collection

Property Get Item(Index As Variant) As clsMilestone
    Set Item = prvt_Milestones(Index)
End Property

Sub Add(param_Name As String, param_Value As String)

    Dim new_milestone As clsMilestone
    Set new_milestone = New clsMilestone

    new_milestone.Name = param_Name
    new_milestone.Value = param_Value

    prvt_Milestones.Add new_milestone
End Sub

Answer 1

Your Milestones class is a collection class. By convention, collection classes have an Item property that is the class' default member. You can't easily specify a class' default member in VBA, but it's not impossible.

Export the code file, open it in Notepad. Locate your Public Property Get Item member and add a VB_UserMemId attribute - while you're there you can add a VB_Description attribute, too:

Public Property Get Item(ByVal Index As Variant) As Milestone
Attribute Item.VB_UserMemId = 0
Attribute Item.VB_Description = "Gets the item at the specified index, or with the specified name."
    Set Item = prvt_Milestones(Index)
End Property

The UserMemId = 0 is what makes the property the class' default member - note that only one member in the class can have that value.

Don't save and close just yet.

You'll want to make your collection class work with a For Each loop too, and for that to work you'll need a NewEnum property that returns an IUnknown, with a number of attributes and flags:

Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_Description = "Gets an enumerator that iterates through the collection."
Attribute NewEnum.VB_UserMemId = -4
Attribute NewEnum.VB_MemberFlags = "40"    
    Set NewEnum = prvt_Milestones.[_NewEnum]
End Property

Note that your internal encapsulated Collection has a hidden member with a name that begins with an underscore - that's illegal in VBA, so to invoke it you need to surround it with square brackets.

Now this code is legal:

Dim ms As Milestone
For Each ms In Plan.Milestones
    Debug.Print ms.Name, ms.Value ', ms.DateDue, ...
Next

Save the file, close it, and re-import it into your project.

Since you're populating the collection using a string key (at least that's what your Add method seems to be doing), then the client code can use either the index or the key to retrieve an item.

And now that Item is the class' default member, this is now legal:

Set milestoneA = Plan.Milestones("Milestone A").Value

Note that your Add method needs to specify a value for the Key argument when adding to the internal collection - if you want the items keyed by Name, use the Name as a key:

Public Sub Add(ByVal Name As String, ByVal Value As Variant)
    Dim new_milestone As Milestone
    Set new_milestone = New Milestone

    new_milestone.Name = Name
    new_milestone.Value = Value

    prvt_Milestones.Add new_milestone, Name
End Sub

Answer 2

Use a dictionary of Milestone classes in the plan class and set the key to be the "Milestone_x" and the item to be a milestone class

Then you can say Plan.Milestones("Milestone99")