Cloning a Collection

Question

I have two collections - collection1 and collection2

collection1 has a number of class objects in it and I am trying to fill collection2 with copies of the same objects using the following command:

Set collection2 = collection1

This doesn't give me the desired result though, because when I use

collection2.Remove 1

It removes the object at index 1 from both collections.

Below is the full code, which I hoped would output 10 objects in collection1 after removing one from collection2

Sub test()
    Dim collection1 As Collection
    Dim collection2 As Collection
    Dim testObj As Worksheet
    Dim i As Integer

    Set collection1 = New Collection
    Set collection2 = New Collection

    For i = 1 To 10
        collection1.Add testObj
    Next i

    Set collection2 = collection1
    collection2.Remove 1
    Debug.Print collection1.Count
End Sub

I tried the code below and it works but I'm looking to avoid filling both collections one by one if possible:

...
For i = 1 To 10
    collection1.Add testObj
    collection2.Add testObj
Next i
...

The reason I'm not so keen on this option is because ultimately I intend to use multiple collections, manipulating them and taking copies at various points so I would end up with lots of for loops in my code, rather than just one.

There is no copy function for a collection. This: Set collection2 = collection2 just makes a second reference to the Collection object. Your best bet is what you want to avoid IE filling both at the same time. — Sorceri, Dec 09 '16 at 16:59
@Sorceri, I added some detail as to why I didn't want to fill them both at the same time - ultimately I wouldn't be filling the collections at the same time but a number of different times — CallumDA, Dec 09 '16 at 17:01
its not possible. Collections in VBA are reference types. read this : http://net-informations.com/faq/general/valuetype-referencetype.htm — cyboashu, Dec 09 '16 at 17:01
You can create a function to "clone" a collection and return the copy (as long as you don't require keys) — Tim Williams, Dec 09 '16 at 17:03
Unfortunately, you can't even create a clone function for Collections because there isn't a way to access the keys. If you need a clone-able generic container, use a `Scripting.Dictionary`. — Comintern, Dec 09 '16 at 17:03
http://stackoverflow.com/questions/4805475/assignment-of-objects-in-vb6/4805812#4805812 — Tim Williams, Dec 09 '16 at 17:04

Mathieu Guindon · Accepted Answer · 2023-08-19T01:49:04.250

I am trying to fill collection2 with copies of the same objects using the following command:
Set collection2 = collection1

Yet that's not what that command does. The Set keyword doesn't "copy objects", and doesn't automagically know (or even care) that it's dealing with a Collection object that contains items.

The Set keyword assigns a reference. Nothing more, nothing less.

So what Set collection2 = collection1 does, is, literally:

Take the pointer for the collection1 object, and replace whatever collection2 is referring to with that.

That Set instruction is effectively discarding the object reference that was created here:

Set collection2 = New Collection

Set does not "copy objects" and cannot "fill" collection2 - you're overwriting its object reference, making the collection2 variable point to the same object as the collection1 variable does.

And since both pointers point to the same object... removing an item using either Collection is indeed going to be removing it from "both" collections, ...because there's only one collection object involved.

The hidden (undocumented?) ObjPtr function can help shed some light on what's going on:

Set collection1 = New Collection
Debug.Print "collection1: " & ObjPtr(collection1)

Set collection2 = New Collection
Debug.Print "collection2: " & ObjPtr(collection2)

'the debug output contains 2 different addresses at this point

Set collection2 = collection1
Debug.Print "collection1: " & ObjPtr(collection1)
Debug.Print "collection2: " & ObjPtr(collection2)

'now the debug output clearly shows that 
'both collection1 and collection2 are pointing to the same object

If you need copies of a collection, you need a function that takes a collection and returns a brand new object:

Public Function CopyCollection(ByVal source As Collection) As Collection
    Dim result As New Collection
    Dim item As Variant
    For Each item In source
        result.Add item
    Next
    Set CopyCollection = result
End Function

As already noted in comments though, this will only work if your collections aren't keyed. Because of how limited a Collection is (you can't iterate its keys), you'll have to use a Scripting.Dictionary instead, if you need to clone a keyed collection.

score 1 · Answer 2 · answered Dec 09 '16 at 17:05

1

First of all, you dont need to Dim collection1 As Collection and Set collection1 = New Collection. This can be replaced by Dim collection1 As New Collection

Now, what you are doing is making the collection2 to contain the collection1. Its like putting lots of caps on a box (collection1) and then putting this box inside another box (collection2).

The alternative is to either fill both of them at the same time or do a code to copy the contents like

for i = 1 to collection1.count
    collection2.add collection1.item(i)
next i

answered Dec 09 '16 at 17:05

Moacir

617
4
19

2

http://www.cpearson.com/excel/classes.aspx doesn't recommend auto-instancing objects, would you disagree? – CallumDA Dec 09 '16 at 17:10
The experience I have with collections is with my [Deck shuffling](http://codereview.stackexchange.com/questions/148546/deck-shuffle-and-drawing-cards) and it gave no problems for me yet. – Moacir Dec 09 '16 at 17:12
Also, the first part of the answer is not what you asked for, so feel free to ignore. – Moacir Dec 09 '16 at 17:25

Cloning a Collection

2 Answers2