Collections in Excel VBA

You have already encountered some predefined collections in Excel VBA, such as Worksheets and Workbooks. The elements of these collections are fixed by Excel. However, you can also create your own custom collections. Collections allow you to group together elements of the same type or different types that share a thematic relationship.

Compared to arrays, collections provide easier ways to add or remove elements dynamically. One limitation is that all elements in a collection are of the Variant data type.

The Collection object type is used to create collections. Collections provide the following key properties and methods:

• Add() method: Adds an element to the collection.
• Count property: Returns the number of elements in the collection.
• Remove() method: Removes an element from the collection.

Elements in a collection can be accessed either via a For Each loop or by their numeric index.

The following example demonstrates how to create a collection of numbers and work with it:

Sub CollectionExample()
    Dim MyCollection As New Collection   
    MyCollection.Add 5.2
    MyCollection.Add 9.6
    MyCollection.Add -3.8
    MyCollection.Add 12.2   
    OutputList MyCollection   
    If MyCollection.Count >= 2 Then MyCollection.Remove 2   
    OutputList MyCollection   
    Set MyCollection = Nothing
End Sub

Sub OutputList(X As Collection)
    Dim i As Integer
    Dim Output As String
    Dim Element As Variant   
    Output = "Index: "
    For i = 1 To X.Count
        Output = Output & i & ": " & X(i) & " "
    Next i
    MsgBox Output   
    Output = "For Each: "
    For Each Element In X
        Output = Output & Element & " "
    Next Element
    MsgBox Output
End Sub

Explanation of the CollectionExample procedure:

• Dim MyCollection As New Collection creates a new collection object and establishes a reference to it.
• The Add() method is used repeatedly to add several numbers to the collection.
• The collection is passed to the procedure OutputList for display.
• The second element of the collection is removed using the Remove() method.
• The collection is displayed again to reflect the removal.

Explanation of the OutputList procedure:

• The parameter X is a reference to a collection object.
• The Count property is used to control the For loop, which accesses elements by their index.
• Alternatively, the elements are output using a For Each loop, which requires a variable of type Variant to iterate over the collection’s elements.