A collection is an object where you can store items of any datatype, and unlike arrays you don't have to set the size of how many items you want to store in the collection.
More information about array variables.
More information about the Dictionary object.

The collection object:

• Can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays)
• Each item can optionally be associated with a unique key that must be a string
• The associated key for each item can not be retrieved, e.g. when looping through the collection items
• Usually you would only add items of the same datatype to the collection
• It is possible to add items of mixed datatypes to the collection
• Items can be added before or after existing items in the collection
• Looping through a collection is as fast as a dictionary when using For Each - Next

Sub Example_Collection()
' updated 2020-06-26 by OPE
Dim coll As Collection, i As Long, lngValue As Long, varItem As Variant, varArray() As Variant, objEmployee As clsEmployee
    ' collection object:
    ' can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays)
    ' each item can optionally be associated with a unique key that must be a string
    ' the associated key for each item can not be retrieved, e.g. when looping through the collection items
    ' usually you would only add items of the same datatype to the collection
    ' it is possible to add items of mixed datatypes to the collection
    ' items can be added before or after existing items in the collection
    ' looping through a collection is as fast as a dictionary when using For Each - Next
    
    Set coll = New Collection ' create a new collection object
    
    ' add items to a collection
    For i = 1 To 10
        coll.Add i * 100 ' add a new item without a key, items can only be retrieved by using their index numbers or a For Each - Next loop
    Next i

    ' add items to a collection
    For i = 1 To 10
        coll.Add i * 100, CStr(i) ' add a new item with a key, the key must be a string, an error will occur if the key already exists
    Next i
        
    ' add items to a collection
    For i = 1 To 10
        On Error Resume Next
        coll.Add i * 100, CStr(i) ' add a new item with a key, the key must be a string, ignore error if the key already exists
        On Error GoTo 0
    Next i
        
    ' add items to the collection before or after any existing items
    coll.Add 111, CStr(111), Before:=2 ' add a new item with a key, the key must be a string, an error will occur if item already exists
    coll.Add 222, CStr(222), Before:="5" ' add a new item with a key, the key must be a string, an error will occur if item already exists
    coll.Add 333, "333", After:=2 ' add a new item with a key, the key must be a string, an error will occur if item already exists
    coll.Add 444, "444", After:="5" ' add a new item with a key, the key must be a string, an error will occur if item already exists
    
    ' remove items from the collection
    coll.Remove 10 ' remove item from collection using an index number between 1 to coll.Count
    coll.Remove "9" ' remove item from collection using a key identifier, the key must be a string
    coll.Remove coll.Count ' remove the last item from collection using an index number between 1 to coll.Count
    
    ' use items in the collection
    Debug.Print "Count of items in the collection: " & coll.Count
    Debug.Print "This is the value of the first item: " & coll(1)
    Debug.Print "This is the value of the last item: " & coll(coll.Count)
    Debug.Print "This is the value of the item for key ""333"": " & coll("333")
    
    ' update items in the collection
    'coll(1) = coll(1) + 1 ' this will not work, you can't change the collection item
    ' if the collection item is an object you can change properties of the object (see example below)
    
    ' you can update an item by removing it and adding it again with an updated value
    lngValue = coll("itemkey") + 1 ' calculate the new value
    coll.Remove "itemkey" ' remove an existing item from the collection
    coll.Add lngValue, "itemkey", Before:=1 ' add the updated value to the collection, optionally at the desired position
    
    ' loop through the collection
    Debug.Print "Collection: " & coll.Count
    
    ' using a For Each - Next loop is the preferred and significantly fastest method for looping through all items in a collection if the collection has many items
    i = 0
    For Each varItem In coll ' varItem must be a Variant, Object or specific object type (e.g. Worksheet or clsEmployee)
        i = i + 1
        Debug.Print i, varItem ' the associated key for each item is not available
    Next varItem
    
    ' using a For Next loop can be extremely slow to loop through all items in a collection with many items (10000 or more)
    For i = 1 To coll.Count
        Debug.Print i, coll(i) ' the associated key for each item is not available
    Next i
    
    ' there is no easy way to write the collection content to a worksheet
    ' you would normally transfer the collection items to an array and write the array to the worksheet
    ' assumes that the items in the collection are normal datatypes, no objects or arrays
    If coll.Count > 0 Then
        ReDim varArray(1 To coll.Count, 1 To 1)
        i = 0
        For Each varItem In coll
            i = i + 1
            varArray(i, 1) = varItem ' populate the array with items from the collection
        Next varItem
        With Sheet1
            On Error Resume Next
            .Range("A3:A" & .Rows.Count).Clear
            .Range("A3").Formula = "Items"
            .Range("A4").Resize(UBound(varArray, 1), 1).Formula = varArray
            With .Range("A3").CurrentRegion
                .Style = "Borders"
                .Rows(1).Style = "Tableheader"
                .EntireColumn.AutoFit
            End With
            On Error GoTo 0
        End With
    End If
    
    ' remove all items in a collection (usually not necessary)
    ' this is slow and should be avoided:
    'Do While coll.Count > 0
    '    coll.Remove 1 ' remove item from collection
    'Loop
    
    ' better alternatives:
    Set coll = Nothing ' delete the collection
    Set coll = New Collection ' create a new collection
    
    ' adding custom objects to a collection, properties for objects in the collection can be edited unless they are read-only
    Set objEmployee = New clsEmployee ' create a new object and add values to it
    With objEmployee
        .ID = 1
        .Name = "Peder Aas"
        .Position = "Manager"
        .Salary = 100000
        .StartDate = DateSerial(2000, 1, 1)
    End With
    coll.Add objEmployee, CStr(objEmployee.ID) ' add a new item with a key, an error will occur if the key already exists

    Set objEmployee = New clsEmployee ' create a new object and add values to it
    With objEmployee
        .ID = 2
        .Name = "Lars Holm"
        .Position = "Supervisor"
        .Salary = 125000
        .StartDate = DateSerial(2001, 1, 1)
    End With
    coll.Add objEmployee, CStr(objEmployee.ID) ' add a new item with a key, an error will occur if the key already exists
    
    ' loop through the collection
    Debug.Print "Collection: " & coll.Count
    For Each objEmployee In coll
        objEmployee.DebugPrint ' assumes that the object has a DebugPrint method
        With objEmployee ' update object in collection
            .Salary = .Salary * 1.05
            .EndDate = DateSerial(Year(Date), 12, 31)
        End With
    Next objEmployee
    
    Debug.Print "After update:"
    For Each objEmployee In coll
        objEmployee.DebugPrint ' assumes that the object has a DebugPrint method
    Next objEmployee
    
    ' add some items of another datatype to the collection
    For i = 1 To 3
        coll.Add i * 100
    Next i
    
    ' loop through a collection with mixed data types
    Debug.Print "Collection with mixed datatypes: " & coll.Count
    i = 0
    For Each varItem In coll
        i = i + 1
        If TypeName(varItem) = "clsEmployee" Then
            varItem.DebugPrint
        Else
            Debug.Print "Not an employee: " & i, varItem, "TypeName: " & TypeName(varItem)
        End If
    Next varItem
End Sub

Function ExistsInColl(coll As Collection, strKey As String) As Boolean
' updated 2020-06-26 by OPE
' returns True if the collection contains an item associated with strKey (not case sensitive)
' example: If ExistsInColl(collEmployees, strEmployeeID) Then ' employee was found
    ExistsInColl = True
    On Error GoTo ErrorHandler
    TypeName coll(strKey)
    On Error GoTo 0
    Exit Function
    
ErrorHandler:
    ExistsInColl = False
    Resume Next
End Function