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.