VBA Dictionaries

The Dictionary object stores data in Key-Item pairs. A Dictionary Key is normally a string or integer but can be any value type. Dictionaries cannot contain duplicate keys. A Dictionary Item can be any value type, object, or array. Searching for data in a Dictionary is generally more efficient than searching for data in an array provided that the Dictionary is used correctly. Dictionary keys are searched in constant time, O(1) in Big O notation, whereas array elements are searched in linear time, O(n) in Big O notation. Dictionaries can be used in a variety of ways including creating lookup tables, removing duplicates from a list, aggregating data, and structuring hierarchical data.

Accessing the Dictionary Class

Dictionaries are not built into VBA. The Dictionary Class is a member of the Microsoft Scripting Runtime Library. To access the Microsoft Scripting Runtime Library, set a reference in the Visual Basic Editor by navigating to Tools → References and checking Microsoft Scripting Runtime. The Dictionary object can also be instantiated using late-binding with the CreateObject function which removes the need to set a reference. Note that intellisense does not work when using late-binding.

Public Sub Example()

    'Early-binding with reference set to Microsoft Scripting Runtime Library

    Dim Dict As Scripting.Dictionary 
    Set Dict = New Scripting.Dictionary

End Sub

Public Sub Example()

    'Late-binding. No need for library reference.

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

End Sub

Dictionary Class Members

The Dictionary class contains properties and methods for working with the Dictionary.

Methods
Method	Description
Add	Adds a new key/item pair to a Dictionary object.
Exists	Returns a Boolean value that indicates whether a specified key exists in the Dictionary object.
Items	Returns an array of all the items in a Dictionary object.
Keys	Returns an array of all the keys in a Dictionary object.
Remove	Removes one specified key/item pair from the Dictionary object.
RemoveAll	Removes all the key/item pairs in the Dictionary object.

Properties
Property	Description
CompareMode	Sets or returns the comparison mode for comparing string keys in a Dictionary object.
Count	Returns the number of key/item pairs in a Dictionary object.
Item	Sets or returns the value of an item in a Dictionary object.
Key	Sets a new key value for an existing key value in a Dictionary object.

Lookup Table

Dictionaries can be used in the most literal sense to lookup data using a unique identifier just like a dictionary is used to look up the definition of a word.

Public Sub Example()

    Dim ChessDict As Object
    Set ChessDict = CreateObject("Scripting.Dictionary")

    ChessDict.Add "Zugzwang", "A position where a player would prefer to pass"
    ChessDict.Add "Zwischenzug", "An in-between move"
    ChessDict.Add "Fingerfehler", "Finger mistake"
    ChessDict.Add "Gambit", "Sacrifice for an advantage"
    ChessDict.Add "En passant", "Rule allowing pawn to be captured ""in passing""."

    Debug.Print ChessDict("Zugzwang")

End Sub

Remove Duplicates

Dictionaries can be used to retrieve a list of the unique elements from a list. Iterate over a list and check if each element exists in the Dictionary, and if not, then add it. Dictionaries do not allow duplicate keys so adding a duplicate key will simply overwrite the previously existing key.

Public Sub Example()

    'Array containing duplicates
    Dim Arr(0 To 9) As Long
    Arr(0) = 1
    Arr(1) = 2
    Arr(2) = 3
    Arr(3) = 3
    Arr(4) = 3
    Arr(5) = 4
    Arr(6) = 5
    Arr(7) = 6
    Arr(8) = 7
    Arr(9) = 7

    'Use Dictionary to get unique elements
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        If Not Dict.Exists(Arr(i)) Then
            Dict.Add Arr(i), Null
        End If
    Next i

    'Print Dictionary keys
    Dim k As Variant
    For Each k In Dict.Keys()
        Debug.Print k
    Next k

End Sub

Aggregate Data

Dictionaries can be used to efficiently aggregate data. This is similar to how a PivotTable works in Excel.

Public Sub Example1()

    '5 rows of data with 2 columns
    Dim Arr(0 To 4, 0 To 1) As Variant

    Arr(0, 0) = "A"
    Arr(0, 1) = 100

    Arr(1, 0) = "B"
    Arr(1, 1) = 200

    Arr(2, 0) = "A"
    Arr(2, 1) = 300

    Arr(3, 0) = "C"
    Arr(3, 1) = 400

    Arr(4, 0) = "B"
    Arr(4, 1) = 500

    'Use Dictionary to aggregate
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

    Dim i As Long
    For i = LBound(Arr, 1) To UBound(Arr, 1)

        Dim CurrentKey As String
        CurrentKey = Arr(i, 0)

        If Dict.Exists(CurrentKey) Then
            Dict(CurrentKey) = Dict(CurrentKey) + Arr(i, 1)
        Else
            Dict(CurrentKey) = Arr(i, 1)
        End If

    Next i

    'Print aggregates
    Dim k As Variant
    For Each k In Dict.Keys()
        Debug.Print k, Dict(k)
    Next k

