“The greatest masterpiece in literature is only a dictionary out of order.” – Jean Cocteau
A Quick Guide to the VBA Dictionary
| Function | Example |
|---|---|
| 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 key | Value = dict(Key) e.g. appleCount = dict("Apples") |
| Check if key exists | dict.Exists(Key) e.g. If dict.Exists("Apples") Then |
| Remove item | dict.Remove Key e.g. dict.Remove "Apples" |
| Remove all items | dict.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 |
| Go through all items (for loop - early and late binding) | 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 items | dict.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 |
What is the VBA Dictionary?
In VBA we use Arraysand Collectionsto 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 Dictionarydict.Add Key:="Apple", Item:=5' Add to Collectioncoll.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 DictionaryTotal = dict("Apple")' Get value from CollectionTotal = 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 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.
Download the Source Code
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
- 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.
' https://excelmacromastery.com/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.DictionarySet 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 ObjectSet 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.)
While Microsoft recommends that you use early binding in almost all cases I would differ. A good rule of thumb is to use early binding when developing the code so that you have access to the Intellisense. Use late binding when distributing the code to other users to prevent various library conflict errors occurring.
Adding Items to the Dictionary
| Function | Params | Example |
|---|---|---|
| Add | Key, Item | dict.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:=45dict.Add "Apple", 66dict.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 alreadydict.Add Key:="Orange", Item:=75
Assigning a Value
| Operation | Format | Example |
|---|---|---|
| Assign | Dictionary(Key) = Item | dict("Oranges") = 60 |
We can change the value of a key using the following code
dict("Orange") = 75Assigning 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 100dict("Orange") = 100
Don’t forget that you can download all the VBA code used in this post from the top or bottom of the post.
Checking if a Key Exists
| Function | Parameters | Example |
|---|---|---|
| Exists | Key | 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 dictionaryIf 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 CodePublic CustomerID As StringPublic Amount As LongPublic Items As Long
' Create a new clsCustomer objectSet oCust = New clsCustomer' Set the valuesoCust.CustomerID = rg.Cells(i, 1).ValueoCust.Amount = rg.Cells(i, 2).ValueoCust.Items = rg.Cells(i, 3).Value' Add the new clsCustomer object to the dictionarydict.Add oCust.CustomerID, oCust
You can see that by using the Class Module we can store as many fields as we want. Examples 2 and 3 at the bottom of the post show how to use a class module with a Dictionary
Other useful functions
| Function | Parameters | Example |
|---|---|---|
| Count | N/A | dict.Count |
| Remove | Key | dict.Remove "Apples" |
| RemoveAll | N/A | dict.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
' https://excelmacromastery.com/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.CountEnd Sub
Remember that you can download all the code examples from the post. Just go to the download section at the top.
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.
© 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.
' https://excelmacromastery.com/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 = NothingEnd Sub
This time we use vbTextCompare which means that the case does not matter
' https://excelmacromastery.com/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 = NothingEnd 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.
' https://excelmacromastery.com/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.
' https://excelmacromastery.com/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.
' https://excelmacromastery.com/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 VariantFor 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(Update Feb 2020: In Office 365 this now works with both versions):
Dim i As LongFor i = 0 To dict.Count - 1 Debug.Print dict.Keys(i), dict.Items(i)Next i
This method works with both Early and Late binding:
Dim i As LongFor 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
' https://excelmacromastery.com/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
' https://excelmacromastery.com/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 SubPublic 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) NextEnd Sub
Sorting by values
To sort the dictionary by the values you can use the SortDictionaryByValue function below.
' https://excelmacromastery.com/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 Functioneh: If Err.Number = 450 Then Err.Raise vbObjectError + 100, "SortDictionaryByValue" _ , "Cannot sort the dictionary if the value is an object" End IfEnd Function
The code below shows you how to use SortDictionaryByValue
' https://excelmacromastery.com/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 SubPublic 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", 4If 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.Dictionarydict.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..." errordict.Add "Apple", 4
Resolution: Use the New keyword when creating the Dictionary
Dim dict As New Scripting.Dictionary
Or
Dim dict As Scripting.DictionarySet 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).
' https://excelmacromastery.com/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) NextEnd Sub
You can use it like this
Dim dict As Scripting.DictionarySet dict = New Scripting.Dictionary' Add items to Dictionary here' Print the contents of the Dictionary to the Immediate WindowPrintContents 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.
Remember that you can download all the code examples from the post. Just go to the download section at the top.
Copying the Dictionary to an Array
As we know the dictionary is made up of Key and Value pairs. The dictionary has a Keys property which is an array of all the keys and an Items property which is an array of all the items(i.e. values).
As both of these properties are arrays, we can write them directly to a worksheet as we will see in the next section.
If we want to copy either the Keys or Items array to a new array then we can do it very easily like this:
Dim arr As Variantarr = dict.Keys
The following example copies the Keys and Items arrays to new arrays. Then the contents of the new arrays are printed to the Immediate Window:
Sub DictionaryToArray() ' Create dictionary and add entries Dim dict As New Dictionary dict.Add "France", 56 dict.Add "USA", 23 dict.Add "Australia", 34 ' Declare variant to use as array Dim arr As Variant ' Copy keys to array arr = dict.Keys ' Print array to Immediate Window(Ctrl + G to View) Call PrintArrayToImmediate(arr, "Keys:") ' Copy items to array arr = dict.Items ' Print array to Immediate Window(Ctrl + G to View) Call PrintArrayToImmediate(arr, "Items:")End Sub' Prints an array to the Immediate Window(Ctrl + G to View)Sub PrintArrayToImmediate(arr As Variant, headerText As String) Debug.Print vbNewLine & headerText Dim entry As Variant For Each entry In arr Debug.Print entry Next End Sub
When you run the code you wil get the following output:
Note that you can only copy the Items array when it contains basic data types like string, long, date, double etc. If the items are objects then you can not copy them to an array. You’ll need to read through the dictionary using a loop instead.
Writing the Dictionary to the Worksheet
We can write the Dictionary keys or items to the worksheet in one line of code.
When you write out the keys or items they will be written to a row. If you want to write them to a column you can use the WorksheetFunction.Transpose function.
The code below shows examples of how to write the Dictionary to a worksheet:
Sub DictionaryToWorksheet() Dim dict As New Dictionary dict.Add "France", 56 dict.Add "USA", 23 dict.Add "Australia", 34 Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Sheet1") ' Write keys to range A1:C1 sh.Range("A1:C1").Value = dict.Keys ' Write items to range A2:C2 sh.Range("A2:C2").Value = dict.Items ' Write keys to range E1:E3 sh.Range("E1:E3").Value = WorksheetFunction.Transpose(dict.Keys) ' Write items to range F1:F3 sh.Range("F1:F3").Value = WorksheetFunction.Transpose(dict.Items)End Sub
Useful Dictionary Examples
The easiest way to see the benefits of the Dictionary is to see some real-world examples of it’s use. So in this section we are going to look at some examples. You can get workbooks and code for these examples by entering your email below:
Example 1 – Summing Single Values
Let’s have a look at a real-world example of using a dictionary. Our data for this example is the World Cup Final matchesfrom 2014.
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.
' https://excelmacromastery.com/vba-dictionary' Reads the World Cup data from the 2014 Worksheet' View the results in the Immediate Window(Ctrl + G)Sub GetTotals() ' Get worksheet Dim wk As Worksheet Set wk = ThisWorkbook.Worksheets("2014") ' Get range for all the matches Dim rg As Range Set rg = wk.Range("A1").CurrentRegion Dim Team1 As String, Team2 As String Dim Goals1 As Long, Goals2 As Long Dim i As Long For i = 2 To rg.Rows.Count ' read the data from each match Team1 = rg.Cells(i, 5).Value Team2 = rg.Cells(i, 9).Value Goals1 = rg.Cells(i, 6).Value Goals2 = rg.Cells(i, 7).Value ' Print each teams/goals to Immediate Window(Ctrl + G) Debug.Print Team1, Team2, Goals1, Goals2 Next i 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.
We can use the following line to add goals to the current team:
dict(Team1) = dict(Team1) + Goals1
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(Team1) Then ' If exists add to total dict(Team) = dict(Team) + Goals1Else ' if doesn't exist then add dict(Team) = Goals1End If
We write out the values from the Dictionary to the worksheet as follows:
' Write the data from the dictionary to the worksheet' https://excelmacromastery.com/vba-dictionaryPrivate Sub WriteDictionary(dict As Scripting.Dictionary _ , shReport As Worksheet) ' Write the keys shReport.Range("A1").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.Keys) ' Write the items shReport.Range("B1").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.Items) 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:
' Sort the data on the worksheet' https://excelmacromastery.com/vba-dictionaryPublic Sub SortByScore(shReport As Worksheet _ , Optional sortOrder As XlSortOrder = xlDescending) Dim rg As Range Set rg = shReport.Range("A1").CurrentRegion rg.Sort rg.Columns("B"), sortOrder End Sub
Our final GetTotals sub looks like this:
' https://excelmacromastery.com/vba-dictionarySub GetTotalsFinal() ' 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 team1 As String, team2 As String Dim goals1 As Long, goals2 As Long Dim i As Long ' Read through the range of data For i = 2 To rgMatches.Rows.Count ' read the data to variables team1 = rgMatches.Cells(i, 5).Value team2 = rgMatches.Cells(i, 9).Value goals1 = rgMatches.Cells(i, 6).Value goals2 = rgMatches.Cells(i, 7).Value ' Add the totals for each team to the dictionary. ' If the team doesn't exist it will be automatically added dict(team1) = dict(team1) + goals1 dict(team2) = dict(team2) + goals2 Next i ' Get the report worksheet Dim shReport As Worksheet Set shReport = ThisWorkbook.Worksheets("2014 Report") ' Write the teams and scores to the worksheet WriteDictionary dict, shReport ' Sort the range ' Change to xlAscending to reverse the order SortByScore shReport, xlDescending ' Clean up Set dict = Nothing shReport.Activate End Sub
When you run this code you will get the following 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:
- WriteToImmediate prints the contents of the dictionary to the Immediate Window.
- WriteToWorksheet writes the contents of the dictionary to the worksheet called Output.
To run this example:
- Create a worksheet called Customers.
- Add the above data to the worksheet starting at cell A1.
- Create a worksheet called Output and leave it blank.
- Go to the Visual Basic Editor(Alt + F11).
- Select Tools->Reference and then check “Microsoft Scripting Runtime” from the list.
- Create a new class module and add the first piece of code from below.
- Create a new standard module and add the second piece of code from below.
- Press F5 to run and select Main from the menu.
- Check the ImmediateWindow(Ctrl + G) and the Output worksheet to see the results.
' clsCustomer Class Module CodePublic CustomerID As StringPublic Amount As LongPublic Items As Long
' Standard module Code' https://excelmacromastery.com/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 SubPrivate 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 = dictEnd Function' Write the Dictionary contents to the Immediate Window(Ctrl + G)' https://excelmacromastery.com/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' https://excelmacromastery.com/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:
Note: If you run the “Example 2” code on data with multiple copies of the CustomerID, it will give the “Key already exists error”.
' clsCustomer Class Module CodePublic CustomerID As StringPublic Amount As LongPublic Items As Long
' Read from worksheet: CustomerSum' Write to worksheet: CustomerRepSum' https://excelmacromastery.com/vba-dictionarySub MainSum() Dim dict As Dictionary ' Read the data to the dictionary Set dict = ReadMultiItemsSum ' Write the Dictionary contents to the Immediate Window(Ctrl + G) WriteToImmediate dict ' Write the Dictionary contents to a worksheet WriteToWorksheet dict, ThisWorkbook.Worksheets("CustomerRepSum") End Sub' Read multiple items but this time sums the items' https://excelmacromastery.com/Private Function ReadMultiItemsSum() As Dictionary ' Declare and Create the Dictionary Dim dict As New Dictionary ' Get the worksheet Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("CustomerSum") ' 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.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 ReadMultiItemsSum = dictEnd Function' Write the Dictionary contents to the Immediate Window(Ctrl + G)' https://excelmacromastery.com/vba-dictionaryPrivate 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 key, .Amount, .Items End With Next key End Sub' Write the Dictionary contents to a worksheet' https://excelmacromastery.com/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 = key sh.Cells(row, 2).Value = .Amount sh.Cells(row, 3).Value = .Items row = row + 1 End With Next key End Sub
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?
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.)
FAQs
How to use dictionary in VBA Excel? ›
Working with VBA Dictionaries
Step 1: Go to Tools > References. Step 2: Scroll down, select the 'Microsoft Scripting Runtime' option, then click “OK.” Now, we can access the VBA Dictionary with the Scripting Library.
Using the VBA Dictionary
In the menu in the VBE, select Tools > References. Scroll down to the Microsoft Scripting Runtime and make sure that the box next to it is ticked. Click OK. A reference will now be added to your VBA project, and you can then define a new dictionary object by referring to it directly.
The . exists() function checks if a key exists. You are searching for an item so you will need to use a loop and check each item in the dictionary.
What is the difference between dictionary and collection in VBA? ›You can assign a Key to an Item when you add the Item to the Collection, but you cannot retrieve the Key associated with an Item nor can you determine (directly) whether a key exists in a Collection. Dictionaries are much friendly and open with their keys. Dictionaries are also considerably faster than Collections.
What is a scripting dictionary in Excel VBA? ›The scripting dictionary is a way to store unique items via a key and item (Keys and Items are terms in the dictionary. It is a fantastic tool to store data based on a unique key. It is powerful in that it the keys can be used to store and consolidate data.
How do I convert Excel data to a dictionary? ›- First initialize the dictionary - New Dictionary(Of String,List(Of String))
- Use a loop to loop through each column Dt.Columns and change the type argument to System.Data.DataColumn.
- Inside use use assign.
Checking if key exists using the get() method
The get() method is a dictionary method that returns the value of the associated key. If the key is not present it returns either a default value (if passed) or it returns None. Using this method we can pass a key and check if a key exists in the python dictionary.
You can check if a key exists in dictionary in Python using the keys() method of the dictionary class. The keys() method returns a view object of keys (something similar to a collection) in which you can search the required key using the in operator we saw earlier.
How do I know if a key is already in the dictionary? ›- has_key()
- if-in statement/in Operator.
- get()
- keys()
- Handling 'KeyError' Exception.
There's some overlap here with a database's schema, but generally speaking a schema defines the structure of the database and how tables and their fields fit together, while a data dictionary provides contextual information about that data.
What is the difference between list array and dictionary? ›
A list refers to a collection of various index value pairs like that in the case of an array in C++. A dictionary refers to a hashed structure of various pairs of keys and values.
What is difference between string list and dictionary? ›A string is a sequence of characters. A list a sequence of values which can be characters, integers or even another list (referred to as a nested list). A dictionary is a more general version of a list and is made up a set of keys and values where there is a mapping between a given key and its corresponding value.
What type of dictionary keys are used in VBA? ›By default, the VBA dictionary keys are case-sensitive. The most commonly used data types used for dictionary keys are strings and numbers, but a key can be any input except an array.
Which command is used to create a dictionary? ›The dict() function creates a dictionary. A dictionary is a collection which is unordered, changeable and indexed.
How do you create a dictionary structure? ›- Understand What You Have. The first step that you will need to accomplish is getting to know your content. ...
- Interview Your Users. ...
- Create Categories. ...
- Label Your Categories. ...
- Create the Navigation. ...
- Test and Iterate.
Released in 1996, it is written in C++ and became an object oriented language. VBA 5.0 was launched in 1997 along with all of MS Office 97 products.
What are the two macro scripting language in Excel? ›Macro and VBA
You can record and run macros with either Excel commands or from Excel VBA. VBA stands for Visual Basic for Applications and is a simple programming language that is available through Excel Visual Basic Editor (VBE), which is available from the DEVELOPER tab on the Ribbon.
VBA is a scripting language and a subset of Visual Basic 6.0, which means it's almost as good, but half the calories. The code actually gets compiled to an intermediate packed format, p-code, and both plaintext and compiled code are included with the document.
How do I auto generate words in Excel? ›- Click File > Options.
- Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn this option on or off.
- Step 1: Pull together your terms.
- Step 2: Identify important information about each term.
- Step 3: Document your dictionary.
- Step 4: Revisit and revise your data dictionary.
How to get the value in a dictionary without knowing the key? ›
Use dict.get() to get the default value for non-existent keys. You can use the get() method of the dictionary ( dict ) to get any default value without an error if the key does not exist.
How do I get a random key from a dictionary? ›Check out random. sample() which will select and return a random element from an list. You can get a list of dictionary keys with dict. keys() and a list of dictionary values with dict.
How do you access a value in a dictionary? ›Values in a Python dictionary can be accessed by placing the key within square brackets next to the dictionary. Values can be written by placing key within square brackets next to the dictionary and using the assignment operator ( = ). If the key already exists, the old value will be overwritten.
Can you find the key from the value dictionary? ›To get the key from the value in the dictionary, use list comprehension and the items() method. For more information on list comprehensions and for loops for dictionaries, see the following articles. The following sample code demonstrates how to get a list of keys associated with a specified value.
How do you get a key from a dictionary with the value? ›We can get the value of a specified key from a dictionary by using the get() method of the dictionary without throwing an error, if the key does not exist. As the first argument, specify the key. If the key exists, the corresponding value is returned; otherwise, None is returned.
How do you pop a key-value in a dictionary? ›- Using del. The del keyword deletes a key: value pair from a dictionary. ...
- Using popitem() The in-built function popitem() deletes the last key: value pair in a dictionary. ...
- Using pop()
Keys in a dictionary can only be used once. If it is used more than once, as you saw earlier, it'll simply replace the value. 00:19 A key must be immutable—that is, unable to be changed. These are things like integers, floats, strings, Booleans, functions.
Are keys in a dictionary unique? ›However, a key can not be a mutable data type, for example, a list. Keys are unique within a Dictionary and can not be duplicated inside a Dictionary. If it is used more than once, subsequent entries will overwrite the previous value.
What are the two main types of data dictionary? ›There are two types of data dictionaries: active and passive.
What are the three types of data schema? ›While the term schema is broadly used, it is commonly referring to three different schema types—a conceptual database schema, a logical database schema, and a physical database schema.
What is the advantage of using a schema or data dictionary? ›
Helps to understand the overall database design, structure, relationships, and data flow. Facilitates building a common vocabulary and hence shared understanding amongst data users. Helps detect errors and anomalies in data. Enables crowdsourcing data quality and data integrity checks.
What's a major advantage of using dictionaries over lists? ›It is more efficient to use dictionaries for the lookup of elements as it is faster than a list and takes less time to traverse. Moreover, lists keep the order of the elements while dictionary does not. So, it is wise to use a list data structure when you are concerned with the order of the data elements.
Should I use array or dictionary? ›Arraylists just store a set of objects (that can be accessed randomly). Dictionaries store pairs of objects. This makes array/lists more suitable when you have a group of objects in a set (prime numbers, colors, students, etc.). Dictionaries are better suited for showing relationships between a pair of objects.
Why is a dictionary better than an array? ›Anyway, an Array generally provides random access to a sequential set of data, whereas a Dictionary is used to map between a set of keys and a set of values (so, very useful in mapping random "pairs" of information).
When would you use a list vs dictionary? ›Lists are used to store the data, which should be ordered and sequential. On the other hand, dictionary is used to store large amounts of data for easy and quick access. List is ordered and mutable, whereas dictionaries are unordered and mutable.
Why is a dictionary faster than a list? ›The reason is because a dictionary is a lookup, while a list is an iteration. Dictionary uses a hash lookup, while your list requires walking through the list until it finds the result from beginning to the result each time.
What is the key difference between a list and a tuple? ›The primary difference between tuples and lists is that tuples are immutable as opposed to lists which are mutable. Therefore, it is possible to change a list but not a tuple. The contents of a tuple cannot change once they have been created in Python due to the immutability of tuples.
How to access dictionary values using keys? ›- Get value from dictionary with dict[key] ( KeyError for non-existent keys)
- Use dict.get() to get the default value for non-existent keys.
Adding a reference to your VBA Project
In the VBE Window, click on the Tools menu and then click References… Scroll down through the list of references to find the one you want to use. In this case, the Microsoft Word 16.0 Object Library. Click OK.
- In the VBA Editor, click on Tools | References. ...
- Scroll down the list of available references until you find Microsoft Word 16.0 Object Library. ...
- Once selected, click on OK to save the selection and close the dialog box. ...
- Create a Sub procedure to initiate Word from within Excel.
What is the difference between module and class module in Excel VBA? ›
In languages such as C# and Java, classes are used to create objects. Class Modules are the VBA equivalent of these classes. The major difference is that VBA Class Modules have a very limited type of Inheritance* compared to classes in the other languages.
What is the difference between module and class in VBA? ›A class is a type. You can use this type like any other type ( String , Integer , Date , FileInfo ...) to declare variables, parameters, properties, and function return types. Whereas modules are static. I.e. Data stored in a module exists exactly once.
What is the difference between module and class module in VBA? ›The main difference between classes and modules is that classes can be instantiated as objects while standard modules cannot.
How to extract key values from dictionary? ›Method 1 : Using List. Step 1: Convert dictionary keys and values into lists. Step 2: Find the matching index from value list. Step 3: Use the index to find the appropriate key from key list.
What are the methods used to retrieve all the items in the dictionary? ›We can use the values() method in Python to retrieve all values from a dictionary. Python's built-in values() method returns a view object that represents a list of dictionaries containing every value.
How to convert dictionary keys and values to list? ›To get dictionary keys as a list in Python use the dict. keys() which returns the keys in the form of dict_keys() and use this as an argument to the list() . The list() function takes the dict_keys as an argument and converts it to a list, this will return all keys of the dictionary in the form of a list.
How do you check if a string contains a word in Excel VBA? ›The VBA Instr Function checks if a string of text is found in another string of text. It returns 0 if the text is not found. Otherwise it returns the character position where the text is found. The Instr Function performs exact matches.
How do you check if a string contains a word in VBA? ›The VBA InStr function is one of the most useful string manipulation functions around. You can use it to test if a string, cell or range contains the substring you want to find. If it does, “InStr” will return the position in the text string where your substring begins.
How do I get a list of references in VBA? ›The VBA reference list (figure 1) can be accessed by the VBE menu Tools > References sequence. The Available References list displays each reference item by its description property, in priority order. References are also part of the libraries drop-down in the Object Browser (figure 2).
How do I auto populate words in Excel? ›Put the mouse pointer over the bottom right-hand corner of the cell until it's a black plus sign. Click and hold the left mouse button, and drag the plus sign over the cells you want to fill. And the series is filled in for you automatically using the AutoFill feature.
Can VBA be used for Word document? ›
In the world of VBA programming, Word exposes a Document object. By using VBA code, you can instruct the Document object to do things such as Open, Save, or Close.
How do I automatically generate a Word document in Excel? ›- Open Excel and choose your spreadsheet. ...
- Click "Insert" in the toolbar. ...
- Select "Text" and then "Object" ...
- Choose either "Create a new file" or "Create from file" ...
- Adjust the formatting of the embedded document. ...
- Save your spreadsheet.