VBA – Dictionary

VBA has a dictionary structure. Dictionary is an object, and it can be referenced either with early binding or with a late binding, referring to Microsoft Scripting Runtime:

What is a dictionary? This is the Microsoft definition of the VBA dictionary:

A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

With other words, we have some kind of object, that maps values to keys. What does this mean? Let’s imagine we have a dictionary object, to which we want to save some information. E.g., information about two other people and salary. Like this:

Sub MyDictionary()
    
    Dim myDict As New Scripting.Dictionary
    myDict.Add "Peter", "Peter is a friend."
    myDict.Add "George", "George is a guy I know."
    myDict.Add "Salary", 1000
    
    Debug.Print myDict("Peter")
    
End Sub

There “Peter” is a key and “Peter is a friend” is a value. At the end of the program, we request to see the value of the key “Peter”, which is printed in the immediate window. The dictionary object has methods and properties. This is a short presentation of them.

Methods:

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:

CompareMode	Sets or returns the comparison mode for comparing 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.

What can we do with them? This is an example with all the methods and properties:

Sub MyDictionary()
    
    'Add
    Dim myDict As New Scripting.Dictionary
    myDict.Add "Peter", "Peter is a friend."
    myDict.Add "George", "George is a guy I know."
    myDict.Add "Salary", 1000
    
    'Exists
    If myDict.Exists("Salary") Then
        Debug.Print myDict("Salary")
        myDict("Salary") = myDict("Salary") * 2
        Debug.Print myDict("Salary")
    End If
    
    'Remove
    If myDict.Exists("George") Then
        myDict.Remove ("George")
    End If
    
    'Items
    Dim item As Variant
    For Each item In myDict.Items
        Debug.Print item
    Next item
        
    'Keys
    Dim key As Variant
    For Each key In myDict.Keys
        Debug.Print key
    Next key
    
    'Remove All
    myDict.RemoveAll
    
    'Compare Mode
    myDict.CompareMode = BinaryCompare
    
    myDict.Add "PeTeR", "Peter written as PeTeR"
    myDict.Add "PETeR", "Peter written as PETeR"
    PrintDictionary myDict
    
End Sub

The trick with .CompareMode is that by default its value is set to vbTextCompare, thus “PeTeR” and “PETeR” would not be allowed as different keys. However, once the value is reset to “BinaryCompare”, this is allowed.

True story…