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:
myCol.Add 10 'Items: 10 myCol.Add 20 'Items: 10, 20 myCol.Add 30, Before:= 1 'Items: 30, 10, 20
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
When removing items in a loop do remember that the index of the remaining items in the VBA Collection will decrease by 1.
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
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
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
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.