Excel VBA: Practical Guide to Using Dictionary

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video.

Excel VBA Dictionary is an awesome tool that can help you achieve so many things.

In this blog, we will look at what dictionaries are, how to set them up and how to use them practically.

We will cover 4 practical use-cases.

1. Find Unique Values from a List

2. Perform Sum & Count on Grouped Values

3. Copy & Paste Data Dynamically

4. Perform VlookUps

What is a Dictionary?

A dictionary is a VBA object that holds al ist of data in pairs of two: A Key and an item.

In simple terms, an item is the value we want to store and a key is the name or reference we want to assign it.

And a VBA dictionary can store multiple such key-item pairs.

This is similar to a real-life dictionary which is basically a list of words as keys and their meanings as items.

How to Use a Dictionary?

We are going to just focus on the parts that we need to get the job done. So, in this section, we will learn how to

create a dictionary,
populate it with data,
retrieve a value
check whether a key exists
write back the contents of a dictionary onto a worksheet.

A dictionary is a VBA object that is available through the Microsoft Scripting Runtime library. We will create the dictionary using late binding, where we create the object instead of referencing the library.

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

Next, lets fill our dictionary with some values.

To a add data to a dictionary, we will use the add method.

We need to add the key and the item. Since, we will be looking at a cricket related dataset later on, we will add the Player name as Key and total runs scored as Item.

dic.Add Key:="SR Tendulkar", Item:=15921
dic.Add Key:="RT Ponting", Item:=13378
dic.Add Key:="JH Kallis", Item:=13289

Some points to note here. The Key can be any data type such as number or text. The item can be any data type, an array, a class object or even another dictionary. For this blog though, we will just stick to regular data types.

Next, look at how to retrieve a particular value or item.

Let’s say we want to print out the total runs scored by Ponting. We will call the dictionary object and feed in the key as the parameter.

Debug.Print dic("SR Tendulkar")

Next, let’s look at how to check if a player is within our dictionary or not. If they are not, we will add them to our dictionary.

For this, we will use the Exists method of the dictionary object.

Let’s add in a new player.

If dic.exists("KC Sangakkara") Then
    MsgBox "Player Already Exisits"
    Else
    dic.Add Key:="KC Sangakkara", Item:=12400
End If

One point to note here is that the default .Exists method is case sensitive. We can program it to ignore Case Senstivity by adding the lines dict.CompareMode = vbTextCompare right after we create the dictionary.

And finally, let’s print out the contents of the dictionary onto our worksheet.

For this we need to understand two things.

The index for a dictionary starts from 0. That is, if you want to refer to the first key-item in the dictionary, it will have the position 0.

Debug.Print dic.keys()(0), dic.items()(0)

And for the second point, in order retrieve all the data from a dictionary we need to loop over it. The loop will start from the first index which is 0 to the last index which is the count of all the key-item pairs in the dictionary minus 1.

Dim i As Long
For i = 0 To dic.Count - 1
    Debug.Print dic.keys()(i), dic.items()(i)
Next i

Let’s see the entire sub as covered in the video.

Sub DictionaryBasics()

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
'dic.CompareMode = vbTextCompare

dic.Add Key:="SR Tendulkar", Item:=15921
dic.Add Key:="RT Ponting", Item:=13378
dic.Add Key:="JH Kallis", Item:=13289

'Debug.Print dic("SR Tendulkar")

'If dic.exists("SR Tendulkar") Then
'    MsgBox "Player Already Exisits"
'    Else
'    dic.Add Key:="SR Tendulkar", Item:=15921
'End If

If dic.exists("KC Sangakkara") Then
    MsgBox "Player Already Exisits"
    Else
    dic.Add Key:="KC Sangakkara", Item:=12400
End If

'Debug.Print dic("KC Sangakkara")
'Debug.Print dic.keys()(0), dic.items()(0)
Dim i As Long
For i = 0 To dic.Count - 1
    Debug.Print dic.keys()(i), dic.items()(i)
Next i

End Sub

Initial Setup

The main dataset consists of highest scoring cricket players in Test Matches.

Above we have the main dataset which is the sheet: RunsBy. We also, have Output sheet which is blank. And, a Country sheet as well, which has player name in col A and country in col B. It will only be used in the vlookup example.

Example 1: Find Unique Values from a List

In Col L, we can see that the Country Names are repeating in the last column. Let’s grab a unique list of countries within this column.

