VBA Dictionary Objects

Associated Files Download Links

Using a VBA Dictionary

A VBA dictionary operates in a similar way to a collection object, but it has more properties and methods, and offers more flexibility

The dictionary stores the data in memory and can be easily manipulated. There is no auto calculation, background backup, and screen refreshing required, so your code will run considerably faster.

The dictionary object works in a similar way to a normal dictionary that you would use if you want to find out the meaning of a word. Each entry in the dictionary object has a ‘key’ value and an ‘item’ value.  You use the ‘key’ the key value to lookup the item value in the dictionary object, in a similar way that you would use a conventional dictionary.

Because of the way the dictionary object works, the key values must all be unique, in the same way as in a conventional dictionary.  Imagine if you opened your conventional dictionary to look up the meaning of a word, and found the word listed more than once with two entirely different definitions. You would be very confused!

Key values are usually text or numbers, or both.  Users often find it easier to remember names of keys as text rather than just numbers.

Comparing to a collection object, the collection object is read only.  It only has two methods (Add and Remove) and two properties (Count and Item). Once an item has been added to a collection object, it can only be removed, but not edited, which is a cumbersome procedure if the value of an item needs to be changed.

A dictionary object will change in size automatically to suit the number of items within it.  It does not need to be defined in size, like a conventional array

The dictionary object is one-dimensional and the data type is ‘Variant’, so any data type can be entered in to it e.g. numeric, text, date

The VBA dictionary is not native to Excel and needs to be accessed by either early or late binding when defining the dictionary object

Sub EarlyBindingExample()
    Dim MyDictionary As New Scripting.Dictionary
End Sub
Sub LateBindingExample()
    Dim MyDictionary As Object
    Set MyDictionary = CreateObject("Scripting.Dictionary")
End Sub

If you use the early binding, you must add a reference to the ‘Microsoft Scripting Runtime’ library

You do this by selecting ‘Tools | References’ on the menu bar of the Visual Basic Editor (VBE) window and a pop-up window will appear with a list of available libraries.

PIC 01

Scroll down to ‘Microsoft Scripting Runtime’ and tick the box next to it.  Click OK and this library is now part of your VBA project and can be referenced using early binding.  All examples of code in this article will use early binding.

Your code will run considerably faster with early binding, because it is all compiled up front. With late binding, the object has to be compiled as the code runs

The Scripting Runtime library has ‘Intellisense’. As you write your code, you will see lists of available methods and properties appearing, which helps to prevent mistakes being made in spelling, which will cause bugs in your program

Also, if you press F2 within the VBE, and select the ‘Scripting’ library, you will see all the methods and properties available, and the parameters required for each


Distributing your Excel Application Containing a Dictionary

As already pointed out, the Scripting Runtime library is not part of Excel VBA so if you distribute your application to other users, they must have access to the Scripting Runtime library on their computer.  If they have not, then an error will occur.

It is a good idea to include some VBA code to check that this library is present when your Excel application is loaded.  You can use the ‘Dir’ command to do this on the ‘Workbook Open’ event

The location of the file is C:\Windows\SysWOW64\scrrun.dll


Scope of a Dictionary Object

The Dictionary object is only available while the Excel workbook is open. It does not get saved when the workbook is saved.

If your dictionary is to be available to all the routines within your module, you need to declare it (Dim) in the Declare section at the very top of the module

You define it as a global object if you want your dictionary to be used throughout your code.

Global MyDictionary As New Dictionary

Populating and Reading from Your Dictionary

To start with, you need to create a dictionary, populate it with some data, and then iterate through it to prove that the data exists

Sub PopulateReadDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3", 30

    For n = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n)
    Next n
End Sub

This code creates a new dictionary object called ‘MyDictionary’ and then populates it with three items.  The Add method has two parameters – Key and Item, and they are both required

The data types for Key and Item are both variant so they will accept any type of data – numeric, text, date, etc

The first item in the dictionary could be added as:

MyDictionary.Add 10, "MyItem1"

The values have been reversed between Key and Item, but this would still work, although the search key would now become 10.

However, it is important to understand that the key value is the lookup value in the dictionary. It works in a very similar way to the VLOOKUP function in Excel.  Because all the keys have to have unique values, you can specify a key value and instantly return the item value for that key.

Note that the dictionary index starts at 0 so you need to subtract 1 from the dictionary count used in the For…Next loop

You can also use a For…Each loop to read the values in the dictionary:

Sub PopulateReadDictionary()
    Dim MyDictionary As New Scripting.Dictionary, I As Variant

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3", 30

    For Each I In MyDictionary.Keys
        MsgBox I & " " & MyDictionary(I)
    Next I
End Sub

This code will iterate through each item and display the item key and the item value


Using the Item Index Number

You can use the index number of a key or item to read the value

Sub IndexNumbers()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.CompareMode = TextCompare

    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    MsgBox MyDictionary.Keys(2)
    MsgBox MyDictionary.Items(1)
End Sub

