Visual Basic for Applications (VBA) facilitates many useful data structures – its not just VBA Arrays you can use. COM libraries provide additional data structures such as the VBA Dictionary, ArrayList, Queue, Stack or SortedList.
But before we jump into explaining how these more complex data structures work and how to use them, let’s start with the basics – VBA array. Often you needn’t reach out for more complex structures, so it’s important to understand first the limits of VBA Arrays.

VBA Dictionary example

The VBA Dictionary is probably the second most often used data structure in VBA. Dictionaries are great at storing key-value pairs of data similarly as the VBA Collection object does.

The VBA Dictionary, however, offers some functionality that is not available with the VBA Collection object e.g. the option to specify a comparison method for Keys, which I will demonstrate below. This allows for a case-sensitive Key.

Ok, so let’s start with a simple example of how the VBA Dictionary can be used.

VBA Dictionary Late Binding

Dim dict as Object 'Declare a generic Object reference
Set dict = CreateObject("Scripting.Dictionary") 'Late Binding of the Dictionary

Dim key, val
key = "SomeKey": val = "SomeValue"
'Add item to VBA Dictionary
If Not dict.Exists(key) Then 
    dict.Add key, val
End If 

Debug.Print dict.Count 'Result: 1

'Dispose of VBA Dictionary
Set dict = Nothing

VBA Dictionary Early Binding

In case you prefer to want to declare variables as the Dictionary object you need to reference the Microsoft Scripting Runtime library. To do this go to the Tools menu and select References. From the pop-up window scroll down and select the library mentioned above. This will allow you to use the VBA Dictionary like this:

Dim dict as Dictionary 'Early binding of VBA Dictionary
Set dict = New Dictionary
'...

Both methods are ok with the difference that with Early Binding if you share your Excel file with another user he/she may have to first reference the Microsoft Scripting Runtime otherwise will be getting errors.

Hence I personally prefer Late Binding limiting any necessary actions from anyone I would be sharing my files with.For learning purposes, however, it may be easier to reference the library and use Early Binding allowing you to see the properties of the Dictionary using the suggestions (CTRL+Space).

Traversing items and keys

Below methods for traversing either all keys or items in a VBA Dictionary

Dim dict as Object, key, val
Set dict = CreateObject("Scripting.Dictionary") 
key = "Key1": val = "Val1"
dict.Add key, val
key = "Key2": val = "Val2"
dict.Add key, val

'Print all keys
For Each key In dict.Keys
   Debug.Print key
Next key

'Print all items
For Each item In dict.Items
   Debug.Print item
Next item

'Dispose of VBA Dictionary
Set dict = Nothing 

Removing items

You can remove either a single specific item by key using the Remove property or RemoveAll to remove all items in the Dictionary:

Dim dict as Object, key, val
Set dict = CreateObject("Scripting.Dictionary") 
key = "Key1": val = "Val1"
dict.Add key, val
key = "Key2": val = "Val2"
dict.Add key, val

'Remove Key2 from VBA Dictionary
dict.Remove "Key2"

'Remove all remaining items / empty the dictionary
dict.RemoveAll

'Dispose of VBA Dictionary
Set dict = Nothing 

Similar data structures

• Hashtable — similar to the Dictionary class. Represents a collection of key/value pairs that are organized based on the hash code of the key. If you want to learn when it’s efficient to use Hashtables read here. More here.
• SortedList — Similar to the Hashtable. Represents a collection of key/value pairs that are sorted by the keys and are accessible by key and by index. More here.

Creating your own structures (VBA Class)

If none of the above data structures are suitable for your needs you can always create your own VBA Class. A Class is a VBA Module that can be instantiated as an object (similarly as the objects above), have its own properties etc. Classes are a good way of encapsulating abstraction and complexity.

Alternatively, consider also a stack of any of the mentioned data structures. For example let’s say we want to manage a structure which would store words in different languages. Later we will want to quickly check if there is a word in a certain language in our multi-dictionary. How to do this? A Dictionary of Dictionaries should do the trick. The first Dictionary will have keywords mapping to different languages. The keyword will be e.g. “English” while the value will be an object of type Dictionary in which you can store the word in the “English” language. Cool right?