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.
Syntax
Function Deserialize(ByRef JSONText As String) As Dictionary
Arguments
| Name of argument | Type of argument | |
|---|---|---|
JSONText | String | Required |
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
Deserializedepends on the following parameters.
| Name of parameter | Method to set | Possible values |
|---|---|---|
HowToConformToJSONGrammar | SetHowToConformToJSONGrammar |
|
HowToConvertJSONNumberToVBA | SetHowToConvertJSONNumberToVBA |
|
HowToConformToJSONNull | SetHowToConvertJSONNullToVBA |
|
Example 1: Basic behavior
Here is the JSON text to deserialize.
Here is the VBA code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Public Sub Example1() 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 |
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.
Here is the VBA code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | Public Sub Example2() Dim JSONText As String JSONText = "{""Name"":FALSE}" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONText) If (D Is Nothing) Then MsgBox "D is nothing" 'shows D is nothing Else MsgBox "D contains data" End If JSON.SetHowToConformToJSONGrammar BeForgiving Set D = JSON.Deserialize(JSONText) If (D Is Nothing) Then MsgBox "D is nothing" Else MsgBox "D contains data" 'shows D contains data End If Set D = Nothing Set JSON = Nothing End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Public Sub Example3() Dim JSONText As String JSONText = "{}" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONText) If (JSON.IsOk()) Then If (D Is Nothing) Then MsgBox "D is nothing" Else MsgBox "D contains data" 'shows D contains data End If Else MsgBox JSON.ShowWhyNotOk() End If Set D = Nothing Set JSON = Nothing End Sub |
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.
""
vbNullString
Here is the VBA code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | Public Sub Example4() Dim JSONText1 As String Dim JSONText2 As String Dim JSONText3 As String JSONText1 = "abc" JSONText2 = "" JSONText3 = vbNullString Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONText1) If (JSON.IsOk()) Then MsgBox D.Item("Name") MsgBox TypeName(D.Item("Name")) Else MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"JSON grammar","Description":"First (non white space) character is incorrect. Expecting { or [.","Position":1,"CharacterAtPosition":"a"}} If (D Is Nothing) Then MsgBox "D is nothing" 'shows D is nothing End If Set D = JSON.Deserialize(JSONText2) If (JSON.IsOk()) Then MsgBox D.Item("Name") MsgBox TypeName(D.Item("Name")) Else MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"JSON grammar","Description":"JSON cannot be empty."}} If (D Is Nothing) Then MsgBox "D is nothing" 'shows D is nothing End If Set D = JSON.Deserialize(JSONText3) If (JSON.IsOk()) Then MsgBox D.Item("Name") MsgBox TypeName(D.Item("Name")) Else MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"JSON grammar","Description":"JSON cannot be empty."}} If (D Is Nothing) Then MsgBox "D is nothing" 'shows D is nothing End If Set D = Nothing Set JSON = Nothing End Sub |
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.