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.
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"