# Collections

# Getting the Item Count of a Collection

The number of items in a Collection can be obtained by calling its .Count function:

Syntax:

.Count()

Sample Usage:

Public Sub Example()
    Dim foo As New Collection
    
    With foo
        .Add "One"
        .Add "Two"
        .Add "Three"
        .Add "Four"
    End With
     
    Debug.Print foo.Count   'Prints 4
End Sub

# Determining if a Key or Item Exists in a Collection

# Keys

Unlike a Scripting.Dictionary (opens new window), a Collection does not have a method for determining if a given key exists or a way to retrieve keys that are present in the Collection. The only method to determine if a key is present is to use the error handler:

Public Function KeyExistsInCollection(ByVal key As String, _
                                      ByRef container As Collection) As Boolean
    With Err
        If container Is Nothing Then .Raise 91
        On Error Resume Next
        Dim temp As Variant
        temp = container.Item(key)
        On Error GoTo 0

        If .Number = 0 Then
            KeyExistsInCollection = True 
        ElseIf .Number <> 5 Then
            .Raise .Number
        End If
    End With
End Function

# Items

The only way to determine if an item is contained in a Collection is to iterate over the Collection until the item is located. Note that because a Collection can contain either primitives or objects, some extra handling is needed to avoid run-time errors during the comparisons:

Public Function ItemExistsInCollection(ByRef target As Variant, _
                                       ByRef container As Collection) As Boolean
    Dim candidate As Variant
    Dim found As Boolean
    
    For Each candidate In container
        Select Case True
            Case IsObject(candidate) And IsObject(target)
                found = candidate Is target
            Case IsObject(candidate), IsObject(target)
                found = False
            Case Else
                found = (candidate = target)
        End Select
        If found Then
            ItemExistsInCollection = True
            Exit Function
        End If
    Next
End Function

# Adding Items to a Collection

Items are added to a Collection by calling its .Add method:

Syntax:

.Add(item, [key], [before, after])

Parameter Description
item The item to store in the Collection. This can be essentially any value that a variable can be assigned to, including primitive types, arrays, objects, and Nothing.
key Optional. A String that serves as a unique identifier for retrieving items from the Collection. If the specified key already exists in the Collection, it will result in a Run-time error 457: "This key is already associated with an element of this collection".
before Optional. An existing key (String value) or index (numeric value) to insert the item before in the Collection. If a value is given, the after parameter must be empty or a Run-time error 5: "Invalid procedure call or argument" will result. If a String key is passed that does not exist in the Collection, a Run-time error 5: "Invalid procedure call or argument" will result. If a numeric index is passed that is does not exist in the Collection, a Run-time error 9: "Subscript out of range" will result.
after Optional. An existing key (String value) or index (numeric value) to insert the item after in the Collection. If a value is given, the before parameter must be empty. Errors raised are identical to the before parameter.

