How can I change the value of Class properties when stored in a Collection

user_2872922 0
Viewed 299

I want to store a class in a Collection and be able to alter the properties of the class without having to remove the collection item and add it back again.

My research has shown that the Item itself cannot be changed without the remove/replace operation, but what about the properties of the item.

vba excel
1 Answers

The code below shows how to do this. When you run the macro, the debug window will show both the initial and the changed values of the stored object.

If you do not use the Key, you need to reference the collection item by its index number.

Class Module

Option Explicit
'RENAME cNodes

Private pNode1 As Variant
Private pNode2 As Variant

Public Property Get Node1() As Variant
    Node1 = pNode1
End Property
Public Property Let Node1(Value As Variant)
    pNode1 = Value
End Property

Public Property Get Node2() As Variant
    Node2 = pNode2
End Property
Public Property Let Node2(Value As Variant)
    pNode2 = Value
End Property

Regular Module

Option Explicit
Sub ChangeCollectionItem()
    Dim COL As Collection, cN As cNodes
    Dim sKey As String
    Dim a, b

Set COL = New Collection
    a = 1
    b = 2
    Set cN = New cNodes
    With cN
        .Node1 = a
        .Node2 = b
        sKey = a & "|" & b

        COL.Add Key:=sKey, Item:=cN

        Debug.Print COL(sKey).Node1, COL(sKey).Node2 '-->  1      2

        With COL(sKey)
            .Node1 = .Node1 * 10
            .Node2 = .Node2 * 5
        End With

        Debug.Print COL(sKey).Node1, COL(sKey).Node2 '-->  10      10
   End With

End Sub
user_28729220
Related