Excel VBA Dictionary – A Complete Guide

VBA Dictionary

“The greatest masterpiece in literature is only a dictionary out of order.” – Jean Cocteau

 
 

A Quick Guide to the VBA Dictionary

FunctionParams
Early binding reference“Microsoft Scripting Runtime”
(Add using Tools->References from the VB menu)
Declare (early binding)
Dim dict As Scripting.Dictionary
Create(early binding) Set dict = New Scripting.Dictionary
Declare (late binding) Dim dict As Object
Create(late binding) Set dict = CreateObject("Scripting.Dictionary")
Add item (key must not already exist) dict.Add Key, Value
e.g. dict.Add "Apples", 50
Change value at key. Automatically adds if the key does not exist. dict(Key) = Value
e.g. dict("Oranges") = 60
Get a value from the dictionary using the keyValue = dict(Key)
e.g. appleCount = dict("Apples")
Check if key existsdict.Exists(Key)
e.g. If dict.Exists("Apples") Then
Remove itemdict.Remove Key
e.g. dict.Remove "Apples"
Remove all itemsdict.RemoveAll
Go through all items (for each loop)Dim key As Variant
For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key
Go through all items (for loop - early binding only)Dim i As Long
For i = 0 To dict.Count - 1
   Debug.Print dict.Keys(i),      dict.Items(i)
Next i
Get the number of itemsdict.Count
Make key case sensitive (the dictionary must be empty).dict.CompareMode = vbBinaryCompare
Make key non case sensitive (the dictionary must be empty).dict.CompareMode = vbTextCompare

 
 

CLICK HERE TO DOWNLOAD THE TEST DATA FOR THIS POST

 
 

What is the VBA Dictionary?

In VBA we use Arrays and Collections to store groups of values. For example, we could use them to store a list of customer names, student marks or a  list of values from a range of cells.

A Dictionary is similar to a Collection. Using both types, we can name an item when we add it. Imagine we are storing the count of different fruit types.

 
 
We could use both a Collection and a Dictionary like this

' Add to Dictionary
dict.Add Key:="Apple", Item:=5

' Add to Collection
coll.Add Item:=5, Key:="Apple"

 
 

VBA Dictionary Fruit

Example of Key, Value pairs

 
 
In both cases, we are storing the value 5 and giving it the name “Apple”. We can now get the value of Apple from both types like this

' Get value from Dictionary
Total = dict("Apple")

' Get value from Collection
Total = coll("Apple")

 
 
So far so good. The Collection however, has two major faults

  1. We cannot check if the key already exists.
  2. We cannot change the value of an existing item.

 
 
The first issue is pretty easy to get around: Check Collection Key exists. The second is more difficult.

The VBA Dictionary does not have these issues. You can check if a Key exists and you can change the Item and the Key.

 
 
For example, we can use the following code to check if we have an item called Apple.

If dict.Exists("Apple") Then 
    dict("Apple") = 78 

 
 

These may seem very simple differences. However, it means that the Dictionary is very useful for certain tasks. Particularly when we need to retrieve the value of an item.

 
 

Dictionary Webinar

If you are a member of the VBA Vault, then click on the image below to access the webinar and the associated source code.

(Note: Website members have access to the full webinar archive.)

 
 

A Dictionary in real world terms

If you are still not clear about a Dictionary then think of it this way. A real world dictionary has a list of keys and items. The Keys are the words and the Items are the definition.

When you want to find the definition of a word you go straight to that word. You don’t read through every item in the Dictionary.

A second real world example is a phone book(remember those?). The Key in a phone book is the name\address and the Item is the phone number. Again you use the name\address combination to quickly find a phone number.

In Excel the VLookup function works in a similar way to a Dictionary. You look up an item based on a unique value.

 
 

A Simple Example of using the VBA Dictionary

The code below give a simple but elegant example of using the Dictionary. It does the following

  1. Adds three fruit types and a value for each to a Dictionary.
  2. The user is asked to enter the name of a fruit.
  3. The code checks if this fruit is in the Dictionary.
  4. If yes then it displays the fruit name and the value.
  5. If no then it informs the user the fruit does not exist.

 
 

Sub CheckFruit()

    ' Select Tools->References from the Visual Basic menu.
    ' Check box beside "Microsoft Scripting Runtime" in the list.
    Dim dict As New Scripting.Dictionary
    
    ' Add to fruit to Dictionary
    dict.Add key:="Apple", Item:=51
    dict.Add key:="Peach", Item:=34
    dict.Add key:="Plum", Item:=43

    Dim sFruit As String
    ' Ask user to enter fruit
    sFruit = InputBox("Please enter the name of a fruit")

    If dict.Exists(sFruit) Then
        MsgBox sFruit & " exists and has value " & dict(sFruit)
    Else
        MsgBox sFruit & " does not exist."
    End If
    
    Set dict = Nothing
    
End Sub

 
 
This is a simple example but it shows how useful a Dictionary is. We will see a real world example later in the post. Let’s look at the basics of using a Dictionary.

 
 

Creating a Dictionary

