Working with Collections

By using Collections within VBA you can store multiple objects, similar to an array.  The key advantage with Collections is that the objects can be added, removed and used by referring to the objects ‘Key’, rather then having to loop through an array.

In this blog post I am going to use the example code provided in my previous blog post Working with Class Modules.

Within the class module cls_Employee we have the following properties, Name, Department, Age and Salary.  In this blog post I will run though how to create multiple employee class objects and add them to a collection.  We will then loop through the collection and retrieve and remove an employee.

To store multiple instances of a class, in this example a group of employees, create several instances of the class and load them to a collection as follows.

    Dim clsEmp As cls_Employee
    Dim colEmp As Collection

    Set colEmp = New Collection

    ' Loop through your existing list
    For Each Item In SomeList

        ' Create a new instance of the employee class
        clsEmp = New cls_Employee

        ' Set the properties of the class
        ' As per the example in the previous blog post

        'Load the class to the collection, _
         using the employee's name as the 'key'
        colEmp.Add clsEmp, clsEmp.Name

    Next Item

Now, you can use a simple For Each loop to loop through the collection and iterate through the collection and access each instance of cls_Employee sequentially:

    Dim clsEmp As cls_Employee
    ' Loop through a collection to retrieve each item
    For Each clsEmp In colEmp
        Debug.Print clsEmp.Name
        Debug.Print clsEmp.Dept
        Debug.Print clsEmp.Age
        Debug.Print clsEmp.Salary
    Next clsEmp

To retrieve or remove a specific employee you can use the object’s ‘key’ to refer to it.

    ' Use the key to retrieve a specific employee
    Dim clsEmp As cls_Employee
    clsEmp = colEmp("Fred Smith")

    ' Use the key to remove a specific employee
    Dim clsEmp As cls_Employee
    colEmp.Remove "Fred Smith"

Something worth noting is that the key needs to be a text value.  Other values can be used but may require the Str() function wrapped around them.