Which is better: Array or Collection of Objects?

[Amateurhr]

I have long used one-dimensional Arrays to store objects (dictionaries, etc). Recently I've been using collections to store objects instead as the code seems a little bit shorter and I don't have to constantly redim and iterate by typing:

For i = lbound(arr) to ubound(arr).

I far prefer to say:

For each entry in coll
or
For i = 1 to coll.count

Any reasons why collections are NOT as good as arrays for storing objects? Or are they generally speaking far better when you're not doing computations such as Application.match(var, arr, 0), which require Arrays/Ranges?

 

[JoeMo]

Here are results of some tests intended to compare the relative speed of storing and reading a large group of items in Collections vs Arrays using the methods previously discussed. Further below I've posted the code used so others can repeat or build upon it.

JS411, nice test, thanks for sharing! Observed same trends when I ran your code on my computer. Any idea why reading the collection is so slow?

 

[taurean]

In my VB .NET training class I came across one more entity which seems to be Combination of Collection and Array. It seems to be middle person in terms of speed as well behavior i.e. fares better than Collection, but poorer than Arrays.

Comparative Results of Store_then_Read with 12,345 items:
Collection: 0.34 seconds
Array_Known_Size: 0 seconds
Array_Unknown_Size_Redim_Each : 0.02 seconds
Array_Unknown_Size_Redim_Chunks : 0 seconds
ArrayList_Unknown_Size : 0.23 seconds

Comparative Results of Store_then_Read with 123,456 items:
Collection: 59.88 seconds
Array_Known_Size: 0 seconds
Array_Unknown_Size_Redim_Each : 0.03 seconds
Array_Unknown_Size_Redim_Chunks : 0 seconds
ArrayList_Unknown_Size : 2.39 seconds

Code usage added to JS411's code was:

Sub Test_ArrayList_Unknown_Size(lNumberOfItems As Long, bRead As Boolean)
    Dim dblStart As Double:   dblStart = Timer
    Dim i As Long, lReadMe As Long
    Dim arlList As Object
    
    Set arlList = CreateObject("System.Collections.Arraylist")
    
    For i = 1 To lNumberOfItems
        arlList.Add (i * 2)
    Next i

    If bRead Then
        For i = 0 To arlList.Count - 1
            lReadMe = arlList(i)
        Next i
    End If
    
    Debug.Print "ArrayList_Unknown_Size : " _
      & Round((Timer - dblStart), 2) & " seconds"

End Sub

ArrayList stores items as objects and doesn't need ReDim and has a built-in sort method. I am still learning and I have no idea if I can implement it in Excel at this point of time. If anyone takes it up and explores some more then it may have some "hidden" talent.

 

[Kyle123]

The cost of adding to dictionaries and collections increases with the size of the collection since they re-index each time an element is added, the time this takes relate to the number of elements it contains. This is the same for array lists also, which typically perform on par with collections and better than dictionaries with a large number of elements iirc, but with the additional benefit of additional methods such as sorting, binary search and being able to reverse the order of their contents - they also have a useful method to return their elements as an array, we had a discussion about some of their features here Collections.ArrayList & Collections.Sortedlist if it helps anyone