VBA: append one collection on to another like ".AddRange" in VB.NET

Question

Let's say I have a master collection that I want to add other collections to. I have some optional functions that return a collection and I want to append that to my existing collection. Is there a command to do that like the ".AddRange" in VB.NET ?

VBA CODE EXAMPLE:

Public Sub main()
    Dim masterCollection As Collection

    Do While addingThings
        If addManyThings Then
            masterCollection.AddRange functionReturnsCollection
        Else
            masterCollection.Add aThing
        End If
    Loop
End Sub

Private Function functionReturnsCollection() As Collection

End Function

By append do you simply mean add another item to the collection? In which case the method is .Add — QHarr, Jun 14 '18 at 20:27
@QHarr the `.Add` method would make the entire collection as one item in the master collection, instead of adding the children to the master, as desired. I don't want a collection of collections. I want to add a whole range of items to the master collection from the function that itself returns a collection of items. — HackSlash, Jun 14 '18 at 20:28

QHarr · Answer 1 · 2018-06-14T20:43:38.127

1

Don't think so with collections. You can access the method you mention via Systems Collection (.Net framework) and insertRange with ArrayList for example:

Public Sub test()
    Dim a As Object, b As Object, i As Long
    Set a = CreateObject("System.Collections.Queue")
    a.Enqueue "D"
    a.Enqueue "E"

   Set b = CreateObject("System.Collections.ArrayList")
    With b
        .Add "A"
        .Add "B"
        .Add "C"
        .InsertRange 1, a
    End With

    For i = 0 To b.count - 1
        MsgBox b(i)
    Next i
End Sub

Perhaps there is a Class you could design to mimic this externally.

edited Jun 14 '18 at 20:43

answered Jun 14 '18 at 20:30

QHarr

83,427
12
54
101

Are you saying I can import an object from the .NET framework in to VBA? – HackSlash Jun 14 '18 at 21:14
I am saying you can use some of the classes within System.Collections as shown above. – QHarr Jun 14 '18 at 21:15
Did you progress this? – QHarr Jun 15 '18 at 15:51
I need to refactor with an arraylist to test, but it's turtles all the way down so that refactor is across 3 modules. – HackSlash Jun 15 '18 at 15:59
Be interesting to see what you come up with :-) – QHarr Jun 15 '18 at 16:00
This is amazing: https://stackoverflow.com/questions/19148762/c-sharp-like-listt-in-vba – HackSlash Jun 15 '18 at 23:04
OMG...yes it is. Not surprising as it is mr guindon! I have upvoted both and will be having a good read of that later. Thanks so much for sharing. Though, I guess you can still use the .Net versions as above. Have a look through those as well. There are [Lists - e.g. List of t](https://msdn.microsoft.com/en-us/library/6sh2ey19(v=vs.110).aspx), SortedlLists, ArrayLists. HashTables.... – QHarr Jun 16 '18 at 05:19

Niels Perfors · Accepted Answer · 2021-02-09T08:44:39.670

Public Function AppendCollections( _
    Optional ByVal FirstCollection As Collection, _
    Optional ByVal SecondCollection As Collection, _
    Optional ByVal ThirdCollection As Collection _
        ) As Collection
    
    Dim ReturnCollection As Collection, i As Variant
    
    ' Start with an empty Collection
    Set ReturnCollection = New Collection
    
    ' Append the first Collection
    If Not FirstCollection Is Nothing Then
        For Each i In FirstCollection
            ReturnCollection.Add i
        Next i
    End If
    
    ' Append the second Collection
    If Not SecondCollection Is Nothing Then
        For Each i In SecondCollection
            ReturnCollection.Add i
        Next i
    End If
    
    'Append the third Collection
    If Not ThirdCollection Is Nothing Then
        For Each i In ThirdCollection
            ReturnCollection.Add i
        Next i
    End If
    
    ' Return the new Collection
    Set AppendCollections = ReturnCollection
    
End Function

VBA: append one collection on to another like ".AddRange" in VB.NET

2 Answers2