The VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. As Microsoft documentation explains Collections are objects containing an orders set of items. They are more flexible than VBA Arrays as they are not limited in their size at any point in time and don’t require manual re-sizing. Collections are also useful when you don’t want to leverage there more complex (but quite similar) Data Structures like the VBA ArrayList or even a VBA Dictionary.

Adding items to a VBA Collection

Let’s start by creating a New VBA Collection and adding some items to it.

Dim myCol As Collection

'Create New Collection
Set myCol = New Collection
    
'Add items to Collection
myCol.Add 10 'Items: 10
myCol.Add 20 'Items: 10, 20
myCol.Add 30 'Items: 10, 20, 30

Notice that the Collection is not typed in any way as we didn’t have to declare what types of objects it stores. This is because a Collection stores object of type Variant.

By default the Add procedure will push items to the end of the VBA Collection. However, you can also choose to insert items before or after any index in the Collection like so:
Before:

myCol.Add 10 'Items: 10
myCol.Add 20 'Items: 10, 20

myCol.Add 30, Before:= 1 'Items: 30, 10, 20

After:

myCol.Add 10 'Items: 10
myCol.Add 20 'Items: 10, 20

myCol.Add 30, After:= 1 'Items: 10, 30, 20

myCol.Add 10, "Key10"
Debug.Print myCol("Key10") 'Returns 10

Removing items from a Collection

Removing items from a VBA Collection is equally easy. However, items are removed by specifying their index.

'Remove selected items from Collection
'Before Items: 10, 20, 30
myCol.Remove (2) 'Items: 10, 30
myCol.Remove (2) 'Items: 10

Clearing a Collection

To Clear all items (remove them) from a VBA Collection use the Clear function.

Dim myCol as Collection
Set myCol = New Collection
'...
myCol.Clear

Clearing all items from a Collection is similar to recreating a VBA Collection:

myCol.Clear
'...is similar to...
Set myCol = New Collection 

Counting items in a Collection

Similarly as with other VBA Data Structures the VBA Collection facilitates the Count function which returns the amount of items currently stored.

Dim myCol As Collection: Set myCol = New Collection

'Add 3 items to Collection
myCol.Add 10 'Items: 10
myCol.Add 20 'Items: 10, 20
myCol.Add 30 'Items: 10, 20, 30

Debug.Print myCol.Count '3 

Getting items in a Collection

To get a specific item in your VBA Collection you need to either specify it’s index or optionally it’s key (if it was defined).

Dim myCol As Collection
Set myCol = New Collection

'Add items to Collection
myCol.Add 10
myCol.Add 20, Key:="MyAge"
myCol.Add 30

'Get item at index 1
Debug.Print myCol(1) '20

'Get item with the specified key
Debug.Print myCol("MyAge") '20

Traversing a VBA Collection

As with VBA Arrays you can similarly traverse a VBA Collection using loops such as For, While etc. by indexing it’s items, or by traversing its items using the For Each loop (preferred).

Dim myCol As Collection: Set myCol = New Collection
myCol.Add 10: myCol.Add 20: myCol.Add 30

'Print items in Collection
Dim it As Variant
For Each it In myCol
  Debug.Print it '10, 20, 30
Next it

'Print items in Collection
Dim i As Long
For i = 1 To myCol.Count
  Debug.Print myCol(i) '10, 20, 30
Next i

Check if Collection contains item

Unfortunately, the VBA Collection object does not facilitate a Contains function. We can however easily write a simple Function that will extend this functionality. Feel free to use the Function below:

Function CollectionContains(myCol As Collection, checkVal As Variant) As Boolean
    On Error Resume Next
    CollectionContains = False
    Dim it As Variant
    For Each it In myCol
        If it = checkVal Then
            CollectionContains = True
            Exit Function
        End If
    Next
End Function

Usage example:

Dim myCol as Collection: Set myCol = New Collection
myColl.Add 10: myColl.Add 20: myColl.Add 30

Debug.Print CollectionContains(myCol, 20) 'True
Debug.Print CollectionContains(myCol, 40) 'False

Convert Collection to VBA Array

In some case you may want to convert your VBA Collection to a VBA Array e.g. if you would want to paste items from your Collection to a VBA Range. The Code below does that exactly that:

Function CollectionToArray(col As Collection) As Variant()
    Dim arr() As Variant, index As Long, it As Variant
    ReDim arr(col.Count - 1) As Variant
    For Each it In col
        arr(index) = it
        index = index + 1
    Next it
    CollectionToArray = arr
End Function

Below a test of how it works:

Sub TestCollectionToArray()
    Dim myCol As Collection, arr() as Variant
    Set myCol = New Collection
    myCol.Add 1
    myCol.Add 2
    myCol.Add 3
    arr = CollectionToArray(myCol)
End Sub 

Summary

The VBA Collection is an extremely useful data type in VBA. It is more versatile than the VBA Array allowing you to add and remove items more freely. The Key-Value store works also similarly as for the VBA Dictionary making it a useful alternative.

As an exercise – next time you will consider using a VBA Array consider replacing it with the Collection object. Hopefully it will guest in your VBA Code more often.