Create a dictionary.

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

We will create a For Loop to loop over the dataset.

In the loop, once we read the Country name from the column, feed it into our dictionary.

Here, country is the Key which needs to be unique. So, put in an If statement to only add the country to the dictionary, if it doesn’t exist already. Add the Country which is the Key. We don’t need the Item in this case. So, we will just leave it as 0.

Dim i As Long
For i = 2 To lrowRunsBy
    sCountry = wsRunsBy.Range("l" & i).Value
    If Not dicRunsBy.exists(sCountry) Then
        dicRunsBy.Add Key:=sCountry, Item:=0
    End If
Next i

Now, once the loop is run, the dictionary will get populated with the unique Country names. Paste the results into our output sheet. Create the loop for printing out the dictionary keys. And we just want to paste the Keys in column A in the second row onwards which is i + 2 because i starts from 0.

For i = 0 To dicRunsBy.Count - 1
    wsOutput.Range("a" & i + 2).Value = dicRunsBy.keys()(i)
Next i

That’s it. Running the sub will give the unique Country names in Col A of the Output sheet.

Full sub.

Sub UniqueValues()

Dim wsRunsBy As Worksheet, wsOutput As Worksheet
Set wsRunsBy = ThisWorkbook.Sheets("RunsBy")
Set wsOutput = ThisWorkbook.Sheets("Output")
wsOutput.Range("a2:c" & wsOutput.Rows.Count).Clear

Dim lrowRunsBy As Long
lrowRunsBy = wsRunsBy.Range("a1").CurrentRegion.Rows.Count

Dim sCountry As String

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

Dim i As Long
For i = 2 To lrowRunsBy
    sCountry = wsRunsBy.Range("l" & i).Value
    If Not dicRunsBy.exists(sCountry) Then
        dicRunsBy.Add Key:=sCountry, Item:=0
    End If
Next i

For i = 0 To dicRunsBy.Count - 1
    wsOutput.Range("a" & i + 2).Value = dicRunsBy.keys()(i)
Next i

wsOutput.Columns("A:c").AutoFit
End Sub

Example 2: Perform Sum & Count on Grouped Values

This example is similar to the previous. We can create another layer on top of that.

Have a look at our data set. Previously, we grabbed the unique values in a column which is the country name. While we are looping through the dataset, we can even count the number of occurrences of these unique values or calculate the sum of another column for each unique value. E.g. Say, we want to find the Total Runs scored per country. We can perform other operations as well such as Average, Max, Min etc. But, for this video, we will stick to Count and Sum.

Use the same sub as previous.

Change the If statement to an If Else. First part will be If the country name Exists and then we will add an Else condition. Let’s address the Else part first. This is where the Country name doesn’t exist. If it doesn’t, we will add the country name to dictionary.

So, in the last example, our Item was 0, which was just an arbitrary value because we weren’t using it. This time, we will use it to count each occurrence of the unique key or country. So, the first time that we encounter a country name, we will assign it a count of one.

This brings us to the upper condition, where the country name already exists in the dictionary.

In this case, we don’t want to add the country to the dictionary. We couldn’t even if we wanted to. So, what we will do here is increase the existing count by one.

Dim i As Long
For i = 2 To lrowRunsBy
    sCountry = wsRunsBy.Range("L" & i).Value
    lRuns = wsRunsBy.Range("F" & i).Value
    If dicRunsBy.exists(sCountry) Then
        dicRunsBy(sCountry) = dicRunsBy(sCountry) + 1

        Else
        dicRunsBy.Add Key:=sCountry, Item:=1

    End If
Next i

Output the values.

Previously, we just pasted out the Keys or the country name. This time, paste the Count which is the Item, in the adjacent cell.

For i = 0 To dicRunsBy.Count - 1
    wsOutput.Range("a" & i + 2).Value = dicRunsBy.keys()(i)
    wsOutput.Range("b" & i + 2).Value = dicRunsBy.items()(i)
Next i

That’s it for Count. This will display Unique Country names in Col A and Count of Country names in Col B. Full sub further below.

Let’s look at how to sum the runs in col F.

In the For Loop, for the Else statement, if the Country doesn’t exist, we will add the country as key and this time, we will add the Runs as Item.

And for the main if condition, if the country already exists, then we will simply add the Runs from the current cell to the previous value of Runs stored for that country.

