In this Article
- Using a VBA Dictionary
- Distributing your Excel Application Containing a Dictionary
- Scope of a Dictionary Object
- Populating and Reading from Your Dictionary
- Using the Item Index Number
- Filtering the Dictionary
- Changing an Item Value of a Key
- Test if a Key Exists
- Using Multiple Values in a Dictionary
- Deleting Items
- Changing Case Sensitivity for Searches
- Sorting the Dictionary
- Copying a List of Keys to a Worksheet
- Comparing a Dictionary with a Collection
Using a VBA Dictionary
A VBA dictionary operates in a similar way to a collection object, but it has more properties and methods, and offers more flexibility
The dictionary stores the data in memory and can be easily manipulated. There is no auto calculation, background backup, and screen refreshing required, so your code will run considerably faster.
The dictionary object works in a similar way to a normal dictionary that you would use if you want to find out the meaning of a word. Each entry in the dictionary object has a ‘key’ value and an ‘item’ value. You use the ‘key’ the key value to lookup the item value in the dictionary object, in a similar way that you would use a conventional dictionary.
Because of the way the dictionary object works, the key values must all be unique, in the same way as in a conventional dictionary. Imagine if you opened your conventional dictionary to look up the meaning of a word, and found the word listed more than once with two entirely different definitions. You would be very confused!
Key values are usually text or numbers, or both. Users often find it easier to remember names of keys as text rather than just numbers.
Comparing to a collection object, the collection object is read only. It only has two methods (Add and Remove) and two properties (Count and Item). Once an item has been added to a collection object, it can only be removed, but not edited, which is a cumbersome procedure if the value of an item needs to be changed.
A dictionary object will change in size automatically to suit the number of items within it. It does not need to be defined in size, like a conventional array
The dictionary object is one-dimensional and the data type is ‘Variant’, so any data type can be entered in to it e.g. numeric, text, date
The VBA dictionary is not native to Excel and needs to be accessed by either early or late binding when defining the dictionary object
Sub EarlyBindingExample() Dim MyDictionary As New Scripting.Dictionary End Sub
Sub LateBindingExample() Dim MyDictionary As Object Set MyDictionary = CreateObject("Scripting.Dictionary") End Sub
If you use the early binding, you must add a reference to the ‘Microsoft Scripting Runtime’ library
You do this by selecting ‘Tools | References’ on the menu bar of the Visual Basic Editor (VBE) window and a pop-up window will appear with a list of available libraries.
Scroll down to ‘Microsoft Scripting Runtime’ and tick the box next to it. Click OK and this library is now part of your VBA project and can be referenced using early binding. All examples of code in this article will use early binding.
Your code will run considerably faster with early binding, because it is all compiled up front. With late binding, the object has to be compiled as the code runs
The Scripting Runtime library has ‘Intellisense’. As you write your code, you will see lists of available methods and properties appearing, which helps to prevent mistakes being made in spelling, which will cause bugs in your program
Also, if you press F2 within the VBE, and select the ‘Scripting’ library, you will see all the methods and properties available, and the parameters required for each
Distributing your Excel Application Containing a Dictionary
As already pointed out, the Scripting Runtime library is not part of Excel VBA so if you distribute your application to other users, they must have access to the Scripting Runtime library on their computer. If they have not, then an error will occur.
It is a good idea to include some VBA code to check that this library is present when your Excel application is loaded. You can use the ‘Dir’ command to do this on the ‘Workbook Open’ event
The location of the file is C:\Windows\SysWOW64\scrrun.dll
Scope of a Dictionary Object
The Dictionary object is only available while the Excel workbook is open. It does not get saved when the workbook is saved.
If your dictionary is to be available to all the routines within your module, you need to declare it (Dim) in the Declare section at the very top of the module
You define it as a global object if you want your dictionary to be used throughout your code.
Global MyDictionary As New Dictionary
Populating and Reading from Your Dictionary
To start with, you need to create a dictionary, populate it with some data, and then iterate through it to prove that the data exists
Sub PopulateReadDictionary() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "MyItem1", 10 MyDictionary.Add "MyItem2", 20 MyDictionary.Add "MyItem3", 30 For n = 0 To MyDictionary.Count - 1 MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n) Next n End Sub
This code creates a new dictionary object called ‘MyDictionary’ and then populates it with three items. The Add method has two parameters – Key and Item, and they are both required
The data types for Key and Item are both variant so they will accept any type of data – numeric, text, date, etc
The first item in the dictionary could be added as:
MyDictionary.Add 10, "MyItem1"
The values have been reversed between Key and Item, but this would still work, although the search key would now become 10.
However, it is important to understand that the key value is the lookup value in the dictionary. It works in a very similar way to the VLOOKUP function in Excel. Because all the keys have to have unique values, you can specify a key value and instantly return the item value for that key.
Note that the dictionary index starts at 0 so you need to subtract 1 from the dictionary count used in the For…Next loop
You can also use a For…Each loop to read the values in the dictionary:
Sub PopulateReadDictionary() Dim MyDictionary As New Scripting.Dictionary, I As Variant MyDictionary.Add "MyItem1", 10 MyDictionary.Add "MyItem2", 20 MyDictionary.Add "MyItem3", 30 For Each I In MyDictionary.Keys MsgBox I & " " & MyDictionary(I) Next I End Sub
This code will iterate through each item and display the item key and the item value
Using the Item Index Number
You can use the index number of a key or item to read the value
Sub IndexNumbers() Dim MyDictionary As New Scripting.Dictionary MyDictionary.CompareMode = TextCompare MyDictionary.Add "Item1", 10 MyDictionary.Add "Item2", 20 MyDictionary.Add "Item3", 30 MsgBox MyDictionary.Keys(2) MsgBox MyDictionary.Items(1) End Sub
This code will return the key ‘item3’ as the index starts at 0, and the item value 20
You can refer to individual key or item values within the Keys or Items collections by using the index numbers.
Filtering the Dictionary
There is not a direct method to do this, but it is quite simple to write code to do it:
Sub FilterDictionary() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "AAItem1", 10 MyDictionary.Add "BBItem2", 20 MyDictionary.Add "BBItem3", 30 For Each I In Filter(MyDictionary.Keys, "BB") MsgBox MyDictionary.Item(I) Next I End Sub
The filter value only works from the beginning of the key value. You cannot use wildcards in the filter. This code will return the two item values with key names beginning with ‘BB’
This will give you a subset of the dictionary based on your filter value, which you could then transfer to another dictionary or a worksheet. With careful planning on key names, making sure that there is a meaningful prefix to each, you would easily be able to split the dictionary into various component parts.
Changing an Item Value of a Key
The dictionary object has a big advantage over a collection in that the item value can be changed e.g.
MyDictionary("MyItem4") = "40"
In the collection, you would need to delete that entry and then re-create it.
Here is a code example:
Sub PopulateReadDictionary() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "MyItem1", 10 MyDictionary.Add "MyItem2", 20 MyDictionary.Add "MyItem3", 30 MyDictionary("MyItem2") = "25" MyDictionary("MyItem4") = "40" For n = 0 To MyDictionary.Count - 1 MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n) Next n End Sub
The above code sets up three items within the dictionary, and then changes the value of ‘MyItem2’ from 20 to 25.
It also changes the value of ‘MyItem4’ to 40. Note that in the add statements of the code, no ‘MyItem4’ was added. When you change the value of a key that does not exist, it is automatically created. This is extremely convenient, as no error is triggered, but it does mean that you need to be careful with your key names. An inadvertent spelling error in the key name would mean that a new key is created, and the original key name would still have the old value.
This could easily lead to integrity problems in the dictionary object.
Test if a Key Exists
You can check if a key value exists within the dictionary
Sub CheckExistsDictionary() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "MyItem1", 10 MyDictionary.Add "MyItem2", 20 MyDictionary.Add "MyItem3", 30 MsgBox MyDictionary.Exists("MyItem8") End Sub
The code adds three items to a new dictionary object, and then tests for a key (‘MyItem8’) which is not in the dictionary. This returns False, but had one of the existing keys been used, it would return True
Wildcards are not accepted. Search text is also case sensitive by default, but this can be changed (see later in article)
Using Multiple Values in a Dictionary
Unlike an array, the dictionary object is one dimensional only. This can lead to problems if you have several values that you want to put against a key.
One way round this is to concatenate each item value using a delimiter character in between each value e.g. ‘|’
Sub MultipleValues() 'Create dictionary object and variables Dim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As String Dim V3 As Date, Temp As String, N As Integer 'Populate 3 variables to demonstrate multiple values V1 = 5 V2 = "Example of multiple values" V3 = "22-Jul-2020" 'Add the concatenated value to the dictionary using "|" delimiter MyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|" 'Capture the concatenated dictionary value from the dictionary into a variable Temp = MyDictionary("MyMultipleItem") 'Iterate through the concatenated string to separate the individual valuues Do 'Find the position of a delimiter N = InStr(Temp, "|") 'If there are no more delimiters the exit Do loop If N = 0 Then Exit Do 'Display text relative to position of delimiter found MsgBox Left(Temp, N - 1) 'Truncate the concatenated string to the next character after the delimiter found Temp = Mid(Temp, N + 1) Loop End Sub
Another way around this problem is to design your own sub-scripting system for key names. There is no reason why you should not use brackets and numbers in key names
Sub MultipleValues() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "Multiple(1)", 5 MyDictionary.Add "Multiple(2)", "Example of multiple values" MyDictionary.Add "Multiple(3)", "22-Jul-2020" For N = 1 To 3 MsgBox MyDictionary("Multiple(" & N & ")") Next N End Sub
This code adds three keys to the dictionary, but each key name contains a sub script number in brackets. You can then refer to key name, but using the sub script number concatenated in. This is very similar to using an array object
You can remove individual items by reference to the key value
Note that because key names are unique, this only removes that one particular key and item value
You can also clear the dictionary completely
Here is an example of using ‘Remove’ in VBA:
Sub RemoveValues() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "Item1", 10 MyDictionary.Add "Item2", 20 MyDictionary.Add "Item3", 30 MyDictionary.Remove ("Item2") For N = 0 To MyDictionary.Count - 1 MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N) Next N MyDictionary.RemoveAll MsgBox MyDictionary.Count End Sub
The code adds three items to the dictionary, and then removes ‘Item2’. It then iterates through the dictionary to prove that ‘Item2’ no longer exists
Finally, the code removes all items in the dictionary and displays the dictionary count, which is now zero.
Changing Case Sensitivity for Searches
If you do a search for a key, it is case sensitive by default. However, you can use the ‘CompareMode’ property to change this.
Note that this must be done immediately in the code after you create the dictionary object, but before you add any data into the dictionary. Once the compare mode has been set, it cannot be changed within that dictionary.
Sub ChangeCaseSensitivity() Dim MyDictionary As New Scripting.Dictionary MyDictionary.CompareMode = TextCompare MyDictionary.Add "Item1", 10 MyDictionary.Add "Item2", 20 MyDictionary.Add "Item3", 30 MsgBox MyDictionary.Exists("item2") End Sub
In this example, the compare mode is set to ‘TextCompare’ which means that it is not case sensitive. The ‘Exists’ statement at the end of the example will return True, in spite of the fact that the search text is all in lower case.
In Excel there are only two values that can be used for compare mode. Binary Compare is case sensitive, and Text Compare is non case sensitive
If you have compare mode set to Binary Compare, you need to be careful in naming your keys. If you set a name to have an upper-case letter as the first character, then when you are changing the value, you must make sure that you still make the first character upper-case. If you start with a lower-case character, this will be interpreted as a new key, and could easily lead to confusion and errors in your dictionary
Remember that if you change a value for a key and the key name does not exist due to a Binary Compare being used, a new key and value will be added to the dictionary.
If you use Text Compare instead, then any value changes will go to the key regardless of case. If you try to add the same item but spelt with a different case character, you will get an error because it already exists.
Sorting the Dictionary
As with the collection object, there is no method provided of being able to sort the dictionary, either using keys or item values.
However, since the VBA code is sitting in an Excel workbook, the dictionary data can be transferred into Excel in tabular form, and then the Excel sort facility can be applied to it. The dictionary can then be cleared using ‘RemoveAll’ and the sorted values added from the worksheet.
This code will sort both the keys and the items values
Sub SortMyDictionary() Dim MyDictionary As New Dictionary Dim Counter As Long 'Build dictionary with random order items MyDictionary.Add "Item5", 5 MyDictionary.Add "Item2", 15 MyDictionary.Add "Item4", 11 MyDictionary.Add "Item1", 2 MyDictionary.Add "Item3", 19 'Capture number of items in dictionary for future use Counter = MyDictionary.Count 'Iterate through dictionary copying each key and item to a consecutive cell on 'Sheet1' (column A) For N = 0 To MyDictionary.Count - 1 Sheets("Sheet1").Cells(N + 1, 1) = MyDictionary.Keys(N) Sheets("Sheet1").Cells(N + 1, 2) = MyDictionary.Items(N) Next N 'Activate the Sheet1 and use the Excel sort routine to sort the data into ascending order Sheets("Sheet1").Activate Range("A1:B" & MyDictionary.Count).Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _ "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:A5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Clear all items from the dictionary MyDictionary.RemoveAll 'Copy the cell values back into the empty dictionary object using the stored value (Counter) for the 'loop For N = 1 To Counter MyDictionary.Add Sheets("Sheet1").Cells(N, 1).Value, Sheets("Sheet1").Cells(N, 2).Value Next N 'Iterate through the dictionary to prove out the order that the items are now in For N = 0 To MyDictionary.Count - 1 MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N) Next N 'Clear the worksheet (Sheet1) - if necessary, delete it as well Sheets("Sheet1").Range(Cells(1, 1), Cells(Counter, 2)).Clear End Sub
This code creates a dictionary with five random order values added. It captures the number of items into a variable, and then iterates through the dictionary, transferring the key and item values into separate columns on a worksheet.
It then sorts the downloaded range, using column A as the sort field. The dictionary is cleared completely using the ‘RemoveAll’ method, and the code then iterates through the cell values in the worksheet adding them back to the dictionary.
Finally, the code iterates through the dictionary, displaying the key and item values concatenated to prove that the sort has worked.
By changing the parameters in the sort code, the data could be sorted by item values.
Copying a List of Keys to a Worksheet
You can copy a list of all key values into a worksheet using the following code:
Sub CopyKeyList() Dim MyDictionary As New Scripting.Dictionary MyDictionary.CompareMode = TextCompare MyDictionary.Add "Item1", 10 MyDictionary.Add "Item2", 20 MyDictionary.Add "Item3", 30 Sheets("Sheet1").Range("A1").Value = Join(MyDictionary.Keys, vbLf) End Sub
This will produce the result in your worksheet:
You can copy an entire dictionary into a worksheet using this code:
Sub CopyIntoWorksheet() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add "Item1", 10 MyDictionary.Add "Item2", 20 MyDictionary.Add "Item3", 30 Range("A1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys) Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items) End Sub
Your worksheet will look like this:
Comparing a Dictionary with a Collection
The Dictionary is faster than a collection.
A Collection is already within VBA. A Dictionary needs a reference to the Microsoft Scripting Dictionary to be added or an object created using late binding
A Collection item can only be written once and read many times. In a Dictionary, the item value can be changed. With a Collection, the item has to be removed and then the changed item added back.
The Collection works on index values, which can be difficult to work out which index value belongs where. The Dictionary works on unique key values which are used to locate an item
Retrieving a single item is slower in a large Collection than in a Dictionary
In a Collection, the keys are only used to lookup data and are not retrievable. In a dictionary, keys can be tested for existence, and can be used to find a particular item.
Collections are case-sensitive and this cannot be changed. In a Dictionary, the compare mode can be set to give case-sensitivity or non-case-sensitivity
In a Collection, the key values must be strings. In a Dictionary they can be any data type e.g. numeric, date, etc
Removing all items in a Collection involves re-defining the Collection object. The Dictionary has the ‘RemoveAll’ method for this.