Unwanted Object Updates inside VBA Collection

Question

I am trying to build a collection with a new key on top of an older collection. I've looked at other, similar topics but they all had particular issues that I couldn't relate to mine. My collection issue requires that I take an object from an older collection (Col_0), split it into a twin object with slightly different property values, and then place both into a new collection with a key based on one of the altered properties. My problem is that the altered twin overwrites the original inside the new collection. Here is a brief example:

Public Sub FillOutput(ID As String, Col_0 As Collection)
'---Obj is a class scope object not defined here.
'---ID is the item key for items in Col_0 collection
Dim Col_1 As Collection
Set Obj_0 = Col_0.Item(ID)
'---Now I have the right item from the collection
Loop_Count = 0
Set Col_1 = New Collection

While Loop_Count < 2
     If Loop_Count = 0 Then
         Obj_0.Color = "Red"
         Col_1.Add Obj_0, CStr(Obj_0.Color)

     ElseIf Loop_Count = 1 Then
         Obj_0.Color = "Blue"
         Col_1.Add Obj_0, CStr(Obj_0.Color)
     End If

Loop_Count = Loop_Count + 1
Wend 'Loop_count
End If
End Sub

I've tried a number of things such as creating an interim object that gets initialized and set to nothing inside the loop. As below:

ElseIf Loop_Count = 1 Then
         Set pObj = New clsObjTable
         pObj = Obj_0
         pObj.Color = "Blue"
         Col_1.Add pObj, CStr(pObj.Color)
         Set pObj = Nothing
     End If

Still doesn't work. My final collection keeps getting overwritten such that both items are "Blue". I have a suspicion that its about keys getting transferred and screwed up but I just don't know enough here. Any help or guidance here would be much appreciated!

Erik A · Accepted Answer · 2017-08-07T14:31:32.667

1

Collections do not actually contain copies of objects, they only contain references to them. If you change your object after adding it to the collection, the object referenced in your collection has the changes too.

The only way to change this behavior, is to manually copy the object. But that's not an easy thing to do. Read this question on cloning objects, but note that you can't just clone built-in objects.

You could try to create a helper function to move all relevant properties from your object to a fresh copy of an object of the same object type, and then return the copy, when struggling with a built-in Access object. But some objects have read-only or private properties, and are thus unclonable.

edited Aug 07 '17 at 14:31

answered Aug 07 '17 at 14:22

Erik A

31,639
12
42
67

This had crossed my mind as I use this same idea frequently in the rest of my code. It just bit me this time. The cloning link was informative, particularly memento patterns but is there a way to create something similar using a ByVal in a helper function? Or does VBA always force a ByRef for objects? – M. Hump Aug 07 '17 at 17:10
Yup. It's always forced ByRef. Else cloning using a helper function would've been WAY easier. If you create a clone helper function, you will need to manually copy all parameters between the old and the new object (you can of course iterate through parameter collections). – Erik A Aug 07 '17 at 17:13

Unwanted Object Updates inside VBA Collection

1 Answers1