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.