To use the Dictionary you need to first add the reference.

  1. Select Tools->References from the Visual Basic menu.
  2. Find Microsoft Scripting Runtime in the list and place a check in the box beside it.

 
 
We declare a dictionary as follows

Dim dict As New Scripting.Dictionary

or

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

 
 
Creating a Dictionary in this way is called “Early Binding”. There is also “Late Binding”. Let’s have a look at what this means.

 
 

Early versus Late Binding

To create a Dictionary using Late binding we use the following code. We don’t need to add a reference.

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

 
 
In technical terms Early binding means we decide exactly what we are using up front. With Late binding this decision is made when the application is running. In simple terms the difference is

  1. Early binding requires a reference. Late binding doesn’t.
  2. Early binding allows access to *Intellisense. Late binding doesn’t.
  3. Early binding may require you to manually add the Reference to the “Microsoft Scripting Runtime” for some users.

 
 
(*Intellisense is the feature that shows you the available procedures and properties of an item as you are typing.)

Microsoft recommends that you use early binding in almost all cases.

 
 

Adding Items to the Dictionary

FunctionParamsExample
AddKey, Itemdict.Add "Apples", 50

 
 
We can add items to the dictionary using the Add function. Items can also be added by assigning a value which we will look at in the next section.

 
 
Let’s look at the Add function first. The Add function has two parameters: Key and Item. Both must be supplied

dict.Add Key:="Orange", Item:=45
dict.Add "Apple", 66
dict.Add "12/12/2015", "John"
dict.Add 1, 45.56

 
 
In the first add example above we use the parameter names. You don’t have to do this although it can be helpful when you are starting out.

The Key can be any data type. The Item can be any data type, an object, array, collection or even a dictionary. So you could have a Dictionary of Dictionaries, Array and Collections. But most of the time it will be a value(date, number or text).

If we add a Key that already exists in the Dictionary then we will get the error

 
 
Error 457

 
 
The following code will give this error

dict.Add Key:="Orange", Item:=45

' This line gives an error as key exists already
dict.Add Key:="Orange", Item:=75

 
 

Assigning a Value

OperationFormatExample
AssignDictionary(Key) = Item dict("Oranges") = 60

 
 
We can change the value of a key using the following code

dict("Orange") = 75

 
 
Assigning a value to Key this way has an extra feature. If the Key does not exist it automatically adds the Key and Item to the dictionary. This would be useful where you had a list of sorted items and only wanted the last entry for each one.

' Adds Orange to the dictionary 
dict("Orange") = 45 

' Changes the value of Orange to 100
dict("Orange") = 100

 
 

Checking if a Key Exists

FunctionParametersExample
ExistsKey If dict.Exists("Apples") Then

 
 
We can use the Exists function to check if a key exists in the dictionary

' Checks for the key 'Orange' in the dictionary
If dict.Exists("Orange") Then
    MsgBox "The number of oranges is " & dict("Orange") 
Else
    MsgBox "There is no entry for Orange in the dictionary."
End If

 
 

Storing Multiple Values in One Key

Take a look at the sample data below. We want to store the Amount and Items for each Customer ID.

 
 

 
 

The Dictionary only stores one value so what can we do?

We could use an array or collection as the value but this is unnecessary. The best way to do it is to use a Class Module.

The following code shows how we can do this

' clsCustomer Class Module Code
Public CustomerID As String
Public Amount As Long
Public Items As Long

 
 

' Create a new clsCustomer object
Set oCust = New clsCustomer

' Set the values
oCust.Customer = rg.Cells(i, 1).Value
oCust.Amount = rg.Cells(i, 2).Value
oCust.Items = rg.Cells(i, 3).Value

' Add the new clsCustomer object to the dictionary
dict.Add oCust.Customer, oCust

 
 
You can see that by using the Class Module we can store as many fields as we want.

Using multiple values in the real world is normally a bit more tricky than this simple example. See Example 2 below for more details on how you could implement this.

 
 

Other useful functions

FunctionParametersExample
CountN/Adict.Count
RemoveKeydict.Remove "Apples"
RemoveAllN/Adict.RemoveAll

 
 
The three functions in the above table do the following:

  1. Count – returns the number of items in the Dictionary.
  2. Remove – removes a given key from the Dictionary.
  3. RemoveAll – removes all items from the Dictionary

 
 
The following sub shows an example of how you would use these functions

Sub AddRemoveCount()

    Dim dict As New Scripting.Dictionary

    ' Add some items
    dict.Add "Orange", 55
    dict.Add "Peach", 55
    dict.Add "Plum", 55
    Debug.Print "The number of items is " & dict.Count
    
    ' Remove one item
    dict.Remove "Orange"
    Debug.Print "The number of items is " & dict.Count
    
    ' Remove all items
    dict.RemoveAll
    Debug.Print "The number of items is " & dict.Count

End Sub

 
 

The Key and Case Sensitivity

Some of the string functions in VBA have a vbCompareMethod. This is used for functions that compare strings. It is used to determine if the case of the letters matter.

 
 

VBA Dictionary Key

© BigStockPhoto.com

 
 
