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

Mathieu Guindon · Accepted Answer · 2017-10-25 16:46:47Z

7

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

edited Oct 25, 2017 at 16:46

answered Oct 25, 2017 at 15:11

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

Note that I dropped the cls prefix on the class name. I've never seen any class in any object model with a cls prefix, for one, and it reeks of Systems Hungarian notation, which was nothing more than an unfortunate misunderstanding and should never have come to be.

KacireeSoftware Over a year ago

+1 Very nicely done. I would sure be interested (as I'm sure a lot of people would) in a little more detail on the NewEnum property you added.

KacireeSoftware Over a year ago

Ok I just tested it and it's beautiful. Thanks Mat's Mug

Nathan_Sav · Accepted Answer · 2017-10-25 14:38:58Z

4

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")

answered Oct 25, 2017 at 14:38

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Comments

KacireeSoftware · Accepted Answer · 2017-10-25 14:42:48Z

0

Add a property to the Milestones class that returns the milestone based on the name:

Property Get SelectByName(strMilestoneName as string) as clsMilestone
     Dim vIndex
     'Add code here to find the index of the milestone in question
     vIndex = ????????

     Set SelectByName = prvt_Milestones(Index)
End Property

OR

Edit the Item Property to Allow selection by either Index or Name:

Property Get Item(Index As Variant) As clsMilestone
    If isNumeric(Index) then
       Set Item = prvt_Milestones(Index)
    Else
       'Find Item based on Name
        Dim vIndex
        vIndex = ?????
       Set Item = prvt_Milestones(vIndex)
     End If
End Property

answered Oct 25, 2017 at 14:42

KacireeSoftware

8086 silver badges20 bronze badges

2 Comments

AB123 Over a year ago

I suppose this would mean I still need to loop through all milestones to find the corresponding item, right?

Mathieu Guindon Over a year ago

@Hans if you're specifying a key when you add the items to the collection, you don't need to iterate anything or to use a dictionary. But you're not showing us your Add method, so we all have to make assumptions.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related