Excel VBA Dictionary – A Complete Guide
“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)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 used 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"
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
We cannot check if the key already exists.
We cannot change the value of an existing item.
The VBA Dictionary does not have these issues. You can check if a Key exists and you can change the Item (Note: you cannot change the Key itself).
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.
Magical Words | © BigStockPhoto.com
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
Adds three fruit types and a value for each to a Dictionary.
The user is asked to enter the name of a fruit.
The code checks if this fruit is in the Dictionary.
If yes then it displays the fruit name and the value.
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.
Select Tools->References from the Visual Basic menu.
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 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
Early binding requires a reference. Late binding doesn’t.
Early binding allows access to *Intellisense. Late binding doesn’t.
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
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
Other useful functions
FunctionParametersExample
CountN/Adict.Count
RemoveKeydict.Remove "Apples"
RemoveAllN/Adict.RemoveAll
The three functions in the above table do the following:
Count – returns the number of items in the Dictionary.
Remove – removes a given key from the Dictionary.
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
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 use a normal for loop and the index
Dim i As Long
For i = 0 To dict.Count - 1
Debug.Print dict.Keys(i), dict.Items(i)
Next i
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.
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.
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.
Using a Dictionary, we can easily check if the team already exists. We can also update the value for this team.
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)
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
If dict.Exists(sTeam2) Then
' If exists add to total
dict(sTeam2) = dict(sTeam2) lGoals2
Else
' if doesn't exist then add
dict(sTeam2) = lGoals2
End If
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
Teams ordered by number of goals scored
When To Use The Dictionary
So when should you use the VBA Dictionary? When you have a task where
You have a list of unique items e.g. countries, invoice numbers, customer name and addresses, project ids, product names etc.
You need to retrieve the value of a unique item.
Key/Values of Countries and Land area in Km2
What’s Next?
If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.
If you are serious about mastering VBA then you may want to check out The Excel VBA Handbook
Get the Free eBook
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.
The post Excel VBA Dictionary – A Complete Guide appeared first on Excel Macro Mastery.