The Dictionary uses a similar method. The CompareMode property of the Dictionary is used to determine if the case of the key matters. The settings are

vbTextCompare: Upper and lower case are considered the same.

vbBinaryCompare: Upper and lower case are considered different. This is the default.

 
 
With the Dictionary we can use these settings to determine if the case of the key matters.

Sub CaseMatters()
    
    Dim dict As New Scripting.Dictionary
    dict.CompareMode = vbBinaryCompare
    dict.Add "Orange", 1
    
    ' Prints False because it considers Orange and ORANGE different 
    Debug.Print dict.Exists("ORANGE")    
    
    Set dict = Nothing

End Sub

 
 
This time we use vbTextCompare which means that the case does not matter

Sub CaseMattersNot()
    
    Dim dict As New Scripting.Dictionary
    dict.CompareMode = vbTextCompare
    dict.Add "Orange", 1
    
    ' Prints true because it considers Orange and ORANGE the same
    Debug.Print dict.Exists("ORANGE")    
    
    Set dict = Nothing

End Sub

 
 
Note: The Dictionary must be empty when you use the CompareMode property or you will get the error: “Invalid procedure call or argument”.

 
 

Things to Watch Out For

vbBinaryCompare (the case matters) is the default and this can lead to subtle errors. For example, imagine you have the following data in cells A1 to B2.

Orange, 5
orange, 12

 
 
The following code will create two keys – on for “Orange” and one for “orange”. This is subtle as the only difference is the case of the first letter.

Sub DiffCase()

    Dim dict As New Scripting.Dictionary
    
    dict.Add Key:=(Range("A1")), Item:=Range("B1")
    dict.Add Key:=(Range("A2")), Item:=Range("B2")

End Sub

 
 
If you do use vbTextCompare for the same data you will get an error when you try to add the second key as it considers “Orange” and “orange” the same.

Sub UseTextcompare()

    Dim dict As New Scripting.Dictionary
    dict.CompareMode = vbTextCompare
    
    dict.Add Key:=(Range("A1")), Item:=Range("B1")
    ' This line will give an error as your are trying to add the same key
    dict.Add Key:=(Range("A2")), Item:=Range("B2")

End Sub

 
 
If you use the assign method then it does not take the CompareMode into account. So the following code will still add two keys even though the CompareMode is set to vbTextCompare.

Sub Assign()
    
    Dim dict As New Scripting.Dictionary
    dict.CompareMode = vbTextCompare
    
    ' Adds two keys
    dict(Range("A1")) = Range("B1")
    dict(Range("A2")) = Range("B2")
    
    ' Prints 2
    Debug.Print dict.Count
    
End Sub

 
 

Reading through the Dictionary

We can read through all the items in the Dictionary. We can go through the keys using a For Each loop. We then use the current key to access an item.

Dim k As Variant
For Each k In dict.Keys
    ' Print key and value
    Debug.Print k, dict(k)
Next

 
 
We can also loop through the keys although this only works with Early Binding.

Dim i As Long
For i = 0 To dict.Count - 1
    Debug.Print dict.Keys(i), dict.Items(i)
Next i

 
 

Sorting the Dictionary

Sometimes you may wish to sort the Dictionary either by key or by value.

The Dictionary doesn’t have a sort function so you have to create your own. I have written two sort functions – one for sorting by key and one for sorting by value.

 
 

Sorting by keys

To sort the dictionary by the key you can use the SortDictionaryByKey function below

Public Function SortDictionaryByKey(dict As Object _
                  , Optional sortorder As XlSortOrder = xlAscending) As Object
    
    Dim arrList As Object
    Set arrList = CreateObject("System.Collections.ArrayList")
    
    ' Put keys in an ArrayList
    Dim key As Variant, coll As New Collection
    For Each key In dict
        arrList.Add key
    Next key
    
    ' Sort the keys
    arrList.Sort
    
    ' For descending order, reverse
    If sortorder = xlDescending Then
        arrList.Reverse
    End If
    
    ' Create new dictionary
    Dim dictNew As Object
    Set dictNew = CreateObject("Scripting.Dictionary")
    
    ' Read through the sorted keys and add to new dictionary
    For Each key In arrList
        dictNew.Add key, dict(key)
    Next key
    
    ' Clean up
    Set arrList = Nothing
    Set dict = Nothing
    
    ' Return the new dictionary
    Set SortDictionaryByKey = dictNew
        
End Function

 
 
The code below, shows you how to use SortDictionaryByKey

Sub TestSortByKey()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "Plum", 99
    dict.Add "Apple", 987
    dict.Add "Pear", 234
    dict.Add "Banana", 560
    dict.Add "Orange", 34
    
    PrintDictionary "Original", dict
    
    ' Sort Ascending
    Set dict = SortDictionaryByKey(dict)
    PrintDictionary "Key Ascending", dict
    
    ' Sort Descending
    Set dict = SortDictionaryByKey(dict, xlDescending)
    PrintDictionary "Key Descending", dict
    
End Sub