Notes:

  • Keys are ****not**** case-sensitive. `.Add "Bar", "Foo"` and `.Add "Baz", "foo"` will result in a key collision.
  • If neither of the optional **before** or **after** parameters are given, the item will be added after the last item in the `Collection`.
  • Insertions made by specifying a **before** or **after** parameter will alter the numeric indexes of existing members to match thier new position. This means that care should be taken when making insertions in loops using numeric indexes.
  • Sample Usage:

    Public Sub Example()
        Dim foo As New Collection
        
        With foo
            .Add "One"            'No key. This item can only be retrieved by index.
            .Add "Two", "Second"  'Key given. Can be retrieved by key or index.
            .Add "Three", , 1     'Inserted at the start of the collection.
            .Add "Four", , , 1    'Inserted at index 2.
        End With
        
        Dim member As Variant
        For Each member In foo
            Debug.Print member    'Prints "Three, Four, One, Two"
        Next
    End Sub
    
    

    # Removing Items From a Collection

    Items are removed from a Collection by calling its .Remove method:

    Syntax:

    .Remove(index)
    
    
    Parameter Description
    index The item to remove from the Collection. If the value passed is a numeric type or Variant with a numeric sub-type, it will be interpreted as a numeric index. If the value passed is a String or Variant containing a string, it will be interpreted as the a key. If a String key is passed that does not exist in the Collection, a Run-time error 5: "Invalid procedure call or argument" will result. If a numeric index is passed that is does not exist in the Collection, a Run-time error 9: "Subscript out of range" will result.

    Notes:

    • Removing an item from a Collection will change the numeric indexes of all the items after it in the Collection. For loops that use numeric indexes and remove items should run backwards (Step -1) to prevent subscript exceptions and skipped items.
    • Items should generally not be removed from a Collection from inside of a For Each loop as it can give unpredictable results.

    Sample Usage:

    Public Sub Example()
        Dim foo As New Collection
        
        With foo
            .Add "One"
            .Add "Two", "Second"
            .Add "Three"
            .Add "Four"
        End With
         
        foo.Remove 1            'Removes the first item.
        foo.Remove "Second"     'Removes the item with key "Second".
        foo.Remove foo.Count    'Removes the last item.
        
        Dim member As Variant
        For Each member In foo
            Debug.Print member  'Prints "Three"
        Next
    End Sub
    
    

    # Retrieving Items From a Collection

    Items can be retrieved from a Collection by calling the .Item function.

    Syntax:

    .Item(index)
    
    
    Parameter Description
    index The item to retrieve from the Collection. If the value passed is a numeric type or Variant with a numeric sub-type, it will be interpreted as a numeric index. If the value passed is a String or Variant containing a string, it will be interpreted as the a key. If a String key is passed that does not exist in the Collection, a Run-time error 5: "Invalid procedure call or argument" will result. If a numeric index is passed that is does not exist in the Collection, a Run-time error 9: "Subscript out of range" will result.

    Notes:

    • .Item is the default member of Collection. This allows flexibility in syntax as demonstrated in the sample usage below.
    • Numeric indexes are 1-based.
    • Keys are not case-sensitive. .Item("Foo") and .Item("foo") refer to the same key.
    • The index parameter is not implicitly cast to a number from a String or visa-versa. It is entirely possible that .Item(1) and .Item("1") refer to different items of the Collection.

    Sample Usage (Indexes):

    Public Sub Example()
        Dim foo As New Collection
        
        With foo
            .Add "One"
            .Add "Two"
            .Add "Three"
            .Add "Four"
        End With
         
        Dim index As Long
        For index = 1 To foo.Count
            Debug.Print foo.Item(index) 'Prints One, Two, Three, Four
        Next
    End Sub
    
    

    Sample Usage (Keys):

    Public Sub Example()
        Dim keys() As String
        keys = Split("Foo,Bar,Baz", ",")
        Dim values() As String
        values = Split("One,Two,Three", ",")
            
        Dim foo As New Collection
        Dim index As Long
        For index = LBound(values) To UBound(values)
            foo.Add values(index), keys(index)
        Next
         
        Debug.Print foo.Item("Bar") 'Prints "Two"
    End Sub
    
    

    Sample Usage (Alternate Syntax):

    Public Sub Example()
        Dim foo As New Collection
        
        With foo
            .Add "One", "Foo"
            .Add "Two", "Bar"
            .Add "Three", "Baz"
        End With
        
        'All lines below print "Two"
        Debug.Print foo.Item("Bar")     'Explicit call syntax.
        Debug.Print foo("Bar")          'Default member call syntax.
        Debug.Print foo!Bar             'Bang syntax.
    End Sub
    
    

    Note that bang (!) syntax is allowed because .Item is the default member and can take a single String argument. The utility of this syntax is questionable.

    # Clearing All Items From a Collection

    The easiest way to clear all of the items from a Collection is to simply replace it with a new Collection and let the old one go out of scope:

    Public Sub Example()
        Dim foo As New Collection
        
        With foo
            .Add "One"
            .Add "Two"
            .Add "Three"
        End With
         
        Debug.Print foo.Count   'Prints 3
        Set foo = New Collection
        Debug.Print foo.Count   'Prints 0
    End Sub
    
    

    However, if there are multiple references to the Collection held, this method will only give you an empty Collection for the variable that is assigned.

    Public Sub Example()
        Dim foo As New Collection
        Dim bar As Collection
        
        With foo
            .Add "One"
            .Add "Two"
            .Add "Three"
        End With
         
        Set bar = foo
        Set foo = New Collection
        
        Debug.Print foo.Count   'Prints 0
        Debug.Print bar.Count   'Prints 3
    End Sub
    
    

    In this case, the easiest way to clear the contents is by looping through the number of items in the Collection and repeatedly remove the lowest item:

    Public Sub ClearCollection(ByRef container As Collection)
        Dim index As Long
        For index = 1 To container.Count
            container.Remove 1
        Next
    End Sub
    
    

    # Remarks

    A Collection is a container object that is included in the VBA runtime. No additional references are required in order to use it. A Collection can be used to store items of any data type and allows retrieval by either the ordinal index of the item or by using an optional unique key.

    # Feature Comparison with Arrays and Dictionaries

    Collection Array (opens new window) Dictionary (opens new window)
    Can be resized Yes Sometimes1 Yes
    Items are ordered Yes Yes Yes2
    Items are strongly typed No Yes No
    Items can be retrieved by ordinal Yes Yes No
    New items can be inserted at ordinal Yes No No
    How to determine if an item exists Iterate all items Iterate all items Iterate all items
    Items can be retrieved by key Yes No Yes
    Keys are case-sensitive No N/A Optional3
    How to determine if a key exists Error handler N/A .Exists function
    Remove all items Iterate and .Remove Erase, ReDim .RemoveAll function

    1 Only dynamic arrays can be resized, and only the last dimension of multi-dimensional arrays.

    2 The underlying .Keys and .Items are ordered.

    3 Determined by the .CompareMode property.