Dim i As Long
For i = 2 To lrowRunsBy
    sCountry = wsRunsBy.Range("L" & i).Value
    lRuns = wsRunsBy.Range("F" & i).Value
    If dicRunsBy.exists(sCountry) Then
        dicRunsBy(sCountry) = dicRunsBy(sCountry) + lRuns
        Else

        dicRunsBy.Add Key:=sCountry, Item:=lRuns
    End If
Next i

That’s it. rest of the code is same as the Count version.

Full sub for both versions below.

Sub sumCount()

Dim wsRunsBy As Worksheet, wsOutput As Worksheet
Set wsRunsBy = ThisWorkbook.Sheets("RunsBy")
Set wsOutput = ThisWorkbook.Sheets("Output")
wsOutput.Range("a2:c" & wsOutput.Rows.Count).Clear

Dim lrowRunsBy As Long
lrowRunsBy = wsRunsBy.Range("a1").CurrentRegion.Rows.Count

Dim sCountry As String, lRuns As Long

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

Dim i As Long
For i = 2 To lrowRunsBy
    sCountry = wsRunsBy.Range("L" & i).Value
    lRuns = wsRunsBy.Range("F" & i).Value
    If dicRunsBy.exists(sCountry) Then
        'dicRunsBy(sCountry) = dicRunsBy(sCountry) + 1
        dicRunsBy(sCountry) = dicRunsBy(sCountry) + lRuns
        Else
        'dicRunsBy.Add Key:=sCountry, Item:=1
        dicRunsBy.Add Key:=sCountry, Item:=lRuns
    End If
Next i

For i = 0 To dicRunsBy.Count - 1
    wsOutput.Range("a" & i + 2).Value = dicRunsBy.keys()(i)
    wsOutput.Range("b" & i + 2).Value = dicRunsBy.items()(i)
Next i

wsOutput.Columns("A:c").AutoFit
End Sub

Example 3: Copy & Paste Data Dynamically

So, far we have been using the Exists method as footnote. In this example, it will be the main feature.

Here, we will look at how to dynamically copy data from one worksheet to another.

Lets look at our dataset. We just want to copy the columns Country, Player and Total Runs into the Output sheet.

You will notice that the column are not in sequence. And, there are columns in the Output sheet that are not in the Data sheet. So, we don’t want to copy anything into this column.

Copying this way using the traditional method of defining the ranges using current region is almost impossible. Here’s where dictionary shines and is must know technique.

Create a dictionary.

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

Loop across the column headers from left to right in the RunsBy dataset. Grab the column header. and feed it into our dictionary. For the item, we will feed in the current column position, which we will be needing later on for copying and pasting.

Dim i As Long
For i = 1 To lcolRunsBy
    sHeader = wsRunsBy.Cells(1, i).Value
    dicHeader.Add Key:=sHeader, Item:=i
Next i

Next, grab the last column of the output sheet. Now, this time, we will loop from left to right, across the column headers of the output sheet and first, check whether each column header exists in the dictionary consisting of column headers from the data sheet.

And if the column header or key exists, we will copy the data from that column using the column position which is stored as the Item or Value of that key in the dictionary.

For the copy method, we will define the range using cells. So, we need to state the first cell which starts in the 2^nd row and column number form the item. And the final cell, which is in the last row and column number from the item.

For i = 1 To lcolOutput
    sHeader = wsOutput.Cells(1, i).Value
    If dicHeader.exists(sHeader) Then
        wsRunsBy.Range(wsRunsBy.Cells(2, dicHeader(sHeader)), wsRunsBy.Cells(lrowRunsBy, dicHeader(sHeader))).Copy _
            wsOutput.Cells(2, i)
    End If
Next i

And that’s it. The right data will get pasted into the right columns.

Full sub here.

Sub CheckIfExists()

Dim wsRunsBy As Worksheet, wsOutput As Worksheet
Set wsRunsBy = ThisWorkbook.Sheets("RunsBy")
Set wsOutput = ThisWorkbook.Sheets("Output")
wsOutput.Range("a2:d" & wsOutput.Rows.Count).Clear

Dim lrowRunsBy As Long
lrowRunsBy = wsRunsBy.Range("a1").CurrentRegion.Rows.Count
Dim lcolRunsBy As Long
lcolRunsBy = wsRunsBy.Range("a1").CurrentRegion.Columns.Count

Dim sHeader As String

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