Public Sub PrintDictionary(ByVal sText As String, dict As Object)
    
    Debug.Print vbCrLf & sText & vbCrLf & String(Len(sText), "=")
    
    Dim key As Variant
    For Each key In dict.keys
        Debug.Print key, dict(key)
    Next
End Sub

 
 

Sorting by values

To sort the dictionary by the values you can use the SortDictionaryByValue function below.

Public Function SortDictionaryByValue(dict As Object _
                    , Optional sortorder As XlSortOrder = xlAscending) As Object
    
    On Error GoTo eh
    
    Dim arrayList As Object
    Set arrayList = CreateObject("System.Collections.ArrayList")
    
    Dim dictTemp As Object
    Set dictTemp = CreateObject("Scripting.Dictionary")
   
    ' Put values in ArrayList and sort
    ' Store values in tempDict with their keys as a collection
    Dim key As Variant, value As Variant, coll As Collection
    For Each key In dict
    
        value = dict(key)
        
        ' if the value doesn't exist in dict then add
        If dictTemp.exists(value) = False Then
            ' create collection to hold keys
            ' - needed for duplicate values
            Set coll = New Collection
            dictTemp.Add value, coll
            
            ' Add the value
            arrayList.Add value
            
        End If
        
        ' Add the current key to the collection
        dictTemp(value).Add key
    
    Next key
    
    ' Sort the value
    arrayList.Sort
    
    ' Reverse if descending
    If sortorder = xlDescending Then
        arrayList.Reverse
    End If
    
    dict.RemoveAll
    
    ' Read through the ArrayList and add the values and corresponding
    ' keys from the dictTemp
    Dim item As Variant
    For Each value In arrayList
        Set coll = dictTemp(value)
        For Each item In coll
            dict.Add item, value
        Next item
    Next value
    
    Set arrayList = Nothing
    
    ' Return the new dictionary
    Set SortDictionaryByValue = dict
        
Done:
    Exit Function
eh:
    If Err.Number = 450 Then
        Err.Raise vbObjectError + 100, "SortDictionaryByValue" _
                , "Cannot sort the dictionary if the value is an object"
    End If
End Function

 
 
The code below shows you how to use SortDictionaryByValue

Sub TestSortByValue()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "Plum", 99
    dict.Add "Apple", 987
    dict.Add "Pear", 234
    dict.Add "Banana", 560
    dict.Add "Orange", 34
    
    PrintDictionary "Original", dict
    
    ' Sort Ascending
    Set dict = SortDictionaryByValue(dict)
    PrintDictionary "Value Ascending", dict
    
    ' Sort Descending
    Set dict = SortDictionaryByValue(dict, xlDescending)
    PrintDictionary "Value Descending", dict
    
End Sub

Public Sub PrintDictionary(ByVal sText As String, dict As Object)
    
    Debug.Print vbCrLf & sText & vbCrLf & String(Len(sText), "=")
    
    Dim key As Variant
    For Each key In dict.keys
        Debug.Print key, dict(key)
    Next key
    
End Sub

 
 

Troubleshooting the Dictionary

This section covers the common errors you may encounter using the Dictionary.

Missing Reference

 
 
Issue: You get the error message “User-defined type not defined”
This normally happens when you create the Dictionary but forget to add the reference.

Dim dict As New Scripting.Dictionary

 
Resolution: Select Tools->Reference from the Visual Basic menu. Place a check in the box beside “Microsoft Scripting Runtime”.
 
See Section: Creating a Dictionary

 
 

Exists is not Working

Issue: You have added a key to the Dictionary but when you use the Exists function it returns false
This is normally an issue with Case Sensitivity(see above).
The following code adds “Apple” as a key. When we check for “apple” it returns false. This is because it takes the case of the letters into account.

dict.Add "Apple", 4

If dict.Exists("apple") Then
    MsgBox "Exists"
Else
    MsgBox "Does not Exist"
End If

 
You can set the CompareMode property to vbTextCompare and this will ignore the case.

Dim dict As New Scripting.Dictionary
dict.CompareMode = vbTextCompare

 
Resolution: Set the CompareMode to vbTextCompare to ignore case or ensure your data has the correct case.
 
See Section: The Key and Case Sensitivity

 
 

Object Variable Error

Issue: You get the error message “Object variable or With block variable not set” when you try to use the Dictionary.

The normally happens when you forget to use New before you use the Dictionary. For example, the following code will cause this error

Dim dict As Scripting.Dictionary
' This line will give "Object variable..." error
dict.Add "Apple", 4

 
Resolution: Use the New keyword when creating the Dictionary

Dim dict As New Scripting.Dictionary

 
Or

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

 
See Section: Creating a Dictionary
 

 
 

Useful Tips for Troubleshooting the Dictionary

If you are investigating an issue with the Dictionary it can be useful to see the contents.

 
 
Use the following sub to Print each Key and Item to the Immediate Window(Ctrl + G).

Sub PrintContents(dict As Scripting.Dictionary)
    
    Dim k As Variant
    For Each k In dict.Keys
        ' Print key and value
        Debug.Print k, dict(k)
    Next

End Sub

 
 
You can use it like this

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

