Using Collections in VBA

A Collection is a type that contains a set of related objects - in memory. Specialized Collection types are used abundantly within Office applications: in Excel a Workbook has a Worksheets collection; in MS Access a Recordset is a collection of records in a table or query, the Forms collection contains all the open Forms. Custom collections are very useful because it is easy to add items to it and iterate over the items in the collection for whatever purpose you have in mind. In the following working with the VBA Collections class is explained.

Note: in some cases a better alternative is to use Dictionaries or arrays.

Declare and instantiate a collection

A collection object variable must be created as a "new" collection unless you are going to assign it to an existing collection. Typically, the code to create a collection looks like this:

Dim colPersons As Collection
Set colPersons = New Collection

Add items to the collection

The code below shows how you can add items to a collection. In our case we added custom class objects (Person) to the collection, but you can add anything, including standard types such as strings, or Office application built-in objects.

Dim prsPerson As Person
Set prsPerson = New Person
With prsPerson
    .Name = "John Doe"
    .Age = 37
End With
colPersons.Add Item:=prsPerson
Set prsPerson = New Person
With prsPerson
    .Name = "Pete Doe"
    .Age = 38
End With
colPersons.Add Item:=prsPerson

When you add multiple items, like above, you only have to declare the variable once - you can re-use it with each new object.

Dim prsPerson As Person

To create multiple objects you will have to instantiate new ones for each.

Set prsPerson = New Person

Add item to the collection include a key

Optionally you can include a key when adding an item.

Set prsPerson = New Person
With prsPerson
    .Name = "Pete Doe"
    .Age = 38
End With
colPersons.Add Item:=prsPerson, key:=prsPerson.Name

The key can be used to access an item in the collection instead of using the positional index. This key should be an unique string. You will have to make sure there is an unambiguous way to construct the key, for example in NL we use Postal Code + house number to uniquely identify an address. Adding a dupplicate error results in error 457: This key is already associated with an element of this collection.

Test if item exists in collection

Often you don't want to include the same item twice in the collection. To achieve this, you can loop through the collection to see if it is there. Alternatively, if a key was defined for the collection, you can try to get the item directly. If it does not exist, an error occurs, which you should handle.

                
Dim booItemExistsInCollection As Boolean
On Error Resume Next
colPersons.Item 3
booItemExistsInCollection = (Err.Number = 0)

Find an item in a collection

Find an item in a collection using its key

If you specified the key argument in the collections .Add, you can later use that key to obtain the item from the collection:

Set clsPerson = colPersons.Item("Pete Doe")

Find an item in a collection by looking at the individual properties

An alternative way to pick up the item you require is by looping through the collection and testing one or more properties. Add Exit For if you expect only one item. Alternatively you can process all the items that match the selection criteria.

Dim clsPerson As Person
For Each clsPerson In colPersons
    If clsPerson.Age = 38 Then
        Debug.Print clsPerson.Name
        Exit For
    End If
Next

Delete item from the collection (Remove)

To remove an item from the collection use the .Remove method. For the index argument either the number in the collection or - if available- the key may be used.

colPersons.Remove 3
colPersons.Remove "Pete Doe"