Dim i As Long
For i = 1 To lcolRunsBy
    sHeader = wsRunsBy.Cells(1, i).Value
    dicHeader.Add Key:=sHeader, Item:=i
Next i

Dim lcolOutput As Long
lcolOutput = wsOutput.Range("a1").CurrentRegion.Columns.Count

For i = 1 To lcolOutput
    sHeader = wsOutput.Cells(1, i).Value
    If dicHeader.exists(sHeader) Then
        wsRunsBy.Range(wsRunsBy.Cells(2, dicHeader(sHeader)), wsRunsBy.Cells(lrowRunsBy, dicHeader(sHeader))).Copy _
            wsOutput.Cells(2, i)
    End If
Next i

wsOutput.Columns("A:D").AutoFit
End Sub

Example 4: Perform VlookUps

Let’s move on to the last use case, which is also my favorite use for a dictionary i.e. looking up values.

So, this time, we are going to tweak the dataset a bit. Instead of having the Country column within the main dataset, we will delete this column off. And then, come on over to the sheet Country. Here, we have a map of each player and the respective country they belong to. If we had this scenario in our corporate reports, we would just vlookup the value of the player from the main dataset against this table and grab the country.

We can do this through dictionaries too.

Lets create our dictionary. This time around, we are going to loop over the Country sheet and feed in the values into the dictionary: with player as key and country as item.

So, create the dictionary.

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

Create the loop. Grab the player name from col A and country from col B.

We are doing this vlookup style, where the column with the matching value is the first column and corresponding value is in a column on the right. But, in reality, the corresponding value could be on the left or right, doesn’t matter. Dictionary is very flexible.

Back to the loop. If the player doesn’t exist in the dictionary, feed in the player as key and country as item.

Dim i As Long
For i = 2 To lrowCountry
    sPlayer = wsCountry.Range("A" & i).Value
    sCountry = wsCountry.Range("B" & i).Value
    If Not dicCountryMap.exists(sPlayer) Then
        dicCountryMap.Add Key:=sPlayer, Item:=sCountry
    End If
Next i

Now, reference the main dataset. We are going to loop over it, and for each player name in col A, we will check whether a key exists in the dictionary with the same player name. If it does, we will grab the item or country name and place it in Col L. If it doesn’t we will fill the text Unknown in col L.

For i = 2 To lrowRunsBy
    sPlayer = wsRunsBy.Range("A" & i).Value
    If dicCountryMap.exists(sPlayer) Then
        wsRunsBy.Range("L" & i).Value = dicCountryMap(sPlayer)
        Else
        wsRunsBy.Range("L" & i).Value = "Unknown"
    End If
Next i

Full sub here.

Sub DoLookUps()

Dim wsRunsBy As Worksheet, wsOutput As Worksheet
Set wsRunsBy = ThisWorkbook.Sheets("RunsBy")
Set wsOutput = ThisWorkbook.Sheets("Output")
wsOutput.Range("a2:c" & wsOutput.Rows.Count).Clear

Dim wsCountry As Worksheet
Set wsCountry = ThisWorkbook.Sheets("Country")

Dim lrowCountry As Long
lrowCountry = wsCountry.Range("a1").CurrentRegion.Rows.Count

Dim lrowRunsBy As Long
lrowRunsBy = wsRunsBy.Range("a1").CurrentRegion.Rows.Count

Dim sCountry As String, sPlayer As String

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

Dim i As Long
For i = 2 To lrowCountry
    sPlayer = wsCountry.Range("A" & i).Value
    sCountry = wsCountry.Range("B" & i).Value
    If Not dicCountryMap.exists(sPlayer) Then
        dicCountryMap.Add Key:=sPlayer, Item:=sCountry
    End If
Next i

For i = 2 To lrowRunsBy
    sPlayer = wsRunsBy.Range("A" & i).Value
    If dicCountryMap.exists(sPlayer) Then
        wsRunsBy.Range("L" & i).Value = dicCountryMap(sPlayer)
        Else
        wsRunsBy.Range("L" & i).Value = "Unknown"
    End If
Next i

End Sub

Home

What is a Dictionary?

How to Use a Dictionary?

Initial Setup

Example 1: Find Unique Values from a List

Example 2: Perform Sum & Count on Grouped Values

Example 3: Copy & Paste Data Dynamically

Example 4: Perform VlookUps

Related Posts

Use Functions With Dynamic Arrays and Dictionaries

Select Dataset Except For Top Row

Download Attachments From Outlook Emails