VBA code: Set How To Convert JSON Null

The method SetHowToConvertJSONNull


The sub SetHowToConvertJSONNull defines the VBA type and value into which the JSON value null will be converted when deserializing.

Image showing mapping the typical flow of the VBA code

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.


Public Sub SetHowToConvertJSONNullToVBA(ByVal HowToConformToJSONNull As eHowToConformToJSONNull, Optional ByVal CustomStringNull As String = vbNullString)


Name of argumentType of argument
HowToConformToJSONNullEnumeration eHowToConformToJSONNullRequired

The argument HowToConformToJSONNull can take the following values.

ValueVBA type
UseNullVariant / NullDefault
UseNullStringString / vbNullString
UseEmptyStringString / ""
UseCustomStringString / Add a string of your choice, like "NA", or "." or ...

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.

When you run the example, you will get this error.

Image showing VBA error when invalid use of null


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.

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.

Choose whatever option you like best.