A dictionary 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 dictionary.
Unlike the collection object, each item in the dictionary object must be associated with a unique key identificator.
More information about array variables.
More information about the Collection object.

The dictionary object:

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

Sub Example_Dictionary()
' updated 2020-06-26 by OPE
Dim dict As Object, strKey As String, varKey As Variant, varItem As Variant, i As Long, lngValue As Long, objEmployee As clsEmployee
    ' dictionary object:
    ' can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays)
    ' each item must be associated with a unique key, usually this is a string or integer, can't be an array
    ' the associated key for each item can be retrieved, e.g. when looping through the dictionary items
    ' usually you would only add items of the same datatype to the dictionary
    ' it is possible to add items of mixed datatypes to the dictionary
    ' items can not be added before or after existing items in the dictionary
    ' looping through a dictionary is as fast as a collection when using For Each - Next with the key or item
    ' looping through a dictionary is a bit slower than a collection when using For Each - Next with the key and getting the item
    
    'Set dict = New Dictionary ' create a new dictionary object (using early binding, requires a reference to the Microsoft Scripting Runtime library)
    Set dict = CreateObject("Scripting.Dictionary") ' create a new dictionary object (using late binding, no extra references are necessary)
    dict.CompareMode = 1 ' dictionary must be empty, 0=vbBinaryCompare (default), 1=vbTextCompare, 2=vbDatabaseCompare, -1=vbUseCompareOption
    
    ' add items to a dictionary
    For i = 1 To 10
        strKey = CStr(i) ' the key for the item to add/edit
        dict.Add strKey, i * 100 ' add a new item with a key, an error will occur if the item already exists
    Next i
    
    ' add items to a dictionary
    For i = 1 To 10
        strKey = CStr(i) ' the key for the item to add/edit
        dict(strKey) = i * 100 ' add a new item with a key, if the item already exists it will be updated with the new value
    Next i
    
    ' add items to a dictionary
    For i = 1 To 10
        strKey = CStr(i) ' the key for the item to add/edit
        If dict.Exists(strKey) Then ' check if item already exist
            dict(strKey) = dict(strKey) + 1 ' edit the existing item value
        Else
            'dict.Add strKey, 1 ' add the new item and set the initial value, an error will occur if the item already exists
            dict(strKey) = 1 ' add a new item with a key, if the item already exists it will be updated with the new value
        End If
    Next i
    
    ' items in a dictionary must be added in the desired order, they can't be added before or after other existing items
    dict(111) = 111 ' add a new item with a key, if the item already exists it will be updated with the new value
    dict(222) = 222 ' add a new item with a key, if the item already exists it will be updated with the new value
    dict("333") = 333 ' add a new item with a key, if the item already exists it will be updated with the new value
    dict("444") = 444 ' add a new item with a key, if the item already exists it will be updated with the new value
    
    ' remove items from the dictionary
    dict.Remove "9" ' remove item from dictionary using a key identifier (can't be an "index" number)
    dict.Remove 222 ' remove item from dictionary using a key identifier (can't be an "index" number)
    dict.Remove "444" ' remove item from dictionary using a key identifier (can't be an "index" number)
    
    ' use items in the dictionary
    Debug.Print "Count of items in the dictionary: " & dict.Count
    Debug.Print "This is the value of the item for key ""1"": " & dict("1")
    Debug.Print "This is the value of the item for key ""111"": " & dict(111)
    Debug.Print "This is the value of the item for key ""333"": " & dict("333")
    
    ' update items in the dictionary
    dict("333") = dict("333") + 1 ' you can change the dictionary items
    ' if the dictionary item is an object you can change properties of the object (see example below)
    
    ' loop through the dictionary
    Debug.Print "Dictionary: " & dict.Count
    
    ' using a For Each - Next loop is the preferred and significantly fastest method for looping through all items in a dictionary if the dictionary has many items
    ' if you need the item value only:
    i = 0
    For Each varItem In dict.Items ' varItem must be a Variant
        i = i + 1
        Debug.Print i, varItem ' item "index", item value
    Next varItem
    
    ' if you need the key value only:
    i = 0
    For Each varKey In dict.Keys ' varKey must be a Variant
        i = i + 1
        Debug.Print i, varKey ' item "index", key value
    Next varKey
    
    ' if you need both the key and item value:
    i = 0
    For Each varKey In dict.Keys ' varKey must be a Variant
        i = i + 1
        Debug.Print i, varKey, dict(varKey) ' item "index", item key and item value
    Next varKey
    
    ' using a For Next loop can be extremely slow to loop through all items in a dictionary with many items (10000 or more)
    For i = LBound(dict.Keys) To UBound(dict.Keys)
        Debug.Print i, dict.Keys()(i), dict.Items()(i) ' item "index", item key and item value
    Next i
    
    ' write the dictionary content to a worksheet
    ' assumes that the items are normal datatypes, no objects or arrays
    With Sheet1
        On Error Resume Next
        .Range("A3:B" & .Rows.Count).Clear
        .Range("A3").Formula = "Keys"
        .Range("B3").Formula = "Items"
        .Range("A4").Resize(dict.Count, 1).Formula = Application.Transpose(dict.Keys) ' one-dimensional array, needs to be transposed
        .Range("B4").Resize(dict.Count, 1).Formula = Application.Transpose(dict.Items) ' one-dimensional array, needs to be transposed
        With .Range("A3").CurrentRegion
            .Style = "Borders"
            .Rows(1).Style = "Tableheader"
            .EntireColumn.AutoFit
        End With
        On Error GoTo 0
    End With
    
    ' remove all items in a dictionary (usually not necessary)
    dict.RemoveAll
    Set dict = Nothing ' or simply delete the dictionary
    ' or create a new dictionary
    'Set dict = New Dictionary ' create a new dictionary object (using early binding, requires a reference to the Microsoft Scripting Runtime library)
    Set dict = CreateObject("Scripting.Dictionary") ' create a new dictionary object using late binding
    dict.CompareMode = 1 ' dictionary must be empty, 0=vbBinaryCompare (default), 1=vbTextCompare, 2=vbDatabaseCompare, -1=vbUseCompareOption

    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
    Set dict(objEmployee.ID) = objEmployee ' add a new item or update an existing one, the use of a key is mandatory

    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
    Set dict(objEmployee.ID) = objEmployee ' add a new item or update an existing one, the use of a key is mandatory

    ' loop through the dictionary
    Debug.Print "Dictionary: " & dict.Count
    ' looping using the items is just as fast as looping through a collection
    For Each varItem In dict.Items ' varItem must be a variant
        varItem.DebugPrint ' assumes that the object has a DebugPrint method
        With varItem ' update object in dictionary
            .Salary = .Salary * 1.05
            .EndDate = DateSerial(Year(Date), 12, 31)
        End With
    Next varItem
    
    ' looping using the keys is a little bit slower than looping through a collection if you need to get the corresponding item too
    For Each varKey In dict.Keys ' varKey must be a variant
        dict(varKey).DebugPrint ' assumes that the object has a DebugPrint method
        With dict(varKey) ' update object in dictionary
            .Salary = .Salary * 1.05
            .EndDate = DateSerial(Year(Date), 12, 31)
        End With
    Next varKey
    
    Debug.Print "After update:"
    For Each varItem In dict.Items
        varItem.DebugPrint ' assumes that the object has a DebugPrint method
    Next varItem

    ' add some items of another datatype to the dictionary
    For i = 1 To 3
        dict.Add CStr(i), i * 100
    Next i

    ' loop through a dictionary with mixed data types
    Debug.Print "Dictionary with mixed datatypes: " & dict.Count
    ' looping using the items is just as fast as looping through a collection
    i = 0
    For Each varItem In dict.Items
        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
    
    ' looping using the keys is a little bit slower than looping through a collection if you need to get the corresponding item too
    i = 0
    For Each varKey In dict.Keys
        i = i + 1
        If TypeName(dict(varKey)) = "clsEmployee" Then
            dict(varKey).DebugPrint
        Else
            Debug.Print "Not an employee: " & i, dict(varKey), "TypeName: " & TypeName(dict(varKey))
        End If
    Next varKey
End Sub