VBA code: Set How To Convert JSON Number To VBA


The number format in a JSON text allows for all kind of numbers: from basic numbers to arbitrarily large and arbitrarily precise numbers. Because of that, it is not sure that each number in your JSON text can be fitted into one of the number types of VBA. There are different strategies to try to cast the JSON text numbers into a VBA number. There is no universally right strategy. It depends on the kind of numbers in the JSON text and on what you want to do with them afterwards in your VBA code. The class offers different number conversion strategies by means the method SetHowToConvertJSONNumberToVBA.

The method SetHowToConvertJSONNumberToVBA

Description

The sub SetHowToConvertJSONNumberToVBA defines the set of VBA types that will be used when converting a JSON number when deserializing.

Image showing mapping the typical flow of the VBA code: convert JSON number

The only thing that the sub SetHowToConvertJSONNumberToVBA does is set a parameter in the class cJSON. The parameter is used when deserializing the argument JSONText of Deserialize.

On page ii of the standard The JSON Data Interchange Format, Standard ECMA-404, 1st Edition / October 2013 the following is written.

"JSON is agnostic about numbers. In any programming language, there can be a variety of number types of various capacities and complements, fixed or floating, binary or decimal. That can make interchange between different programming languages difficult. JSON instead offers only the representation of numbers that humans use: a sequence of digits. All programming languages know how to make sense of digit sequences even if they disagree on internal representations. That is enough to allow interchange."

The sub SetHowToConvertJSONNumberToVBA determines how the JSON numbers will be translated into VBA numbers. There are multiple VBA number types, each having its own characteristics.

Be careful. Number conversion can be quite tricky.

Syntax

Public Sub SetHowToConvertJSONNumberToVBA(ByVal HowToConvertJSONNumberToVBA As eHowToConvertJSONNumberToVBA)

Arguments

Name of argumentType of argument
HowToConvertJSONNumberToVBAEnumeration eHowToConvertJSONNumberToVBARequired

The argument HowToConvertJSONNumberToVBA can take the following values.

Value... will convert to VBA typeConversion strategy
TryLongTryDoubleElseUseStringLong or Double or StringDefaultFirst, the code will try to convert to Long using CLng. The code will check if no precision was lost during the conversion to Long. If precision is lost or if the conversion fails, the code will try to convert to Double using CDbl. The code will again check if no precision was lost during the conversion to Double. If precision is lost or if the conversion fails, the code will convert to String. Hence, this option will never generate an error. The deserialization cannot fail because of the number conversion.
TryLongTryDoubleElseErrorLong or DoubleFirst, the code will try to convert to Long using CLng. The code will check if no precision was lost during the conversion to Long. If precision is lost or if the conversion fails, the code will try to convert to Doubleusing CDbl. The code will again check if no precision was lost during the conversion to Double. If precision is lost or if the conversion fails, the code will generate an error. This will cause the deserialization to fail.
TryCurrencyTryDoubleElseErrorCurrency or DoubleFirst, the code will try to convert to Currency using CCur. The code will check if no precision was lost during the conversion to Currency. If precision is lost or if the conversion fails, the code will try to convert to Double using CDbl. The code will check if no precision was lost during the conversion to Double. If precision is lost or if the conversion fails, the code will generate an error. This will cause the deserialization to fail.
TryLongElseErrorLongThe code will try to convert to Long using CLng. The code will check if no precision was lost during the conversion to Long. If precision is lost or if the conversion fails, the code will generate an error. This will cause the deserialization to fail.
TryCurrencyElseErrorCurrencyThe code will try to convert to Currency using CCur. The code will check if no precision was lost during the conversion to Currency. If precision is lost or if the conversion fails, the code will generate an error. This will cause the deserialization to fail.
TryDoubleElseErrorDoubleThe code will try to convert to Double using CDbl. The code will check if no precision was lost during the conversion to Double. If precision is lost or if the conversion fails, the code will generate an error. This will cause the deserialization to fail.
UseLongLongThe code will try to convert to Long using CLng. The code will not check if precision was lost during the conversion to Long. If the conversion fails, the code will generate an error. This will cause the deserialization to fail.
UseCurrencyCurrencyThe code will try to convert to Currency using CCur. The code will not check if precision was lost during the conversion to Currency. If the conversion fails, the code will generate an error. This will cause the deserialization to fail.
UseDoubleDoubleThe code will try to convert to Double using CDbl. The code will not check if precision was lost during the conversion to Double. If the conversion fails, the code will generate an error. This will cause the deserialization to fail.
UseStringStringThe code will convert to String. Hence, this option will never generate an error. The deserialization cannot fail because of the number conversion.

The approach is summarized in the following picture.

Image showing mapping the number conversion approach

Type of function

The sub is a public method of the class cJSON.

Remarks

  • We have though whether or not to use CLng. Wanted to use the standard functions. The aim is not to "improve" VBA, if this would be possible.
  • The code will guess the decimal sign by doing evaluating CDbl("1.5") = 15. In our Excel, the decimal sign is a comma.

Example 1: Conversion to Long

Here is the JSON text to deserialize. We will try to convert the JSON number to Long.

{"Number":12345}

Here is the VBA code.

The deserialization is ok. Now let's try to convert this JSON number to Long.

{"Number":123.45}

Here is the VBA code.

The deserialization fails. This seems logical as the JSON number has a decimal part. However, it is less obvious than it may seem. Note that the conversion Clng("123,45") did not fail. It yields 123. It is the precision check that generates the error, not the conversion itself. The code has detected that the conversion leads to a loss of precision. Because of this precision loss, an error was generated.

