VBA Dictionary: Introduction

General

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.

{"Name":"Value"}

Here is the VBA code to deserialize.


	Public Sub Example()
		
		Dim JSONText As String
		JSONText = "{""Name"":""Value""}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONText)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")            'shows Value
			MsgBox TypeName(D.Item("Name"))  'shows String
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example()

Dim JSONText As String

JSONText = "{""Name"":""Value""}"

Dim JSON As cJSON

Set JSON = New cJSON

Dim D As Dictionary

Set D = JSON.Deserialize(JSONText)

If (JSON.IsOk()) Then

MsgBox D.Item("Name") 'shows Value

MsgBox TypeName(D.Item("Name")) 'shows String

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.

Methods

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.

Do not change the compare mode. Just accept that strings are compared in a case sensitive way.
Do not use objects or variants containing objects or null as key.
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.

All about the use of JSON in Office applications