' Add items to Dictionary here

' Print the contents of the Dictionary to the Immediate Window
PrintContents dict

 
 
If you are stepping through the code you can also add dict.Count to the Watch Window to see how many items are currently in the Dictionary. Right-click anywhere in the code window and select Add Watch. Type dict.Count into the text box and click Ok.

You can also use the Dictionary itself as a Watch. Add Dict to the Watch window. If you click on the plus sign you will see the contents of the Dictionary. This can be useful but it only shows the key and not the item.

Note: You can only view Watches when the code is running.

 
 

A Real World Dictionary Example

Let’s have a look at a real world example of using a dictionary. Our data for this example is the World Cup Final matches from 2014.

 
 
VBA World Cup

 
 
CLICK HERE TO DOWNLOAD THE TEST DATA FOR THIS POST

 
 
Our task here is to get the number of goals scored by each team.

The first thing we need to do is to read all the data. The following code reads through all the matches and prints the names of the two teams involved.

Sub GetTotals()
    
    ' Get worksheet
    Dim wk As Worksheet
    Set wk = ThisWorkbook.Worksheets("2014")
    
    ' Get range for all the matches
    Dim rgMatches As Range
    Set rgMatches = wk.Range("A1").CurrentRegion
    
    Dim sTeam1 As String, sTeam2 As String
    Dim lGoals1 As Long, lGoals2 As Long

    Dim row As Range
    For Each row In rgMatches.Rows
        ' read the data from each match
        sTeam1 = row.Cells(1, 5)
        sTeam2 = row.Cells(1, 9)
        lGoals1 = row.Cells(1, 6)
        lGoals2 = row.Cells(1, 7)
        ' Print each teams/goals to Immediate Window(Ctrl + G)
        Debug.Print sTeam1, sTeam2, lGoals1, lGoals2
    Next row
    
End Sub

 
 
What we want to do now is to store each team and the goals they scored. When we meet a team for the first time we add the name as a Key and the number of goals as the Item.

 
 

VBA Dictionary World Cup

Celebrating a Goal | © BigStockPhoto.com

 
 
If the team has already been added then we add the goals they scored in the current match to their total.

 
 
We can use the following line to add goals to the current team

dict(sTeam1) = dict(sTeam1) + lGoals1

 
This line is very powerful.

If the teams exists in the Dictionary, the current goals are added to the current total for that team.

If the team does not exist in the Dictionary then it will automatically add the team to the Dictionary and set the value to the goals.

 
For example, imagine the Dictionary has one entry

Key, Value
Brazil, 5

Then

dict("Brazil") = dict("Brazil") + 3

 
will update the dictionary so it now looks like this
 
Key, Value
Brazil, 8

 
The line

dict("France") = dict("France") + 3

 
will update the dictionary so it now looks like this
 
Key, Value
Brazil, 8
France, 3

 

This saves us having to write code like this

If dict.Exists(sTeam1) Then
    ' If exists add to total
    dict(sTeam1) = dict(sTeam1) + lGoals1
Else
    ' if doesn't exist then add
    dict(sTeam1) = lGoals1
End If

 
 
We write out the values from the Dictionary to the worksheet as follows

Public Sub WriteDictionary(dict As Scripting.Dictionary _
    , shReport As Worksheet)
    
    shReport.Cells.Clear
    Dim k As Variant, lRow As Long
    lRow = 1
    For Each k In dict.Keys
        shReport.Cells(lRow, 1) = k
        shReport.Cells(lRow, 2) = dict(k)
        lRow = lRow + 1
    Next
    
End Sub

 
 
We obviously want the scores to be sorted. It is much easier to read this way. There is no easy way to sort a Dictionary. The way to do it is to copy all the items to an array. Sort the array and copy the items back to a Dictionary.

 
 
What we can do is sort the data once it has been written to the worksheet. We can use the following code to do this

Public Sub SortByScore(shReport As Worksheet)
    
    Dim rg As Range
    Set rg = shReport.Range("A1").CurrentRegion
    rg.sort rg.Columns("B"), xlDescending
    
End Sub

 
 
Our final GetTotals Sub looks like this

Sub GetTotals()
    
    ' Create dictionary
    Dim dict As New Scripting.Dictionary
    
    ' Get worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("2014")
    
    ' Get range
    Dim rgMatches As Range
    Set rgMatches = sh.Range("A1").CurrentRegion
    
    Dim sTeam1 As String, sTeam2 As String
    Dim lGoals1 As Long, lGoals2 As Long
    Dim i As Long
    For i = 2 To rgMatches.Rows.Count
        sTeam1 = rgMatches.Cells(i, 5)
        sTeam2 = rgMatches.Cells(i, 9)
        lGoals1 = rgMatches.Cells(i, 6)
        lGoals2 = rgMatches.Cells(i, 7)
        
        dict(sTeam1) = dict(sTeam1) + lGoals1
        dict(sTeam2) = dict(sTeam2) + lGoals2
        
    Next i
    
    ' Get the report worksheet
    Dim shReport As Worksheet
    Set shReport = ThisWorkbook.Worksheets("Report")
    
    ' Write the teams and scores to the worksheet
    WriteDictionary dict, shReport
    
    ' Sort the range
    SortByScore shReport

    ' Clean up
    Set dict = Nothing
        