What would be the difference with the option UseLong?

The deserialization is ok now! Do you see why? The reason is that the conversion Clng("123,45") worked - even though it may not have yield the expected result - and the option UseLong skips the precision check. Hence, the code does not detect the loss of digits and does not generate an error. The deserialization works even though the data exchange between the JSON text and VBA is not perfect.

Now let's try this.

{"Number":-2147483649}

The range of Long in VBA are integer numbers in {-2147483648,...,-1,0,1,...,2147483647}. Hence, the JSON number does not fit into the range of Long.

The deserialization fails. The reason is that the conversion Clng("123,45") failed, which generated an error.

Would there be a difference when you use the option UseLong?

No, there is no difference. Again, the deserialization fails because the conversion Clng("123,45") failed.

Example 2: Conversion to Currency

Here is the JSON text to deserialize. We will try to convert the JSON number to Currency.

{"Number":123451234512345.1234}

Here is the VBA code.

The deserialization is ok. Now let's try to convert this JSON number to Currency.

{"Number":123451234512345.12345}

Here is the VBA code.

The deserialization fails. This seems logical as the JSON number has a decimal part that consist of more than 4 digits. However, it is less obvious than it may seem. Note that the conversion CCurr("123451234512345,12345") did not fail. It yields 123451234512345,1234, loosing only the last digit. It is the precision check that generates the error, not the conversion itself. The code has detected that the conversion leads to a loss of precision. Because of this precision loss, an error was generated.

What would be the difference with the option UseCurrency?

The deserialization is ok now! Do you see why? The reason is that the conversion CCurr("123451234512345,12345") worked - even though it may not have yield the expected result - and the option UseCurrency skips the precision check. Hence, the code does not detect the loss of the last digit and does not generate an error. The deserialization works even though the data exchange between the JSON text and VBA is not perfect.

Now let's try this.

{"Number":922337203685477.5808}

The range of Currency in VBA are decimal numbers with maximum 4 digits in the decimal part in {-922337203685477.5808,...,-0.0001,0,0.0001,...,922337203685477.5807}. Hence, the JSON number does not fit into the range of Currency.

The deserialization fails. The reason is that the conversion CCurr("922337203685477,5808") failed, which generated an error.

Would there be a difference when you use the option UseLong?

No, there is no difference. Again, the deserialization fails because the conversion CCurr("922337203685477,5808") failed.

Example 3: Conversion to Double

Here is the JSON text to deserialize. It contains pi with a precision of 10 digits.

{"Pi":3.141592653}

Here is the VBA code.

The deserialization is ok. Now let's try add some digits to pi.

{"Pi":3.14159265358979323846264338327950288419716939937510
58209749445923078164062862089986280348253421170679
82148086513282306647093844609550582231725359408128
48111745028410270193852110555964462294895493038196
44288109756659334461284756482337867831652712019091
45648566923460348610454326648213393607260249141273
72458700660631558817488152092096282925409171536436
78925903600113305305488204665213841469519415116094
33057270365759591953092186117381932611793105118548
07446237996274956735188575272489122793818301194912
98336733624406566430860213949463952247371907021798
60943702770539217176293176752384674818467669405132
00056812714526356082778577134275778960917363717872
14684409012249534301465495853710507922796892589235
42019956112129021960864034418159813629774771309960
51870721134999999837297804995105973173281609631859
50244594553469083026425223082533446850352619311881
71010003137838752886587533208381420617177669147303
59825349042875546873115956286388235378759375195778
18577805321712268066130019278766111959092164201989}

Here is the VBA code.

The deserialization fails. Thousand decimal digits is too much for Double. Still, the failure is less obvious than it may seem. The conversion CDbl("3,14159265358[etc]") did not fail. It yields 3,14159265358979. It is the precision check that generates the error, not the conversion itself. The code has detected that the conversion leads to a loss of precision. Because of this precision loss, an error was generated.

What would be the difference with the option UseDouble?

Now, the deserialization is succesful. Do you see why? The reason is that the conversion CDbl("3,14159265358[etc]") worked - even though it may not have yield the expected result - and the option UseDouble skips the precision check. Hence, the code does not detect the loss of digits and does not generate an error. The deserialization works even though the data exchange between the JSON text and VBA is not perfect. Most digits of pi are lost.

Depending on your pruposes, this behavior might be ok. For instance, imagine that the data exchange between the JSON text and VBA consists of a lot of numbers, most of which can be flawlessly converted to Double. It may be quite annoying to see the deserialization as a whole fail because of a limited, and maybe ignorable, set of JSON numbers that are annoying to convert.

Let's now try the default option of the code.

The default option of the VBA code is TryLongTryDoubleElseUseString. First, the code tried to fit the JSON number into a variable of the type Long. It didn't work. Then, the code tried to fit the JSON number into a variable of the type Double. The function CDbl will make a conversion, to 3,14159265358979. A lot of precision is lost. Because of this loss of precision, the code will consider that the conversion to the type Double failed. Last, the code will put the JSON number into the a variable of the type String. The deserialization will not fail. However, it is then up to you to see how to handle this in your VBA code.

Now let's try a number that is out of the range of Double. The range goes from -1.79769313486231E+308 to 1.79769313486231E+308, but only the numbers that can be fit into the Double format defined by IEEE are in the range.

The deserialization fails. The reason is that the conversion CDbl("999E+999") failed, which generated an error.

Would there be a difference when you use the option UseDouble?

No, there is no difference. Again, the deserialization fails because the conversion CDbl("999E+999") failed.