VBA code: Deserialize JSON


Before you can use the data that is in a JSON text in VBA, you must make it available in VBA. In other words, you need to put the content of the JSON text into a VBA variable. One simple option is to put the JSON text into a variable of the type String and extract the data from the String when you need it. An more interesting alternative is to put the content of the JSON text into a variable of the type Dictionary. As dictionaries can handle many types of data and make it easy to look up data, the VBA class uses a Dictionary. The operation that that puts the content of the JSON text into a variable of the type Dictionary is called the deserialization.

The method Deserialize

Description

The function Deserialize will try to deserialize a string into a Dictionary. It is the main method of the class cJSON.

Image showing mapping the typical flow of the VBA code: Deserialize JSON

Syntax

Function Deserialize(ByRef JSONText As String) As Dictionary

Arguments

Name of argumentType of argument
JSONTextStringRequired

The argument JSONText is expected to be a JSON text. However, it can be any string, even the empty string or vbNullString. The string is passed by reference, to avoid string copying. The function Deserialize does not change the value of JSONText.

Return

If the argument JSONText contains a text that conforms to the JSON format, the return of the function will be an object of the type Dictionary that is different from Nothing. In that case, the Dictionary that is returned will contain the data that are in the JSON text. Otherwise, if the argument JSONText contains a text that does not conform to the JSON format, the function will exit at the first error and return an object of the type Dictionary that is Nothing. The error can be obtained using the function ShowWhyNotOk.

Type of function

The function is a public method of the class cJSON.

Remarks

  • The behavior of the function Deserialize depends on the following parameters.
Name of parameterMethod to setPossible values
HowToConformToJSONGrammarSetHowToConformToJSONGrammar
  • BeStrict(default)
  • BeForgiving
HowToConvertJSONNumberToVBASetHowToConvertJSONNumberToVBA
  • TryLongTryDoubleElseUseString(default)
  • TryLongTryDoubleElseError
  • TryCurrencyTryDoubleElseError
  • TryLongElseError
  • TryCurrencyElseError
  • TryDoubleElseError
  • UseString
HowToConformToJSONNullSetHowToConvertJSONNullToVBA
  • UseNull(default)
  • UseNullString
  • UseEmptyString
  • UseCustomString
  • UseZero

Example 1: Basic behavior

Here is the JSON text to deserialize.

{"Name":"Value"}

Here is the VBA code.

Don't forget to put Set when assiging the return of Deserialize to a Dictionary.

Example 2: Changing the behavior of Deserialize

Here is the text to deserialize. Strictly speaking, it is not a JSON text. {"Name":false} would be a JSON text.

{"Name":FALSE}

Here is the VBA code.

The behavior of the function Deserialize is affected by the parameter HowToConformToJSONGrammar.

Example 3: When the JSON text contains an empty JSON object

Here is the JSON text to deserialize. This is a JSON text (but a useless one).

{}

Here is the VBA code.

If the value of the argument JSONText is a JSON text, the Dictionary that is returned by Deserialize will never be Nothing.

Example 4: When the argument is not a JSON text

Here are the strings to deserialize. These texts do not conform to the JSON format. They are no JSON texts.

"abc"
""
vbNullString

Here is the VBA code.

There are three ways of checking whether or not the deserialization went well. First, you can evaluate the return of the function IsOk. Second, you can check if the string that is returned by ShowWhyNotOk has length zero or not. Third, you can check if the Dictionary that is returned by Deserialize is nothing. The three ways are completely equivalent.