VBA code: Show Why JSON Is Incorrect


If you try to put the content of the JSON text into a variable of the type Dictionary, several things could go wrong. After a deserialization, it is good practice to check if it was ok. To do this, you can use the function IsOk. If all is ok, don't worry. If not, you probably want to know why. Was the JSON text incorrect? Was there a bug in the VBA code? Could the data that is in the JSON text not be translated into a VBA variable? For this, we have added the function WhyNotIsOk to the class. If the deserizalization fails, this function will try to tell you why.

Description

The function ShowWhyNotOk will return a description of the error when the deserialization failed.
Image showing mapping the typical flow of the VBA code

If the deserialization is not succesfull, the function ShowWhyNotOk will return a description of the error. The description of the error is a JSON text. It can be deserialized. For example, if the deserialization failed because of an error in the JSON text, the position of the error can be extracted from the description of the error.

Arguments

The function has no arguments.

Return

The function ShowWhyNotOk returns a string. If the deserialization was succesfull, the function will return the empty string "". If the deserialization failed, the string will not be empty.

There are three types of errors.

  • JSON grammar
  • VBA restriction
  • Code

Let's look more in detail on each of these error types.

"JSON grammar" related errors

There is something wrong in the JSON text. If the text follows the standard as defined in The JSON Data Interchange Format, Standard ECMA-404, 1st Edition / October 2013, you should never see these errors. The sub SetHowToConformToJSONGrammar allows to set the level of strictness of the JSON grammar that will be used when deserializing. Changing the level from BeStrict to BeForgiving might do away with the error. Here is a list of all the possible JSON grammar errors.

Error
Expecting , to separate name from value in object.
Expecting , to separate values in array.
Expecting : to separate name and value in name/value pair of object.
Expecting 4 hexadecimal characters in escaped sequence in string.
Expecting a in value false.
Expecting a or A in value false.
Expecting at least one digit before decimal sign position in number.
Expecting at least one digit in exponent of number.
Expecting digit after minus sign.
Expecting digit in number.
Expecting e in value false.
Expecting e in value true.
Expecting e or E in value false.
Expecting e or E in value true.
Expecting escape sequence in name of name/value pair.
Expecting escape sequence in string of name/value pair.
Expecting l in value false.
Expecting l in value null.
Expecting l or L in value false.
Expecting new name/value pair in object.
Expecting r in value true.
Expecting r or R in value true.
Expecting s in value false.
Expecting s or S in value false.
Expecting u in value null.
Expecting u in value true.
Expecting u or U in value true.
Expecting value.
First (non white space) character is incorrect. Expecting { or [.
JSON cannot be empty.
JSON only consists of white space.
Numbers cannot have unnecessary zeroes in front.
Out of characters while extracting array.
Out of characters while extracting decimal part of number.
Out of characters while extracting exponent of number.
Out of characters while extracting name.
Out of characters while extracting name/value pair.
Out of characters while extracting number.
Out of characters while extracting object.
Out of characters while extracting string.
Out of characters while extracting value.
Unnecessary characters after closing JSON.

"VBA restriction" related errors

These errors are due to restrictions in VBA itself. For instance, there is no number format that can take arbitrary large numbers. Often, there will be little you can do and will have to accept that data in the JSON text cannot be exchanged perfectly with VBA. Here is a list of the errors. The notation x represents an element that is dynamicly filled in.

Error
Name x already exists in dictionary.
Number x is not in range of currency and not in range of double.
Number x is not in range of currency.
Number x is not in range of double and not in range of double.
Number x is not in range of double.
Number x is not in range of long and not in range of double.
Number x is not in range of long.

"Code" related errors

These errors are our faults. We hope that you will never come across these errors - although our coding experience gives us little hope. If you would come across one of these errors, please let us know and we will try to fix the bug, and release a new version of the code. Here is a list of the errors.

Error
Impossible path in AddNumber().
Impossible path in ExtractValue().
Unexpected error in AddNumber().
Unexpected error in Deserialize().
Unexpected error in ExtractArray().
Unexpected error in ExtractFalse().
Unexpected error in ExtractNameValuePair().
Unexpected error in ExtractNull().
Unexpected error in ExtractNumber().
Unexpected error in ExtractObject().
Unexpected error in ExtractString().
Unexpected error in ExtractTrue().
Unexpected error in ExtractValue().
Unexpected error in Minimize().
Unexpected error in MinimizeArray().
Unexpected error in MinimizeFalse().
Unexpected error in MinimizeNameValuePair().
Unexpected error in MinimizeNull().
Unexpected error in MinimizeNumber().
Unexpected error in MinimizeObject().
Unexpected error in MinimizeTrue().
Unexpected error in MinimizeValue().

Type of function

The function is a public method of the class cJSON.

Remarks

  • It makes no sense to call the function ShowWhyNotOk before the function Deserialize has been called. If you do so, the function will return the empty string "".
  • If the function IsOk returns true, the reason why the JSON text is invalid can be obtained from the function ShowWhyNotOk.
  • If the strictness of the JSON grammar interpretation is changed using the The sub SetHowToConformToJSONGrammar, a deserialization that fails can, depending on the type of error, be changed into a deserialization that is succesful.

Example 1: Basic error handling

Here is the JSON text to deserialize.

{"Name":"Value"}

Here is the VBA code.

If the deserialization fails, it is good coding practice to handle the error properly.