Add Array to ArrayList in one go

Background:

I'm trying to populate an ArrayList object from any given array of values.

To populate these objects you can either use .Add method or the .AddRange method, however either way it seems that you need to iterate over your array to populate the ArrayList. And apparently, to make use of .AddRange we need to make use of the Queue object.

Code:

Example of the .Add method:

Sub Test1()

Dim ArrList As Object: Set ArrList = CreateObject("System.Collections.ArrayList")

With ArrList
    For Each ArrItem In Array("A", "B", "C")
        If Not .contains(ArrItem) Then .Add ArrItem
    Next
End With

End Sub

Example of the .AddRange method:

Sub Test2()

Dim ArrList As Object: Set ArrList = CreateObject("System.Collections.ArrayList")
Dim Q As Object: Set Q = CreateObject("System.Collections.Queue")

With Q
    For Each ArrItem In Array("A", "B", "C")
        If Not .contains(ArrItem) Then .Enqueue ArrItem
    Next
End With

ArrList.AddRange Q

End Sub

Question:

As you can see, I wonder what the purpose is of the .AddRange method if we can't assign an array directly. We still had to iterate and .Enqueue them.

So what can be done to assign an array at once? Obviously, the following will error (5) out:

ArrList.AddRange Array("A", "B", "C")

Btw, for early binding, add the mscorlib.dll reference.

Solution

The problem is that ArrayList is a .NET object and the AddRange() method expects a parameter of type ICollection. Now, a VBA array is not an ICollection object (i.e., it doesn't implement the .NET's ICollection interface), therefore, the call to the method fails.

The reason a Queue object works is that it does implement ICollection. Any other ICollection object would work too. For example, if you have another ArrayList, you can pass that to the AddRange method and it would work just fine. Try this, for example:

Sub Test()
    Dim ArrList1 As Object: Set ArrList1 = CreateObject("System.Collections.ArrayList")
    Dim ArrList2 As Object: Set ArrList2 = CreateObject("System.Collections.ArrayList")

    ArrList1.Add "A"
    ArrList1.Add "B"
    ArrList1.Add "C"

    ArrList2.AddRange ArrList1
End Sub

So, the bottom line is: I guess you're out of luck using a VBA array to pass to the ArrayList.AddRange() method.