Visual Basic for Applications:
Using associative arrays

How to:

In VBA, the Dictionary object provides functionality similar to associative arrays. You must first add a reference to the Microsoft Scripting Runtime to use it:

1. In the VBA editor, go to Tools > References…
2. Check “Microsoft Scripting Runtime” and click OK.

Here’s how to declare, populate, and access items in a Dictionary:

Dim sampleDictionary As Dictionary
Set sampleDictionary = New Dictionary

' Adding items
sampleDictionary.Add Key:="Name", Item:="John Doe"
sampleDictionary.Add Key:="Age", Item:=29
sampleDictionary.Add Key:="Occupation", Item:="Engineer"

' Accessing items
Debug.Print sampleDictionary.Item("Name")  ' Output: John Doe
Debug.Print sampleDictionary.Item("Age")   ' Output: 29

' Checking if a key exists
If sampleDictionary.Exists("Occupation") Then
    Debug.Print "Occupation Key Exists"
End If

' Removing items
sampleDictionary.Remove("Occupation")

' Looping through the dictionary
For Each Key In sampleDictionary.Keys
    Debug.Print Key & ": " & sampleDictionary.Item(Key)
Next Key

Deep Dive

The Dictionary object under the hood interfaces with components of the Windows Scripting Host. As such, it’s a late-bound COM object, which was a common way to extend VBA’s functionality in the past. Its usage in VBA can significantly enhance the language’s ability to manipulate complex datasets without enforcing a rigid structure, as seen in traditional arrays or Excel ranges.

One limitation to bear in mind is accessing the Dictionary requires setting a reference to the Microsoft Scripting Runtime, which can complicate distribution of your VBA projects. Alternatives like Collections exist within VBA but lack some of the Dictionary’s key features, such as the ability to easily check for the existence of a key without triggering an error.

In more recent programming contexts, languages like Python offer built-in support for associative arrays (known as dictionaries in Python too) without the need for adding external references. This built-in support streamlines the process and offers more advanced features out of the box. However, within the confines of VBA and for specific applications geared towards automating tasks in the Microsoft Office suite, using the Dictionary object remains a powerful and relevant method for associative array-like data structures.