VBA Collections

The Collection object stores a collection of items in an ordered sequence. Collections can store any type of data and objects except user-defined types. Collections start at index 1 unlike arrays which start at index 0 by default. The Option Base statement has no effect on collections as they always start at index 1. The Collection object is designed to be simple and easy to use with a minimalist set of methods that allow for adding, removing, retrieving, and counting items. Items in a collection cannot be reassigned once they are added. Objects in a collection can still be mutated but the object reference variable cannot be reassigned.

Collection Methods

Collections have four simple methods: Add, Remove, Count, and Item. Item is the default method of the Collection class which retrieves an item at a given index and does not need to be explicitly written. To clear a Collection just set the Collection to a New Collection.

Public Sub Example()

    'Create Collection
    Dim Coll As Collection
    Set Coll = New Collection

    'Add items
    Coll.Add 1
    Coll.Add 2
    Coll.Add 3

    'Count items
    Debug.Print Coll.Count

    'Remove item at index 1
    Coll.Remove 1

    'Retrieve item from Collection
    Debug.Print Coll(1) 
   'Debug.Print Coll.Item(1) 'Same as above

    'Cannot reassign an item in a Collection
    'Coll(1) = 4  'Causes an error. 

    'Clear Collection
    Set Coll = New Collection

End Sub

Keys and Values

Items can be given a Key and a Value and can be accessed either by their index or by their key. A Key needs to be a unique string value.

Public Sub Example()

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add 1, "Key1"
    Coll.Add 2, "Key2"
    Coll.Add 3, "Key3"

    Debug.Print Coll("Key2") 'Prints: 2

End Sub

Collections Vs Arrays

The Collection and the array both store a sequential list of data, however the array has a predefined size and data type whereas the Collection does not. A Collection should be used in place of an array when items need to be added or removed frequently from the list and items do not need to be reassigned once added.

Array

Predetermined size
Predetermined data type
Elements can be reassigned
Adding and removing items can be inefficient and requires more code

Collection

No predetermined size
Can store any type of data
Items cannot be reassigned
Items can be easily and efficiently added and removed

Jagged Collections

A Jagged Collection is a Collection of Collection Objects.

Public Sub Example()

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add New Collection
    Coll.Add New Collection
    Coll.Add New Collection

    Coll(1).Add "Test"

    Debug.Print Coll(1)(1) 'Prints: Test

End Sub

Looping Over Collections

While it is possible to use any type of loop to iterate over a Collection, it is most efficient to use a For Each loop. Accessing items via the Item method is inefficient when looping. When using a For Each loop, each item in the Collection is accessed via the control variable so Item does not need to be called for each iteration.

Note: In general, a For Each loop should always be used to iterate over a Collection object.

For Each Loop

When a Collection contains data types that are not objects, the control variable for a For Each loop when iterating over the items in a Collection must be type Variant.

Public Sub Example()

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add 1
    Coll.Add 2
    Coll.Add 3

    Dim i As Variant
    For Each i In Coll
        Debug.Print i
    Next i

End Sub

When a Collection contains objects, the control variable can be of type Variant, Object, or the specific type of object the Collection contains.

Public Sub Example()

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add New Collection
    Coll.Add New Collection
    Coll.Add New Collection

    Dim C As Collection
    For Each C In Coll
        Debug.Print C.Count
    Next C

End Sub

For Loop

To use a For loop to iterate over the items in a Collection, start the incrementor at 1 and increment to the Collection's Count property. Items of a collection can be accessed by their index using the Item method. The Item method is the default member of the Collection object and can be omitted by simply using parentheses after the Collection name.

Note: It is inefficient to iteratively access items in a Collection object via the Item method.

Public Sub Example()

    'INEFFICIENT

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add 1
    Coll.Add 2
    Coll.Add 3

    Dim i As Long
    For i = 1 To Coll.Count
        Debug.Print Coll(i) 'Same as Coll.Item(i)
    Next i

End Sub

Collections And Objects

Collections work very well for storing objects. Collections can be used to store instances of user-defined classes which makes storing and manipulating data in VBA much easier.

'Class Module: clsPerson

Option Explicit

Public FirstName  As String
Public Birthday   As Date
Public Hometown   As String

Public Sub PrintDetails()
    Debug.Print Me.FirstName, Me.Birthday, Me.Hometown
End Sub

'Standard Module: modExample

Option Explicit

Public Sub Example()

    Dim PersonCollection As Collection
    Set PersonCollection = New Collection

    Dim P As clsPerson

    'Add person
    Set P = New clsPerson
    P.FirstName = "Johnnie"
    P.Birthday = #1/1/1990#
    P.Hometown = "New York"
    PersonCollection.Add P

    'Add person
    Set P = New clsPerson
    P.FirstName = "Sally"
    P.Birthday = #1/1/1991#
    P.Hometown = "Chicago"
    PersonCollection.Add P

    'Add person
    Set P = New clsPerson
    P.FirstName = "Susie"
    P.Birthday = #1/1/1992#
    P.Hometown = "Los Angeles"
    PersonCollection.Add P

    'Print each person's details
    For Each P In PersonCollection
        P.PrintDetails
    Next P

    'Cleanup
    Set PersonCollection = Nothing

End Sub