6

What's the most efficient way to merge two collections together in VBA, so that I have a 3rd Collection containing all of the items from the first two?

Greedo
  • 4,967
  • 2
  • 30
  • 78
  • Why looking for the most efficient way to merge collections when arrays are already more efficient? – Florent B. Dec 11 '17 at 17:03
  • @FlorentB. That's just how my data's stored, I have to work with what I've got! But in the application that prompted me to ask this, I'm using collections because I'm constantly adding/removing, which I think collections *are* more efficient at that arrays? Or if what you're saying is that writing the collections to a variant array then writing back to collection would be an efficient way of merging, then by all means, post and we can benchmark! – Greedo Dec 11 '17 at 17:26
  • arrays are more efficient than collection even for adding/removing. – Florent B. Dec 11 '17 at 17:35
  • 1
    @FlorentB. except when they're misused, and resized at every single iteration ;-) – Mathieu Guindon Dec 11 '17 at 17:35
  • Have a look [here](http://mielk.pl/en/download/code/collection/joinCollections.php). – Daniel Dušek Dec 20 '17 at 13:11

3 Answers3

6

Read this article, which compares For vs For Each loops when iterating arrays, vs. when iterating object collections.

Bottom line:

  • Use For loops to iterate arrays.
  • Use For Each loops to iterate collections.

So, iterate your collections with For Each loops, and in these loops Add the items to your 3rd collection.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Fantastic, this is why you should post even if you think you know the answer! I suppose this is because `For ... Each` takes advantage of the un-ordered nature of a collection whereas `For i = 1 to Count` requires multiple lookups to a map? – Greedo Dec 11 '17 at 18:11
3

You can merge collections by looping through their items and adding them to either a new collection, or one of the original ones. This does not preserve the keys in collection 2 though - you can't use collections if you want to do that

If you want to add the items in Collection col2 to another Collection col1 and return a new merged Collection:

Function mergedCollection(ByVal col1 As Collection, ByVal col2 As Collection) As Collection
    'Add items from col2 to col1 and return the result
    'ByVal means we are only looking at copies of the collections (the values within them)
    'The function returns a NEW merged collection
    Dim i As Long 
    For i = 1 To col2.Count
        col1.Add col2.item(i)
    Next i
    Set mergedCollection = col1 'set return value
End Function

And to add col2 to col1 directly (col1 is altered)

Sub mergeCollectionsInPlace(ByRef col1 As Collection, ByVal col2 As Collection)
    'Routine to add items from col 2 directly to col1
    'ByRef col1 means we are adding directly to the original col1
    'Routine doesn't return a new collection, it just appends col2 to col1
    Dim i As Long
    For i = 1 To col2.Count 'loop through each item in collection 2
        col1.Add col2.item(i) 'add the item at index i to collection 1
    Next i
End Sub

Use each like

Dim col3 As Collection
Set col3 = mergedCollection(col1, col2) 'sets col3 to be the merge of the two collections
mergeCollectionsInPlace(col1, col2)     'sets col1 to be the merge of the two collections
Greedo
  • 4,967
  • 2
  • 30
  • 78
1

This function will merge any number of collections, using the type of loop specified in the accepted answer:

Function MergeCollections(ParamArray collections())
    Set MergeCollections = New Collection
    For i = LBound(collections) To UBound(collections)
        For Each c In collections(i)
            MergeCollections.Add c
        Next
    Next
End Function

Each collection goes in as a separate argument.

Note this doesn't preserve any keys, if any of your collections have keys, since that is not as simple.1

Mark E.
  • 373
  • 2
  • 10