End Sub

 
 
When you run this code you will get the following results

VBA Results

Teams ordered by number of goals scored

 
 

Example 2 – Dealing with Multiple Values

We are going to use the data from the Multiple Values section above
 
 

 
 

Imagine this data starts at cell A1. Then we can use the code below to read to the dictionary.

The code includes two subs for displaying the data:

  1. WriteToImmediate prints the contents of the dictionary to the Immediate Window.
  2. WriteToWorksheet writes the contents of the dictionary to the worksheet called Output.

To run this example:

  1. Create a worksheet called Customers.
  2. Add the above data to the worksheet starting at cell A1.
  3. Create a worksheet called Output and leave it blank.
  4. Create a new class module and add the first piece of code from below.
  5. Create a new standard module and add the second piece of code from below.
  6. Press F5 to run and select Main from the menu.
  7. Check the ImmediateWindow(Ctrl + G) and the Output worksheet to see the results.

 
 

' clsCustomer Class Module Code
Public CustomerID As String
Public Amount As Long
Public Items As Long

 
 

' Standard module Code
Sub Main()

    Dim dict As Dictionary
    
    ' Read the data to the dictionary
    Set dict = ReadMultiItems
    
    ' Write the Dictionary contents to the Immediate Window(Ctrl + G)
    WriteToImmediate dict
    
    ' Write the Dictionary contents to a worksheet
    WriteToWorksheet dict, ThisWorkbook.Worksheets("Output")

End Sub

Private Function ReadMultiItems() As Dictionary

    ' Declare and create the Dictionary
    Dim dict As New Dictionary
    
    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Customers")
    
    ' Get the range of all the adjacent data using CurrentRegion
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion

    Dim oCust As clsCustomer, i As Long
    ' read through the data
    For i = 2 To rg.Rows.Count
    
        ' Create a new clsCustomer object
        Set oCust = New clsCustomer
        
        ' Set the values
        oCust.CustomerID = rg.Cells(i, 1).Value
        oCust.Amount = rg.Cells(i, 2).Value
        oCust.Items = rg.Cells(i, 3).Value
        
        ' Add the new clsCustomer object to the dictionary
        dict.Add oCust.CustomerID, oCust
            
    Next i
    
    ' Return the dictionary to the Main sub
    Set ReadMultiItems = dict

End Function

' Write the Dictionary contents to the Immediate Window(Ctrl + G)
Private Sub WriteToImmediate(dict As Dictionary)
    
    Dim key As Variant, oCust As clsCustomer
    ' Read through the dictionary
    For Each key In dict.Keys
        Set oCust = dict(key)
        With oCust
            ' Write to the Immediate Window (Ctrl + G)
            Debug.Print .CustomerID, .Amount, .Items
        End With
        
    Next key
    
End Sub

' Write the Dictionary contents  to a worksheet
Private Sub WriteToWorksheet(dict As Dictionary, sh As Worksheet)
    
    ' Delete all existing data from the worksheet
    sh.Cells.ClearContents
    
    Dim row As Long
    row = 1
    
    Dim key As Variant, oCust As clsCustomer
    ' Read through the dictionary
    For Each key In dict.Keys
        Set oCust = dict(key)
        With oCust
            ' Write out the values
            sh.Cells(row, 1).Value = .CustomerID
            sh.Cells(row, 2).Value = .Amount
            sh.Cells(row, 3).Value = .Items
            row = row + 1
        End With
        
    Next key
    
End Sub

 
 

Example 3 – Summing Multiple Values

In this example were are going to make a small update to Example 2. In that example there was only one entry per customer in the data.

This time there will be multiple entries for some customers and we want to sum the total Amount and total Items for each customer.

See the updated dataset below

 
 

 
 
The only change from the code in Example 2 is for the code in the ReadMultiItems sub. So you can take the code in Example 2 and replace the ReadMultiItems sub with the one below.

Note: If you run the “Example 2” code on data with multiple copies of the CustomerID, it will give the “Key already exists error”.
 
 

Private Function ReadMultiItems() As Dictionary

    ' Declare and Create the Dictionary
    Dim dict As New Dictionary
    
    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Customers")
    
    ' Get the range of all the adjacent data using CurrentRegion
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion

    Dim oCust As clsCustomer, i As Long, CustomerID As String
    ' read through the data
    For i = 2 To rg.Rows.Count
        
        CustomerID = rg.Cells(i, 1).Value
        
        ' check if the customerID has been added already
        If dict.Exists(CustomerID) = True Then
            ' Get the existing customer object
            Set oCust = dict(CustomerID)
        Else
            ' Create a new clsCustomer object
            Set oCust = New clsCustomer
        
             ' Add the new clsCustomer object to the dictionary
            dict.Add CustomerID, oCust
        End If
        
        ' Set the values
        oCust.CustomerID = CustomerID
        oCust.Amount = oCust.Amount + rg.Cells(i, 2).Value
        oCust.Items = oCust.Items + rg.Cells(i, 3).Value
            
    Next i
    
    ' Return the dictionary to the Main sub
    Set ReadMultiItems = dict

