In this Article
This article will explain and demonstrate the use of the VBA Dictionary Object.
VBA allows a user to create class modules where the user can create collection objects. A VBA dictionary can be compared to a collection object or array in that it stores information in memory while the VBA code is running and allows the user to recall and use this information later in the code. The dictionary object in VBA is more flexible than a collection in that it can be amended in size automatically. It also is a variant data type, so any data can be added it such as dates, numbers, text etc.
Using the VBA Dictionary
The VBA dictionary can be use in other Microsoft Office programs such as Word or PowerPoint. It therefore needs to be accessed by either Early or Late Binding.
If you wish to use Early binding, you need to add a reference in your project to the Microsoft Scripting Runtime Library.
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.
Sub EarlyBindingExample() Dim MyDictionary As New Scripting.Dictionary End Sub
If you are using Late Binding, you can use Create Object to create a dictionary object. If you do this, you do not need to add a reference to the Microsoft Scripting Runtime to your project.
Sub LateBindingExample() Dim MyDictionary As Object Set MyDictionary = CreateObject("Scripting.Dictionary") End Sub
Adding items to the Dictionary
Once you have created a dictionary object, you can add items to the dictionary.
Sub PopulateDictionary() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add 10, "MyItem1” MyDictionary.Add 20, "MyItem2" MyDictionary.Add 30, "MyItem3" 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 key must be unique as this is the lookup value in the dictionary. If the key is not unique, when you try to run the code, you will get an error.
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 "MyItem1", 10
The values have been reversed between Key and Item, but this would still work, although the search key would now become “MyItem1”.
Reading the Values from the Dictionary
Once you have populated your dictionary, you can read the keys and items from the dictionary either individually, or by using a loop to go through all the items and keys
To read individual values, you can just read the item or key by the index number in the dictionary. The index number in a dictionary always starts at 0
Sub IndexNumbers() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add 10, "Item1" MyDictionary.Add 20, "Item2" MyDictionary.Add 30, "Item3" MsgBox MyDictionary.Keys(0) End Sub
Will return 10
Sub IndexNumbers() Dim MyDictionary As New Scripting.Dictionary MyDictionary.Add 10, "Item1" MyDictionary.Add 20, "Item2" MyDictionary.Add 30, "Item3" MsgBox MyDictionary.Items(1) End Sub
Will return “Item2”
We can also loop through the dictionary to return each key and item
Sub ReadDictionary() Dim MyDictionary As New Scripting.Dictionary, I As Variant MyDictionary.Add 10, "MyItem1" MyDictionary.Add 20, "MyItem2" MyDictionary.Add 30, "MyItem3" For Each I In MyDictionary.Keys MsgBox I & " " & MyDictionary(I) Next I End Sub
Changing an Item Value of a Key
It is very simple to change the item value in a dictionary.
MyDictionary(“20”) = “MyNewItem2”
You can also add items to a dictionary by using this method. If the key does not exist, it will be automatically created.
So the code:
MyDictionary(“40”) = “MyItem4”
Will add a 4th item to the dictionary.
Deleting Items from the Dictionary
Simarly, it is very easy to delete items from the dictionary.
Will remove Item2
You can also remove all of the items in a dictionary.
Scope of the Dictionary
Because you have declared this dictionary within the sub PopulateDictionary, it will only be available within this sub-procedure. If you want a dictionary to be available to your whole module, you need to declare it at a module level rather than a procedure level. You can do this at the top of the module beneath Option Explicit.
Furthermore, If you want it to be available for your entire project, then you would need to declare it at a global level for it to be available to your entire project. It can still be declared at the top of your module, but you need to use the PUBLIC statement rather than the DIM statement to declare a global variable.
Using the Dictionary in a Workbook
Once you have populated your dictionary, you can then populate a workbook with the items and /or keys in the dictionary.
This code below will loop through all the items in the dictionary and populate column A with the key value and column B with the item value.
Sub CopyIntoWorksheet() Dim MyDictionary As New Scripting.Dictionary Dim n As Integer MyDictionary.Add 10, "Item1" MyDictionary.Add 20, "Item2" MyDictionary.Add 30, "Item3" Range("A1").Select For n = 0 To MyDictionary.Count - 1 ActiveCell.Value = MyDictionary.Keys(n) ActiveCell.Offset(0, 1).Value = MyDictionary.Items(n) ActiveCell.Offset(1, 0).Select Next n End Sub