Variable types in VBA : Should I use Arrays or Collections?

Considering the specificities of Arrays and Collections in VBA, it is clear that they are made for different purposes. Arrays are lower level objects that remain very adaptable and easy to modify. Collections are meant to be scarcely modified and provide instead nice lookup capabilities thanks to the text index.

Creating Arrays and Collections in VBA

Arrays and collections are the most common table and data management objects in VBA. They both provide a different approach to storing tabular data and they can't be used interchangeably!

Naïve array dimensions

Let us do 10,000,000 iterations in a loop, add an element to each object at every pass in the loop, and observe the computation time.

Sub test_Collection()
t = Time()
Dim Col1 As New Collection
For i = 0 To 10000000
   Col1.Add i
Next
Debug.Print (Time - t) * 24 * 3600
End Sub
 
Sub test_Array()
t = Time()
Dim Arr1() As Long
For i = 0 To 10000000
   ReDim Preserve Arr1(0 To i)
   Arr1(i) = i
Next
Debug.Print (Time - t) * 24 * 3600
End Sub

Collection: 3 seconds

 Array: 146 seconds

Outcome of the test: Collections are better than Arrays when we need to dimension an array dynamically

Optimized array initiation

The previous result is quite unsatisfactory as arrays are a rather low level object. Indeed this code is drastically under-optimized: the redim preserve operation takes up a lot of resource and time! Let’s twist the previous code so that we set up the array size from the beginning and don’t need resizing. Now the two pieces of code are not totally equivalent, although in this case we already know in advance the exact size of the beast. We observe that we tremendously gain in efficiency when we remove the redim preserve statement.

Sub test_Array()
t = Time()
Dim Arr1(0 To 10000000) As Long
For i = 0 To 10000000
    Arr1(i) = i
Next
Debug.Print (Time - t) * 24 * 3600
End Sub

 

Collection: 3 seconds

 Array: 1.5 seconds

Outcome of the test: Arrays are better than Collections as long as we are willing to give up some flexibility at initiation

Access the content of Arrays and Collections in VBA

Accessing the content of arrays and collections in VBA is not very complicated. The typical loop to go through an array would be either: for i=0 to ubound(TheArray) or for each Element in TheArray. Collections don’t support the ubound function but have access to similar property TheCollection.Count. Also note that the index in a collection starts at 1, not at 0 like a typical array.

Sub test_Collection()
Dim Col1 As New Collection
For i = 0 To 100000
    Col1.Add i
Next
t = Time()
'' For each loop
For Each colElement In Col1
    test = colElement * 2
Next
'' count property
For i = 0 To Col1.Count
    test = colElement * 2
Next
Debug.Print (Time - t) * 24 * 3600
End Sub
Sub test_Array()
Dim Arr1(0 To 100000) As Long
For i = 0 To 100000
    Arr1(i) = i
Next
t = Time()
'' For each loop
For Each ArrElement In Arr1
    test = ArrElement * 2
Next
'' ubound function
For i = 0 To UBound(Arr1)
    test = ArrElement * 2
Next
Debug.Print (Time - t) * 24 * 3600
End Sub

Timing these two procedures is not very relevant here and gives very similar results.

Modify of the content of Arrays and Collections in VBA

Well, let’s put it this way: collections are clearly not designed to be modified once they are created. First, you can’t modify the content of collection items after they are added. The only way to modify the collection is to remove the item and add another one at the same place. This can be done with the following code. For the sake of my pc’s survival, I lowered the loop down below 10000 iterations. This issue is virtually non-existent for arrays, which once again out-compete collections.

Sub test_Collection()
Dim Col1 As New Collection
For i = 0 To 100000
    Col1.Add i
Next
t = Time()
For i = 1 To Col1.Count - 1
    Col1.Remove (i)
    Col1.Add i + 10, before:=i
    '' Note that we won't replace the last item as it would cause an error here
Next
Debug.Print (Time - t) * 24 * 3600
End Sub
Sub test_Array()
Dim Arr1(0 To 100000) As Long
For i = 0 To 100000
    Arr1(i) = i
Next
t = Time()
For i = 0 To UBound(Arr1)
    Arr1(i) = i + 1
Next
Debug.Print (Time - t) * 24 * 3600
End Sub

 

Collection: 83 seconds

 Array: less than a second

Outcome of the test: Arrays are better than Collections when one need to modify their content later

How to use VBA Collection text index

A specificity of collections against arrays is the possibility to associate a name (or key) to some or all items in the collection. This is very efficient to look for a specific text in the collection and setup an associative table. This is also a way to make your code more self-explanatory, by giving names to the element of a list.

Create an associative list with VBA collections

If you want to setup a list of text values corresponding to a value or an array of values, chances are you should prefer collections against arrays as collections in VBA will make look-up of a specific text in the object more effective that arrays ever will.

Sub test_Collection_textkey()
    Dim Col1 As Collection
    Set Col1 = New Collection
    For i = 1 To 6
        Name = Cells(i, 1).Value
        Value = Cells(i, 2).Value
        Col1.Add Value, Key:=Name
    Next
    Debug.Print Col1("barack")
    '' return 20
    Debug.Print Col1("angela")
    '' returns 30
    Debug.Print Col1("louis")
    '' returns 5
End Sub

Create a named list with VBA collection

Collections allow naming each item and will call the value of each item by its name (or key). This makes the code much clearer compared to arrays that can only be designated by their index number. Let us graphically design the data we want to load into a collection. It describes three teams in the company with their members and some numerical amount (let’s say the value of their portfolio). We can translate this structure into collections with the code below.

Sub test_Collection_namedLists()
    Dim Teams As New Collection
    Dim Team As Collection
    Set Team = New Collection
    For i = 2 To 7
        Team.Add Cells(i, 2).Value, Key:=Cells(i, 1).Value
    Next
    Teams.Add Team, "team1"
    Set Team = New Collection
    For i = 2 To 9
        Team.Add Cells(i, 5).Value, Key:=Cells(i, 4).Value
    Next
    Teams.Add Team, "team2"
    Set Team = New Collection
    For i = 2 To 5
        Team.Add Cells(i, 8).Value, Key:=Cells(i, 7).Value
    Next
    Teams.Add Team, "team3"
    Debug.Print Teams("team3")("lucy")
    '' this prints 45
End Sub

 

 

 

Matthieu Liatard
Remember this!
Se souvenir de cela
Play!
Tester mes connaissances
Close

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte
Notez cet article :
1
2
3
4
5

Articles similaires