End Sub

Nested Dictionaries

Dictionaries can be nested inside dictionaries to create a hierarchical data structure.

Public Sub Example()

    '12 rows 4 columns
    'Columns: Company, Department, Role, Employee

    Dim Arr(0 To 11, 0 To 3) As Variant

    Arr(0, 0) = "ABC Company"
    Arr(0, 1) = "Marketing"
    Arr(0, 2) = "Associate"
    Arr(0, 3) = "Marky"

    Arr(1, 0) = "ABC Company"
    Arr(1, 1) = "Marketing"
    Arr(1, 2) = "Associate"
    Arr(1, 3) = "Ricky"

    Arr(2, 0) = "ABC Company"
    Arr(2, 1) = "Finance"
    Arr(2, 2) = "Analyst"
    Arr(2, 3) = "Danny"

    Arr(3, 0) = "Family Owned LLC"
    Arr(3, 1) = "Operations"
    Arr(3, 2) = "Associate"
    Arr(3, 3) = "Terry"

    Arr(4, 0) = "Family Owned LLC"
    Arr(4, 1) = "Operations"
    Arr(4, 2) = "Manager"
    Arr(4, 3) = "Mikey"

    Arr(5, 0) = "Family Owned LLC"
    Arr(5, 1) = "Sales"
    Arr(5, 2) = "Manager"
    Arr(5, 3) = "Davey"

    Arr(6, 0) = "Big Bank Inc"
    Arr(6, 1) = "Investments"
    Arr(6, 2) = "Sales"
    Arr(6, 3) = "Timmy"

    Arr(7, 0) = "Big Bank Inc"
    Arr(7, 1) = "Investments"
    Arr(7, 2) = "Sales"
    Arr(7, 3) = "Tommy"

    Arr(8, 0) = "Big Bank Inc"
    Arr(8, 1) = "Loans"
    Arr(8, 2) = "Underwriter"
    Arr(8, 3) = "Joey"

    Arr(9, 0) = "Big Bank Inc"
    Arr(9, 1) = "Loans"
    Arr(9, 2) = "Manager"
    Arr(9, 3) = "Robby"

    Arr(10, 0) = "Big Bank Inc"
    Arr(10, 1) = "Loans"
    Arr(10, 2) = "Underwriter"
    Arr(10, 3) = "Johnny"

    Arr(11, 0) = "Big Bank Inc"
    Arr(11, 1) = "Loans"
    Arr(11, 2) = "Underwriter"
    Arr(11, 3) = "Brian"


    '   Create hierarchical structure using nested dictionaries
    '
    '            Company -----------------------Company
    '           /       \                      /       \
    '       Dept         Dept              Dept         Dept
    '      /    \       /    \            /    \       /    \
    '   Role   Role   Role  Role       Role   Role   Role  Role
    '     |     |       |     |          |     |       |     |
    '   Emps   Emps   Emps  Emps       Emps   Emps   Emps  Emps


    Dim CompanyDict As Object
    Set CompanyDict = CreateObject("Scripting.Dictionary")

    Dim i As Long
    For i = LBound(Arr, 1) To UBound(Arr, 1)

        Dim Comp As String
        Dim Dept As String
        Dim Role As String
        Dim Empl As String

        Comp = Arr(i, 0)
        Dept = Arr(i, 1)
        Role = Arr(i, 2)
        Empl = Arr(i, 3)

        If Not CompanyDict.Exists(Comp) Then
            CompanyDict.Add Comp, CreateObject("Scripting.Dictionary")
        End If

        If Not CompanyDict(Comp).Exists(Dept) Then
            CompanyDict(Comp).Add Dept, CreateObject("Scripting.Dictionary")
        End If

        If Not CompanyDict(Comp)(Dept).Exists(Role) Then
            CompanyDict(Comp)(Dept).Add Role, New Collection
        End If

        CompanyDict(Comp)(Dept)(Role).Add Empl

    Next i


    'Print All Employees
    Dim C As Variant
    Dim D As Variant
    Dim R As Variant
    Dim E As Variant

    For Each C In CompanyDict
        For Each D In CompanyDict(C)
            For Each R In CompanyDict(C)(D)
                For Each E In CompanyDict(C)(D)(R)
                    Debug.Print C, D, R, E
                Next E
            Next R
        Next D
    Next C


    'Print all underwriters at Big Bank Inc
    For Each E In CompanyDict("Big Bank Inc")("Loans")("Underwriter")
        Debug.Print E
    Next E


End Sub

Looping Over Dictionaries

To iterate over a Dictionary, use a For Each loop or For loop to iterate over the Keys or Items array properties.

