VBA Dictionary: How to fill in a Dictionary

VBA Dictionary Fill

A dictionary must first be filled with key/item pairs. When the object reference is assigned, it contains zero key/item pairs.

You can add as many key/item pairs as you like. The keys must be unique. The keys and items can be of any VBA type.

Or equivalently.


A key can be a variable of any VBA type, even object or variant.

Although keys can be of the type object or variant, we have never come across a situation where this seemed to be the best design choice. If you use objects or variants be aware that the variable is not the object itself.

Image showing types a key can have

Image showing types a key can have in watch window

When deserializing a JSON text, the keys will always be of the type string.


If the key is not unique, VBA will generate a run time error.

Image showing key not unique error

This seems straightforward, but it can be subtle.

If the key is a string, there are two options to compare: case sensitive ("key" <> "KEY", BinaryCompare, 0) and case insensitive ("key" = "KEY", TextCompare, 1). The default is case sensitive.

Or, equivalently.

You can change to case insensitive comparison.

Or, equivalently.

You need to make this choice before you start filling the dictionary. If the method Count returns > 0, the compare mode cannot be changed anymore (unless you first call the method RemoveAll).

Changing the compare mode affects the behavior of other methods.

When deserializing a JSON text into a dictionary, use the default.

Adding key/item pairs by using the method Add, is called explicit adding. There is also implicit adding. The following line of code illustrates implicit adding.

If the key does not yet exist, then the key/item pair will be added. If it the key already exists, this statement will overwrite the item attached to the key, without warning. If you use this statement, be aware of the compare mode.

This example shows it is risky to change the CompareMode of a dictonary in an existing piece of VBA code (without looking at the other lines in which the dictionary is used). We try to avoid adding key/value pairs implicitely.

When deserializing a JSON text into a dictionary, use the default.


An item can be of any VBA type.

As it can be an object, this means an item can be another dictionary. It is this feature that makes dictionaries the obvious output choice for JSON deserialization.

Image showing types an item can have

Image showing types an item can have in watch window