Unlocking the Scripting Dictionary
The scripting dictionary in Excel is a mystery to most users, even the programmers who think they are sharp, are generally in the dark. It is a wonderful tool that runs like grease lightning and can perform some fantastical calculations. Harnessing this weapon is a matter of understanding how the tool itself works.
The following Youtube video takes you through the scripting dictionary examples on this page. Enjoy.
The scripting dictionary works along the same lines as a normal dictionary, a word is not entered twice with the same spelling. Same with the scripting dictionary in Excel, keys are the unique identifiers that marks the entry into the dictionary. With only unique keys being allowed to enter the dictionary, this opens up wonderful possibilities. The dictionary can consolidate like data as if the key exists, it can grow and consolidate at light speed. The results can be output anywhere and updated in real time.
Methods Associated with the Dictionary
The following is a summary from Microsoft - these are the methods associated with the scripting dictionary.
Method Description
Add Adds a new key/item pair to a Dictionary object.
Exists Returns a Boolean value indicating if a key exists in the Dictionary object.
Items Returns an array of all the items in a Dictionary object.
Keys Returns an array of all the keys in a Dictionary object.
Remove Removes one specified key/item pair from the Dictionary object.
RemoveAll Removes all the key/item pairs in the Dictionary object.
Putting Data into a Dictionary
The following are ways to put items into a dictionary. There are two parts to a basic dictionary entry. A Key:
Key - Sets a new key value for an existing key value in a Dictionary object.
and an Item:
Item - Sets or returns the value of an item in a Dictionary object.
So a typical entry into the dictionary would look like this:
“Key 1” , “Item 1”
With Key 1 being the unique key and Item 1 being its associated item. Rather confusingly to the layperson, the Item obtains the value for the key when referring to ranges which is usually done through an array object. However, to simplify the process I will show you how it works at a very basic level by adding a key and an item.
A Practical Look at the Add Method
Using the .Add method we can ad both a Key and the keys Item. This is done in the following way:
Sub ScriptKey()
Dim d As Variant
Set d = CreateObject("Scripting.dictionary")
d.Add "Key1", "i1"
d.Add "Key2", "i2"
d.Add "Key3", "i3"
End SubThe following would be the output of the above in the locals window.
The items (i1, i2, i3) can’t be seen. To see them we can debug print the keys. The VBA code would look as follows:
Sub ScriptPrint()
Dim d As Variant
Set d = CreateObject("Scripting.dictionary")
d.Add "Key1", "i1"
d.Add "Key2", "i2"
d.Add "Key3", "i3"
Debug.Print d.keys()(0), d.items()(0)
Debug.Print d.keys()(1), d.items()(1)
Debug.Print d.keys()(2), d.items()(2)
End Sub
While the output from the Immediate window would look like this.
With the key on the left and the item on the right.
Caveat - the only time you would demonstrate the dictionary using the .Add method is when teaching someone how the dictionary works. It is not practical in Excel to use the .Add method as it is a language intricately designed to work with arrays of data inside of Excel itself. In the next blog post we will delve into using the dictionary in conjunction with ranges.
Referring to Microsoft Scripting Runtime
To get the best out of the dictionary it is best to use a reference to the Microsoft Scripting Runtime in the reference menu of VBA. This will give you full access to IntelliSense and allow you to see the objects available to you. In the video I will outline both methods.
To add the Microsoft Scripting Runtime reference go to the VBA Editor.
The following is the Microsoft Scripting Runtime
This allows a declaration to a new dictionary object.
Counting the Items in a Dictionary
To count the items in the dictionary, use the following method.
Sub ScriptCount()
Dim d As New Dictionary
Set d = CreateObject("scripting.dictionary")
d.Add "Key1", "i1"
d.Add "Key2", "i2"
d.Add "Key3", "i3"
MsgBox d.Count
End Sub
The above gives the total number of items in the dictionary.
Remove an Item from the Dictionary
Removing an items inside the dictionary is rather straightforward. Just refer to the dictionary and the item to remove. The following will work nicely.
Sub ScriptRem()
Dim d As New Dictionary
Set d = CreateObject("scripting.dictionary")
d.Add "Key1", "i1"
d.Add "Key2", "i2"
d.Add "Key3", "i3"
d.Remove "Key1"
MsgBox d.Count
End SubRemove All the Items from A Dictionary
Removing every items inside the dictionary is easier than removing one item. Just refer to the dictionary itself and invoke the RemoveAll command. The following will remove everything.
Sub ScriptRemAll()
Dim d As New Dictionary
Set d = CreateObject("scripting.dictionary")
d.Add "Key1", "i1"
d.Add "Key2", "i2"
d.Add "Key3", "i3"
d.RemoveAll
MsgBox d.Count
End SubComparing Items in the Dictionary
Using the compare mode is a way to make names that are capitalised get treated the same as lower case text. As mentioned the dictionary accepts unique keys.
KEy1 and Key1
are not the same so a unique key will be made for each. If this is not the intention the Comparemode can help to solve the problem.
Sub ScriptKeyPass()
Dim d As New Dictionary
Set d = CreateObject("Scripting.dictionary")
d.Add "KEy1", "i1"
d.Add "Key1", "i2"
End SubWith the above method the new key is added.
While the following code will fix the comparison problem.
Sub ScriptComp()
Dim d As New Dictionary
Set d = CreateObject("Scripting.dictionary")
d.CompareMode = TextCompare
d.Add "KEy1", "i1"
d.Add "Key1", "i2"
End Sub
An error will occur - which points to fixing the source data.
That is a basic introduction to the scripting dictionary in Excel. It does not even touch on the full capability of this amazing resource. It is truly an amazing and thought provoking tool that is inside VBA.
The Excel file with all the above Scripting code is below.
The next part in this series, Unlocking the Scripting Dictionary is contained at the following link.