Public Sub Example()

    '''For Each loop

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add "A", 1
    Dict.Add "B", 2
    Dict.Add "C", 3

    'Loop over Keys and get item
    Dim k As Variant
    For Each k In Dict.Keys()
        Debug.Print k, Dict(k)
    Next k

    'Loop over Items
    Dim i As Variant
    For Each i In Dict.Items()
        Debug.Print i
    Next i

End Sub

Public Sub Example()

    '''For loop

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add "A", 1
    Dict.Add "B", 2
    Dict.Add "C", 3

    Dim i As Long

    'Loop over Keys and get item
    Dim DictKeys() As Variant
    DictKeys = Dict.Keys()
    For i = LBound(DictKeys) To UBound(DictKeys)
        Debug.Print DictKeys(i), Dict(DictKeys(i))
    Next i

    'Loop over Items
    Dim DictItems() As Variant
    DictItems = Dict.Items()
    For i = LBound(DictItems) To UBound(DictItems)
        Debug.Print DictItems(i)
    Next i

End Sub

Efficiency

Using a Dictionary can be much more efficient than using an array but there are some things to consider. When the list size is small or when a Dictionary will be created and destroyed many times, it may be more efficient to use an array.

Allocation And Deallocation

Using a Dictionary requires allocating and at some point deallocating an object, which is not as efficient as dimensioning an array. Due to the overhead of object allocation and deallocation, calling a function many times that creates a Dictionary object and then releases it when the function ends can be less efficient than an equivelent function that does not use a Dictionary object. The example below demonstrates the inefficiency of allocating and deallocating a Dictionary.

Public Sub Example()

    Const ITERATIONS As Long = 10000

    Dim Arr() As Variant
    Dim Dict  As Object
    Dim t     As Single
    Dim i     As Long

    'Dimension and erase array
    t = Timer
    For i = 1 To ITERATIONS
        ReDim Arr(0 To 100) As Variant
        Erase Arr
    Next i
    t = Timer - t
    Debug.Print "Array:" & t

    'Allocate and deallocate Dictionary object
    t = Timer
    For i = 1 To ITERATIONS
        Set Dict = CreateObject("Scripting.Dictionary")
        Set Dict = Nothing
    Next i
    t = Timer - t
    Debug.Print "Dictionary:" & t

    'Allocating and deallocating the Dictionary object takes
    'a significant toll on the performance of the loop.

    'Array:      0.0078125 seconds @ 10000 iterations with > 4GHz
    'Dictionary: 3.320313  seconds @ 10000 iterations with > 4GHz

End Sub

Overhead

Accessing an element of an array at a known index and accessing an item in a Dictionary using a key both occur in constant time, O(1). However, there is overhead associated with the hashing function used by the Dictionary, so it is faster to access an array element at a known index. The example below demonstrates the overhead of the Dictionary hashing function.

Public Sub Example()

    Const ITERATIONS As Long = 1000000

    Dim Arr(0 To 0) As Variant
    Dim Dict        As Object
    Dim t           As Single
    Dim i           As Long
    Dim v           As Variant

    Arr(0) = 1

    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add 0, 1

    'Array access
    t = Timer
    For i = 1 To ITERATIONS
        v = Arr(0)
    Next i
    t = Timer - t
    Debug.Print "Array:" & t

    'Dictionary access
    t = Timer
    For i = 1 To ITERATIONS
        v = Dict(0)
    Next i
    t = Timer - t
    Debug.Print "Dictionary:" & t

    'Array:      0.0078125 seconds @ 1000000 iterations with > 4GHz
    'Dictionary: 0.6367188 seconds @ 1000000 iterations with > 4GHz

End Sub

List Size

Accessing an array element at an unknown index occurs in linear time, O(n). For small list sizes, accessing array elements in linear time is faster than accessing Dictionary items in constant time because of the overhead of the hashing function. For larger list sizes however, accessing Dictionary items in constant time is much more efficient. The larger the list the greater the efficiency gap. The example below demonstrates how a Dictionary can be much more efficient than an array for accessing elements at an unknown index.

Public Sub Example()

    Const ITERATIONS As Long = 1000000
    
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim t As Single

    '''Dictionary
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    For i = 0 To 255
        Dict.Add Chr$(i), i
    Next i

    t = Timer
    For i = 1 To ITERATIONS
        k = Dict("A")
    Next i
    t = Timer - t
    Debug.Print "Dictionary: " & t

    '''Array
    Dim KeyArr(0 To 255) As String
    Dim ValueArr(0 To 255) As Long
    For i = 0 To 255
        KeyArr(i) = Chr$(i)
        ValueArr(i) = i
    Next i

    t = Timer
    For i = 1 To ITERATIONS
        For j = LBound(KeyArr) To UBound(KeyArr)
            If KeyArr(j) = "A" Then
                k = ValueArr(j)
                Exit For
            End If
        Next j
    Next i
    t = Timer - t
    Debug.Print "Array: " & t

    'Dictionary: 0.6757813 @ 1000000 iterations with > 4GHz
    'Array:      1.507813  @ 1000000 iterations with > 4GHz

End Sub