End Function

 
 

When To Use The Dictionary

So when should you use the VBA Dictionary? When you have a task where:

  1. You have a list of unique items e.g. countries, invoice numbers, customer name and addresses, project ids, product names etc.
  2. You need to retrieve the value of a unique item.

 
 

VBA Dictionary 1

Key/Values of Countries and Land area in Km2

 
 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 

100 comments

  1. Paul,
    I was very happy to find your site and I believe the Dictionary is something I have been needing for a long time. I am having problem with it though. I am attempting to use the Dictionary to evaluate a student’s loan amount in a term. The term is the key (2017SP, 2017SU, 2017FA, etc) and the item is the loan amount value. I am using a For Next loop to assign the key and item and everything looks great in the Immediate window. Keys are there and the loan amounts are there. The problem occurs when I go to utilize the item assigned to the key. Below is a snippet of the code where the problem is occurring. Code is a little convoluted at the moment due to trying to solve the problem. When I step though the code in debug mode Dictionary works perfect in the code using Debug.Print, but appears empty in the code right below that. Just thought I would see if you notice anything that might be causing the issue. Thanks!

    For lCol = 3 To lLastCol
    dTerm.Add Key:=wsLoan.Cells(1, lCol), Item:=wsLoan.Cells(rLoan.Row, lCol)
    Next lCol

    For Each k In dTerm.Keys
    ‘ Print key and value
    Debug.Print k, dTerm(k)
    Next
    ‘Sums loans for the terms the student was in attendance
    For lTerm = 14 To lLastTerm
    sTerm = wsDegree.Cells(lRow, lTerm)
    dbLoan = dTerm(sTerm)
    wsDegree.Cells(lRow, 9) = wsDegree.Cells(lRow, 9) + dbLoan
    Next lTerm

    1. Replace

      dTerm.Add Key:=wsLoan.Cells(1, lCol), Item:=wsLoan.Cells(rLoan.Row, lCol)
      with
      dTerm.Add Key:=wsLoan.Cells(1, lCol).Value, Item:=wsLoan.Cells(rLoan.Row, lCol)

  2. Hi Paul,
    I’ve read many articles on Dictionaries the past few weeks and I’ve found contradictory information, hopefully you’ll clarify a few issues I’ve encountered:

    1 – Some say Dictionaries are classes, others say objects. My take (right or wrong; most likely wrong) is that a dictionary is an instance of the dictionary class; and the records each dictionary holds are keys and values; that’s it. You can’t have a dictionary that holds more than two things unless you nest the dictionaries; is this right?

    2 – Surely a dictionary’s name is a property. Is it possible to rename a dictionary after it is created?

    It’d be great to see a full-blown nested dictionary tutorial out there as I’ve been searching on this subject for 3 weeks and there’s very little out there at the moment. A lot of incorrect references from Microsoft reference VBScript as VBA lol.

    Regards
    Dal

    1. Hi Dal

      Yes. An object is an instance of a class module.
      In other words, the class module is like the prototype and the object is when you create it and use it.

      “You can’t have a dictionary that holds more than two things unless you nest the dictionaries; is this right?”
      If you need more than one item you can use a class module. For example, if you were storing customer records such as ID, name, address, email, then you could use a class module object for each customer and store this in the Dictionary. See Class Module Example.

      A Dictionary doesn’t have a name. You reference it using a variable which is like a name.

      Hope this helps.

      Paul

    1. Yes, one item per key.

      If you need to store more variables you can use a class module. You can see an example of using a Class Module with a collection here.

      It’s pretty similar to use a Dictionary in this way.

      1. Thank you for the clear examples. When I try the suggested and add an object as a key.
        I get the following behavior

        object1 and object2 have the same values on initialization

        I add object1
        then when I do
        if dict.Exists(object2) –> is false –> i hoped for true..

        then when I change object1 and check if it exist I get true… –> hoped false

        am I correct that it only checks for references?

        .. I just wanted to add an array to the key so I can check for duplicates instead of writing ‘complex code’ myself 🙂 …

        Again thanks for the nice examples

        1. Hi Marcel,

          It compares the address of the object they are pointing at( see VBA Objects in memory).

          See the example code below for how it works.

          Paul

          Sub TestDictionaryKey()
          
              Dim dict As New Dictionary
              
              ' They are all the same
              Dim o1 As clsData, o2 As clsData, o3 As clsData
              
              ' o1 is different than o2 and o3
              Set o1 = New clsData
              
              dict.Add o1, 1
              Debug.Print dict.Exists(o1), dict.Exists(o2), dict.Exists(o3)
              
              ' o2 is now the same o1
              Set o2 = o1
              Debug.Print dict.Exists(o1), dict.Exists(o2), dict.Exists(o3)
              
          End Sub
          
  3. Hi Paul,

    I’m Grateful to have found your site … I think! 😉 This code idea has been driving me mad … could you help me understand why my if exists is failing? whether I use integers as the key or strings it keeps failing. Trying to add from a range and then reference the range as existing.

    Sub dictionarytest()

    Dim MyDictionary As Scripting.Dictionary
    Set MyDictionary = New Scripting.Dictionary
    MyDictionary.CompareMode = vbTextCompare

    ‘works as expected..
    Dim z As Variant
    For Each z In Range(“A1:A4”)
    MyDictionary.Add z, z.Offset(0, 1)
    Debug.Print z, MyDictionary(z)
    Next

    ‘my plan is to do a pseudo index,match,match through dictionary, but _
    I can’t get the dictionary keys to exist = true

    Dim value As Variant
    Dim j As Variant

    Debug.Print MyDictionary.Count()

    ‘this range A10:A13 is arbitrary data I ultimately want the dictionary to _
    look at and place the values in an adjacent column.
    For Each j In Range(“A10:A13”)
    If MyDictionary.Exists(j) Then
    Range(“B” & j.Row).value = MyDictionary(j)
    Debug.Print True
    Else
    Debug.Print False
    End If
    Next

    End Sub

    1. You’re adding the range and not the value

      MyDictionary.Add z, z.Offset(0, 1)

      should be

      MyDictionary.Add z.Value, z.Offset(0, 1).Value

  4. Paul, a great tutorial. However, I’m just a little unclear on one aspect. A dictionary cannot be accessed outwith the procedure in which it is created, would that be correct? For example, if I ran code to create a Scripting Dictionary called dictF on workbook open (an example, but I could place this code in any module and run at any time obviously):
    Sub workbookOpen
    dim dictF as scripting.dictionary
    set dictF = new scripting.dictionary
    dictf.add Key….etc
    end sub

    I don’t believe I could access that from a subsequent procedure in another module within the workbook. The dictionary essentially is built within the module/Sub code and any performance on that must occur at that time. If I need to use this dictionary multiple times for varying functions, then I must create it each time. What I mean is that there is no way to reference it using:
    Dim df as scripting.dictionary
    Set df = scripting.dictionary(dictF)
    df….do something

    I cannot see anywhere in your code above where you reference the dictionary outwith the module in which it is created, nor a way of expressing this, so I’m assuming that’s not what they’re for?

    Thanks,
    Rico

    1. Hi Rico,

      Thanks for your comment. You are talking about program design more so than the Dictionary.

      You can make the Dictionary a global variable which is accessible everywhere. However, this is considered poor practice and can lead to many problems.

      The dictionary is a group of variables. All variables only exist when the code runs. They are temporary storage space in memory.

      If you want to use the dictionary in a different module you can pass it to the module and return it from the module. The code below is a typical example of using the dictionary

      Sub UseDictionary()
      
          ' Create variable to reference the dictionary
          Dim dict As Dictionary
          
          ' Read dictionary from function
          Set dict = ReadData
      
      
          ' Write the dictionary values - pass dictionary to sub
          WriteDictionary dict
      
      End Sub
      
      Function ReadData() As Dictionary
      
          ' Create the dictionary
          Dim dict As New Dictionary
          
          ' Read data to the dictionary here
          
          ' return the dictionary
          Set ReadData = dict
      
      End Function
      
      Sub WriteDictionary(dict As Dictionary)
      
      
      End Sub
      
  5. Hi Mr. Kelly,
    Google keeps directing me to Excel Macro Mastery when I have VBA questions. Typically I find the answer here! What a great resource. Thank you so much. I didn’t know VBA implemented dictionaries. Please pardon my marginally relevant comment. It won’t bother me if you delete this.
    Thank you,
    Jim

  6. Mr. Kelly,

    First many thanks for such a wonderful tutorials.
    My question is, can be item: also a string text, or not.

    Thank you, Tilen

  7. Paul,

    I just discovered something very important that led to about 2 straight work days of complete frustration. Setting a watch for dct.exists(key) will add an empty item with that key if it does not exist. It will literally return false all the way up to the moment before the code is executed and magically return true. You might want to add an edit to this article as well as the debugging article.

    1. Hi Jeff,

      I haven’t been able to reproduce this exist scenario. But I have seen strange behaviour when using the Watch Window with the Dictionary.

  8. Bonjour Mr Kelly
    Merci, merci, merci
    Il n’existe pas beaucoup de site web de cette qualité sur les éléments avancés du VBA (Objet, collection…)
    Bien cordialement
    Jean-Baptiste

  9. Paul, what about nested dictionaries? How can I set up those?
    I have a some keys in my “Parent” dictionary that would require two parameters to be defined (those would be in the “Child” dictionary). I suspect that it would be more convenient to use a Class Module instead, right?

    Thanks for the great tutorial!

    1. Hi Jose,

      I get asked about nested dictionaries but it is very rare that they are needed.

      The Dictionary stores a key and value. If you want to store more than one value then you can use a class module.

      In this example I use a class module to store items in a collection. You can easily replace the collection with a dictionary in this example.

      Paul

Leave a Reply

Your email address will not be published. Required fields are marked *