Dictionary in Excel VBA
I recently worked on a project which involved creating a macro in Microsoft Excel. Macros can be easily created without any programming knowledge by recording a macro. However, recording macros do not help with advanced or customized use cases. VBA is used in such cases where you can code the macro to suit your requirement.
VBA and its Key-Value Pair Data Structure
Just like any other programming language, VBA has its own syntax of variable declarations, data types, conditional and looping statements etc. But, I was not able to find a particular data structure in VBA, a data structure that deals with key-value pairs. Java
has the Map Interface, Python
has the Dictionary, Ruby
has the Hash and so on. Why doesn’t VBA have one by default?
It turns out that VBA does have a key-value pair data structure called the Dictionary. To use it in a project, it has to be enabled manually by adding an reference. The Dictionary in VBA contains Keys
and Items
as its values. The Keys and Items can be of any data type supported by VBA.
Creating a Dictionary
Early Binding
This type of Dictionary creation requires adding an reference before usage. This also enables auto-complete of code, which allows us to view the procedures and properties of the Dictionary in the editor.
To add an reference,
- Select
Tools -> References
from the Visual Basic Menu. - Select
Microsoft Scripting Runtime
from the list of references and save.
Now, the Dictionary can be initialized as follows
Dim dict As Dictionary
Set dict = New Dictionary
Late Binding
This type of Dictionary creation does not require adding an reference before usage. This does not have the auto-complete feature.
The Dictionary can be initialized as follows
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Add to Dictionary
An entry can be added to the Dictionary as follows
Syntax
dict.Add Key, Item
Examples
dict.Add "Name", "John"
dict.Add Key:="Age", Item:=25
Just like other programming langugaes, duplicate Keys are not allowed in VBA Dictionary too. If a duplicate Key is added to the Dictionary, a Run-time error is thrown.
Read from Dictionary
An Item can be read from the Dictionary using its Key as follows
Syntax
dict.Item(Key)
Examples
dict.Item("Name") 'Returns "John"
The list of Keys and Items can be read from the Dictionary as follows
Syntax
dict.Keys 'Returns the list of Keys
dict.Items 'Returns the list of Items
A Key can be checked for existence in the Dictionary as follows
Syntax
dict.Exists(Key) 'Returns Boolean
Examples
dict.Exists("Name") 'Returns true
dict.Exists("Salary") 'Returns false
Update a Dictionary
An Item can be updated in the Dictionary using its Key as follows
Syntax
Set dict.Item(Key) = Item
Examples
Set dict.Item("Age") = 26
Remove from Dictionary
Item(s) can be removed from the Dictionary as follows
Syntax
dict.Remove(Key) 'Remove entry based on Key
dict.RemoveAll 'Remove all entries
VBA Dictionary in Office for Mac
When you are using Office for Mac, the VBA references list does not contain the Microsoft Scripting Runtime
option. How else do you create a Dictionary with early binding?
There are two open-source implementations of the Dictionary class that you can import into Excel for Mac.
Once imported, any of the above implementations prove to be a good replacement of Dictionary class provided by the Microsoft Scripting Runtime
reference.
Conclusion
Data types that deal with collection of objects are important for any programming language. While collections such as Arrays or Lists are used to deal with multiple entries of single objects, key-value pair data structures are equally necessary to pair objects together and for lookups.