This code will return the key ‘item3’ as the index starts at 0, and the item value 20

You can refer to individual key or item values within the Keys or Items collections by using the index numbers.

Filtering the Dictionary

There is not a direct method to do this, but it is quite simple to write code to do it:

Sub FilterDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "AAItem1", 10
    MyDictionary.Add "BBItem2", 20
    MyDictionary.Add "BBItem3", 30

    For Each I In Filter(MyDictionary.Keys, "BB")
        MsgBox MyDictionary.Item(I)
    Next I
End Sub

The filter value only works from the beginning of the key value.  You cannot use wildcards in the filter. This code will return the two item values with key names beginning with ‘BB’

This will give you a subset of the dictionary based on your filter value, which you could then transfer to another dictionary or a worksheet. With careful planning on key names, making sure that there is a meaningful prefix to each, you would easily be able to split the dictionary into various component parts.

Changing an Item Value of a Key

The dictionary object has a big advantage over a collection in that the item value can be changed e.g.

MyDictionary("MyItem4") = "40"

In the collection, you would need to delete that entry and then re-create it.

Here is a code example:

Sub PopulateReadDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3", 30
    MyDictionary("MyItem2") = "25"
    MyDictionary("MyItem4") = "40"

    For n = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n)
    Next n
End Sub

The above code sets up three items within the dictionary, and then changes the value of ‘MyItem2’ from 20 to 25.

It also changes the value of ‘MyItem4’ to 40.  Note that in the add statements of the code, no ‘MyItem4’ was added.  When you change the value of a key that does not exist, it is automatically created. This is extremely convenient, as no error is triggered, but it does mean that you need to be careful with your key names. An inadvertent spelling error in the key name would mean that a new key is created, and the original key name would still have the old value.

This could easily lead to integrity problems in the dictionary object.


Test if a Key Exists

You can check if a key value exists within the dictionary

Sub CheckExistsDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3", 30

    MsgBox MyDictionary.Exists("MyItem8")
End Sub

The code adds three items to a new dictionary object, and then tests for a key (‘MyItem8’) which is not in the dictionary.  This returns False, but had one of the existing keys been used, it would return True

Wildcards are not accepted. Search text is also case sensitive by default, but this can be changed (see later in article)


Using Multiple Values in a Dictionary

Unlike an array, the dictionary object is one dimensional only.  This can lead to problems if you have several values that you want to put against a key.

One way round this is to concatenate each item value using a delimiter character in between each value e.g. ‘|’

Sub MultipleValues()

    'Create dictionary object and variables
    Dim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As String
    Dim V3 As Date, Temp As String, N As Integer

    'Populate 3 variables to demonstrate multiple values
    V1 = 5
    V2 = "Example of multiple values"
    V3 = "22-Jul-2020"

    'Add the concatenated value to the dictionary using "|" delimiter
    MyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"

     'Capture the concatenated dictionary value from the dictionary into a variable
     Temp = MyDictionary("MyMultipleItem")

    'Iterate through the concatenated string to separate the individual valuues

        'Find the position of a delimiter
        N = InStr(Temp, "|")

        'If there are no more delimiters the exit Do loop
        If N = 0 Then Exit Do

        'Display text relative to position of delimiter found
        MsgBox Left(Temp, N - 1)

        'Truncate the concatenated string to the next character after the delimiter found 
        Temp = Mid(Temp, N + 1)
End Sub


Another way around this problem is to design your own sub-scripting system for key names.  There is no reason why you should not use brackets and numbers in key names

Sub MultipleValues()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "Multiple(1)", 5
    MyDictionary.Add "Multiple(2)", "Example of multiple values"
    MyDictionary.Add "Multiple(3)", "22-Jul-2020"

    For N = 1 To 3
        MsgBox MyDictionary("Multiple(" & N & ")")
    Next N
End Sub

This code adds three keys to the dictionary, but each key name contains a sub script number in brackets. You can then refer to key name, but using the sub script number concatenated in.  This is very similar to using an array object

Deleting Items

You can remove individual items by reference to the key value

MyDictionary.Remove (“MyItem2”)

Note that because key names are unique, this only removes that one particular key and item value

You can also clear the dictionary completely


Here is an example of using ‘Remove’ in VBA:

Sub RemoveValues()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30
    MyDictionary.Remove ("Item2")

    For N = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N)
    Next N


    MsgBox MyDictionary.Count
End Sub


The code adds three items to the dictionary, and then removes ‘Item2’. It then iterates through the dictionary to prove that ‘Item2’ no longer exists

Finally, the code removes all items in the dictionary and displays the dictionary count, which is now zero.


Changing Case Sensitivity for Searches

If you do a search for a key, it is case sensitive by default.  However, you can use the ‘CompareMode’ property to change this.

Note that this must be done immediately in the code after you create the dictionary object, but before you add any data into the dictionary.  Once the compare mode has been set, it cannot be changed within that dictionary.

