VBA Dictionary Objects

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

and

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 4^th item to the dictionary.

Deleting Items from the Dictionary

Simarly, it is very easy to delete items from the dictionary.

MyDictionary.Remove(“20”)

Will remove Item2

You can also remove all of the items in a dictionary.

MyDictionary.RemoveAll

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