VBA Dictionary: Introduction


An object to store things

In programming, it is often handy to have an object that can store all kind of things, whatever the type of the thing you would like to store. An object that can store numbers, strings, dates, arrays or any other object. An object of unlimited space into which you can put things whenever you like, and, retreive them whenever you need to, quickly and easily.

A collection

Standard VBA only offers a collection. The collection object is handy, but, it has some disadvantages. Its main disadvantage is that it doesn't really allow for intuitive key handling. The collection is handy, but, for our purpose - the deserialization of a JSON text - there is a better object.

A dictionary

The Microsoft Scripting Runtime library (scrrun.dLL) offers a dictionary. So, dictionaries are not part of standard VBA and you will need add a reference to the Microsoft Scripting Runtime library in your code if you want to use them. Dictionaries are great objects. You can store all other VBA variables into them, as many as you want. The stored VBA variables are easy to retreive by using a unique key.

Image showing that a dictionary can store anything

Dictionaries can even store other dictionaries. It is this property the makes them the perfect object to deserialize JSON texts into.

Image showing that a dictionary can store anything

A dictionary is a set of key/item pairs. The only requirement is that the keys must be unique.

JSON Deserialization

Assume you want to deserialize this JSON text.


Here is the VBA code to deserialize.

The return of the method Deserialize is always a dictionary. If the deserialization is successful, the data that is in the JSON text has been put in the dictionary. If it is not successful, the dictionary will be Nothing. By using the dictionary, the data can be used in your VBA code whenever you like.


Here is an exhaustive list of all the methods for a dictionary.

  • Add
  • CompareMode
  • Count
  • Exists
  • Item
  • Items
  • Key
  • Keys
  • Remove
  • RemoveAll

Some final tips?

Dictionaries are easy to handle if you keep things easy.

  1. Do not change the compare mode. Just accept that strings are compared in a case sensitive way.
  2. Do not use objects or variants containing objects or null as key.
  3. Do not use objects or variants containing objects as items.

If you don't like this rules, you need to know what you are doing. In the context of JSON deserialization, we will stick to these rules.