VBA Dictionary: How to look up in a Dictionary

VBA Dictionary Look up

Looking up items

Dictionaries have been designed to allow for easy (and fast) lookups of items. Lookups are based the keys of the key/item pairs.

If the items are of the type dictionary, lookups can be nested. This feature is used a lot in the context of JSON deserialization.

When the keys are of the type string, keep in mind the compare mode.

Test for existence of a key

It is good practice to check if the key exists, before you look up the item.

If you try to get an item of a key that does not exist, you will not error. The item will be empty.

It is possible to test if the item is empty by using the function IsEmpty.

In the context of JSON text deserialization, safe coding comes at the cost of elegance. Safe coding turns the elegant

into the not so-elegant but safer

It can be a pain to come up with clear error messages for every case.

The type of the item

An item can be of any VBA type. When assigning the item to a VBA variable: the types must match. If not, you will get a run time error.

Image showing error when types don't match

Sometimes, you might not know the VBA type of the item upfront. In such a case, the VBA function VarType can be used to check the VBA type of the item.

If the item is an object, the function VarType can tell you, but, it cannot tell you which kind of object. For this, you can use the VBA function TypeName or the operator TypeOf...Is.

If the item is of the type dictionary, the following code works.

In the context of JSON deserialization, you will probably use code like this to extract information from the dictionary. As all keys are strings, you just need to know the sequence of keys that leads to the item you need. In a dictionary that is the result of the deserialization of a JSON text, all items can be looked up in this way. If the dictionary is the result of a JSON deserialization, only the following types are possible for an item.

Image showing item after JSON deserialization


It is possible to loop through a dictionary. You can loop through the keys and through the items. Take care to manage the possible different VBA types of the keys and items.

Looping is useful when you are copying a dictionary.

It is possible to output all keys or all items to an array of the type variant. The array is one dimensional and zero based (always, even if you use the option Base 1).

Because of this feature, there is an alternative way to loop through a dictionary.