Sub ChangeCaseSensitivity()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.CompareMode = TextCompare
    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    MsgBox MyDictionary.Exists("item2")
End Sub

In this example, the compare mode is set to ‘TextCompare’ which means that it is not case sensitive.  The ‘Exists’ statement at the end of the example will return True, in spite of the fact that the search text is all in lower case.

In Excel there are only two values that can be used for compare mode.  Binary Compare is case sensitive, and Text Compare is non case sensitive

If you have compare mode set to Binary Compare, you need to be careful in naming your keys. If you set a name to have an upper-case letter as the first character, then when you are changing the value, you must make sure that you still make the first character upper-case.  If you start with a lower-case character, this will be interpreted as a new key, and could easily lead to confusion and errors in your dictionary

Remember that if you change a value for a key and the key name does not exist due to a Binary Compare being used, a new key and value will be added to the dictionary.

If you use Text Compare instead, then any value changes will go to the key regardless of case. If you try to add the same item but spelt with a different case character, you will get an error because it already exists.

Sorting the Dictionary

As with the collection object, there is no method provided of being able to sort the dictionary, either using keys or item values.

However, since the VBA code is sitting in an Excel workbook, the dictionary data can be transferred into Excel in tabular form, and then the Excel sort facility can be applied to it. The dictionary can then be cleared using ‘RemoveAll’ and the sorted values added from the worksheet.

This code will sort both the keys and the items values

Sub SortMyDictionary()

    Dim MyDictionary As New Dictionary
    Dim Counter As Long

    'Build dictionary with random order items
    MyDictionary.Add "Item5", 5
    MyDictionary.Add "Item2", 15
    MyDictionary.Add "Item4", 11
    MyDictionary.Add "Item1", 2
    MyDictionary.Add "Item3", 19

    'Capture number of items in dictionary for future use
    Counter = MyDictionary.Count
    'Iterate through dictionary copying each key and item to a consecutive cell on 'Sheet1' (column A)
    For N = 0 To MyDictionary.Count - 1
        Sheets("Sheet1").Cells(N + 1, 1) = MyDictionary.Keys(N)
        Sheets("Sheet1").Cells(N + 1, 2) = MyDictionary.Items(N)
    Next N

    'Activate the Sheet1 and use the Excel sort routine to sort the data into ascending order
    Range("A1:B" & MyDictionary.Count).Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With

    'Clear all items from the dictionary

    'Copy the cell values back into the empty dictionary object using the stored value (Counter) for the 'loop
    For N = 1 To Counter
        MyDictionary.Add Sheets("Sheet1").Cells(N, 1).Value, Sheets("Sheet1").Cells(N, 2).Value
    Next N

    'Iterate through the dictionary to prove out the order that the items are now in
    For N = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N)
    Next N

    'Clear the worksheet (Sheet1) - if necessary, delete it as well
    Sheets("Sheet1").Range(Cells(1, 1), Cells(Counter, 2)).Clear
End Sub

This code creates a dictionary with five random order values added.  It captures the number of items into a variable, and then iterates through the dictionary, transferring the key and item values into separate columns on a worksheet.

It then sorts the downloaded range, using column A as the sort field. The dictionary is cleared completely using the ‘RemoveAll’ method, and the code then iterates through the cell values in the worksheet adding them back to the dictionary.

Finally, the code iterates through the dictionary, displaying the key and item values concatenated to prove that the sort has worked.

By changing the parameters in the sort code, the data could be sorted by item values.


Copying a List of Keys to a Worksheet

You can copy a list of all key values into a worksheet using the following code:

Sub CopyKeyList()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.CompareMode = TextCompare
    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    Sheets("Sheet1").Range("A1").Value = Join(MyDictionary.Keys, vbLf)
End Sub

This will produce the result in your worksheet:

PIC 02

You can copy an entire dictionary into a worksheet using this code:

Sub CopyIntoWorksheet()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    Range("A1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys)
    Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items)
End Sub

Your worksheet will look like this:

PIC 03

Comparing a Dictionary with a Collection

The Dictionary is faster than a collection.

A Collection is already within VBA. A Dictionary needs a reference to the Microsoft Scripting Dictionary to be added or an object created using late binding

A Collection item can only be written once and read many times. In a Dictionary, the item value can be changed. With a Collection, the item has to be removed and then the changed item added back.

The Collection works on index values, which can be difficult to work out which index value belongs where.  The Dictionary works on unique key values which are used to locate an item

Retrieving a single item is slower in a large Collection than in a Dictionary

In a Collection, the keys are only used to lookup data and are not retrievable.  In a dictionary, keys can be tested for existence, and can be used to find a particular item.

Collections are case-sensitive and this cannot be changed. In a Dictionary, the compare mode can be set to give case-sensitivity or non-case-sensitivity

In a Collection, the key values must be strings.  In a Dictionary they can be any data type e.g. numeric, date, etc

Removing all items in a Collection involves re-defining the Collection object.  The Dictionary has the ‘RemoveAll’ method for this.