The method SetHowToConvertJSONNull
Description
The sub SetHowToConvertJSONNull defines the VBA type and value into which the JSON value null will be converted when deserializing.
The only thing that the sub SetHowToConvertJSONNullToVBA does is set a parameter in the class cJSON. The parameter is used when deserializing the argument JSONText of Deserialize. The default value is to convert a JSON value null into the VBA value Null. However, Null can be annoying to handle. For this reason, the class offers some other options.
Syntax
Public Sub SetHowToConvertJSONNullToVBA(ByVal HowToConformToJSONNull As eHowToConformToJSONNull, Optional ByVal CustomStringNull As String = vbNullString)
Arguments
| Name of argument | Type of argument | |
|---|---|---|
HowToConformToJSONNull | Enumeration eHowToConformToJSONNull | Required |
CustomStringNull | String | Optional |
The argument HowToConformToJSONNull can take the following values.
| Value | VBA type | |
|---|---|---|
UseNull | Variant / Null | Default |
UseNullString | String / vbNullString | |
UseEmptyString | String / "" | |
UseCustomString | String / Add a string of your choice, like "NA", or "." or ... | |
UseZero |
The argument CustomStringNull is only used when the argument HowToConformToJSONNull has the value UseCustomString. In all other cases, the value of the argument CustomStringNull is ignored.
Type of function
The sub is a public method of the class cJSON.
Example 1: Null can be annoying to handle
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 JSONString As String JSONString = "{"Name"":null}" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONString) If (JSON.IsOk()) Then MsgBox D.Item("Name") 'generates a VBA error Else MsgBox JSON.ShowWhyNotOk() End If Set D = Nothing Set JSON = Nothing End Sub |
When you run the example, you will get this error.
Why?
The deserialization was ok. It is what happens afterward that is not ok. The implicit conversion to string in MsgBox D.Item("Name") is generating the error. VBA cannot convert a variant with value Null into a string. That can be annoying, especially, if you don't expect to have null in the JSON text. Note that the editor didn't warn you. Also, the code compiles without problems. The VBA error is generated at run time.
There are two ways to do away with the VBA error.
The first option is to adjust the VBA code that is running after the deserialization. Depending on the complexity of the VBA code, this can be easy or something you want to avoid. In the example, it is easy to do.
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 Example1Bis() Dim JSONString As String JSONString = "{""Name"":null}" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONString) If (JSON.IsOk()) Then If (IsNull(D.Item("Name"))) Then MsgBox "Null" 'shows Null Else MsgBox D.Item("Name") End If Else MsgBox JSON.ShowWhyNotOk() End If Set D = Nothing Set JSON = Nothing End Sub |
The second option is to change the VBA type and value into which the JSON value null will be converted when deserializing by using the sub SetHowToConvertJSONNullToVBA. The second option is illustrated below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Public Sub Example1Ter() Dim JSONString As String JSONString = "{""Name"":null}" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary JSON.SetHowToConvertJSONNullToVBA UseCustomString, "Unknown" Set D = JSON.Deserialize(JSONString) If (JSON.IsOk()) Then MsgBox D.Item("Name") 'shows Unknown MsgBox TypeName(D.Item("Name")) 'shows String Else MsgBox JSON.ShowWhyNotOk() End If Set D = Nothing Set JSON = Nothing End Sub |
Choose whatever option you like best.