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.

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 argument	Type of argument
`HowToConvertJSONNumberToVBA`	Enumeration eHowToConvertJSONNumberToVBA	Required

The argument HowToConvertJSONNumberToVBA can take the following values.

Value	... will convert to VBA type		Conversion strategy
`TryLongTryDoubleElseUseString`	`Long` or `Double` or `String`	Default	First, 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.
`TryLongTryDoubleElseError`	`Long` or `Double`		First, 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 generate an error. This will cause the deserialization to fail.
`TryCurrencyTryDoubleElseError`	`Currency` or `Double`		First, 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.
`TryLongElseError`	`Long`		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 generate an error. This will cause the deserialization to fail.
`TryCurrencyElseError`	`Currency`		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 generate an error. This will cause the deserialization to fail.
`TryDoubleElseError`	`Double`		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.
`UseLong`	`Long`		The 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.
`UseCurrency`	`Currency`		The 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.
`UseDouble`	`Double`		The 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.
`UseString`	`String`		The 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.

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.


	Public Sub Example1()
		
		Dim JSONString As String
		JSONString = "{""Name"":12345}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA TryLongElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                'shows 12345
			MsgBox TypeName(D.Item("Name"))      'Long
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example1()

Dim JSONString As String

JSONString = "{""Name"":12345}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryLongElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name") 'shows 12345

MsgBox TypeName(D.Item("Name")) 'Long

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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

{"Number":123.45}

Here is the VBA code.


	Public Sub Example1Bis()
		
		Dim JSONString As String
		JSONString = "{""Name"":123.45}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA TryLongElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                
			MsgBox TypeName(D.Item("Name"))      
		Else
			MsgBox JSON.ShowWhyNotOk()			'shows {"Error":{"Type":"VBA restriction","Description":"Number 123,45 is not in range of long.","Position":13,"CharacterAtPosition":"5"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example1Bis()

Dim JSONString As String

JSONString = "{""Name"":123.45}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryLongElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number 123,45 is not in range of long.","Position":13,"CharacterAtPosition":"5"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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?


	Public Sub Example1Ter()
		
		Dim JSONString As String
		JSONString = "{""Name"":123.45}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA UseLong
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                'shows 123
			MsgBox TypeName(D.Item("Name"))      'Long		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example1Ter()

Dim JSONString As String

JSONString = "{""Name"":123.45}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA UseLong

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name") 'shows 123

MsgBox TypeName(D.Item("Name")) 'Long Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.

					
	Public Sub Example1Quater ()
		
		Dim JSONString As String
		JSONString = "{""Name"":-2147483649}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA TryLongElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                
			MsgBox TypeName(D.Item("Name"))      
		Else
			MsgBox JSON.ShowWhyNotOk()			'shows {"Error":{"Type":"VBA restriction","Description":"Number -2147483649 is <em>not</em> in range of long.","Position":18,"CharacterAtPosition":"9"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example1Quater ()

Dim JSONString As String

JSONString = "{""Name"":-2147483649}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryLongElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number -2147483649 is not in range of long.","Position":18,"CharacterAtPosition":"9"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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?

	
	Public Sub Example1Quinquies()
		
		Dim JSONString As String
		JSONString = "{""Name"":-2147483649}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA UseLong
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                
			MsgBox TypeName(D.Item("Name"))      
		Else
			MsgBox JSON.ShowWhyNotOk()			'shows {"Error":{"Type":"VBA restriction","Description":"Number -2147483649 is not in range of long.","Position":18,"CharacterAtPosition":"9"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example1Quinquies()

Dim JSONString As String

JSONString = "{""Name"":-2147483649}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA UseLong

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number -2147483649 is not in range of long.","Position":18,"CharacterAtPosition":"9"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.


	Public Sub Example2()
		
		Dim JSONString As String
		JSONString = "{""Name"":123451234512345.1234}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA TryCurrencyElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                'shows 123451234512345,1234
			MsgBox TypeName(D.Item("Name"))      'Currency
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example2()

Dim JSONString As String

JSONString = "{""Name"":123451234512345.1234}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryCurrencyElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name") 'shows 123451234512345,1234

MsgBox TypeName(D.Item("Name")) 'Currency

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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

{"Number":123451234512345.12345}

Here is the VBA code.


	Public Sub Example2Bis()
		
		Dim JSONString As String
		JSONString = "{""Name"":123451234512345.12345}""
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA TryCurrencyElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                
			MsgBox TypeName(D.Item("Name"))      
		Else
			MsgBox JSON.ShowWhyNotOk()			'shows {"Error":{"Type":"VBA restriction","Description":"Number 123451234512345,12345 is not in range of currency.","Position":28,"CharacterAtPosition":"5"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example2Bis()

Dim JSONString As String

JSONString = "{""Name"":123451234512345.12345}""

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryCurrencyElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number 123451234512345,12345 is not in range of currency.","Position":28,"CharacterAtPosition":"5"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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?


	Public Sub Example2Ter()
		
		Dim JSONString As String
		JSONString = "{""Name"":123451234512345.12345}""
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA UseCurrency
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                'shows 123451234512345,1234
			MsgBox TypeName(D.Item("Name"))      'Currency
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example2Ter()

Dim JSONString As String

JSONString = "{""Name"":123451234512345.12345}""

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA UseCurrency

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name") 'shows 123451234512345,1234

MsgBox TypeName(D.Item("Name")) 'Currency

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.

					
	Public Sub Example2Quater ()
		
		Dim JSONString As String
		JSONString = "{""Name"":922337203685477.5808}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA TryCurrencyElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                
			MsgBox TypeName(D.Item("Name"))      
		Else
			MsgBox JSON.ShowWhyNotOk()			'shows {"Error":{"Type":"VBA restriction","Description":"Number 922337203685477,5808 is not in range of currency.","Position":27,"CharacterAtPosition":"8"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example2Quater ()

Dim JSONString As String

JSONString = "{""Name"":922337203685477.5808}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryCurrencyElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number 922337203685477,5808 is not in range of currency.","Position":27,"CharacterAtPosition":"8"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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?

	
	Public Sub Example2Quinquies()
		
		Dim JSONString As String
		JSONString = "{""Name"":922337203685477.5808}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON

		JSON.SetHowToConvertJSONNumberToVBA UseCurrency
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")                
			MsgBox TypeName(D.Item("Name"))      
		Else
			MsgBox JSON.ShowWhyNotOk()			'shows {"Error":{"Type":"VBA restriction","Description":"Number 922337203685477,5808 is not in range of currency.","Position":27,"CharacterAtPosition":"8"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example2Quinquies()

Dim JSONString As String

JSONString = "{""Name"":922337203685477.5808}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA UseCurrency

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number 922337203685477,5808 is not in range of currency.","Position":27,"CharacterAtPosition":"8"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.


	Public Sub Example3()
		
		Dim JSONString As String
		JSONString = "{""Pi"":3.1415926535}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON
		
		JSON.SetHowToConvertJSONNumberToVBA TryDoubleElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Pi")             'shows 3,1415926535
			MsgBox TypeName(D.Item("Pi"))   'shows Double
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example3()

Dim JSONString As String

JSONString = "{""Pi"":3.1415926535}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryDoubleElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Pi") 'shows 3,1415926535

MsgBox TypeName(D.Item("Pi")) 'shows Double

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.


	Public Sub Example3Bis()
		
		Dim JSONString As String
		JSONString = "{""Pi"":3." &amp; _
				"14159265358979323846264338327950288419716939937510" &amp; _
				"58209749445923078164062862089986280348253421170679" &amp; _
				"82148086513282306647093844609550582231725359408128" &amp; _
				"48111745028410270193852110555964462294895493038196" &amp; _
				"44288109756659334461284756482337867831652712019091" &amp; _
				"45648566923460348610454326648213393607260249141273" &amp; _
				"72458700660631558817488152092096282925409171536436" &amp; _
				"78925903600113305305488204665213841469519415116094" &amp; _
				"33057270365759591953092186117381932611793105118548" &amp; _
				"07446237996274956735188575272489122793818301194912" &amp; _
				"98336733624406566430860213949463952247371907021798" &amp; _
				"60943702770539217176293176752384674818467669405132" &amp; _
				"00056812714526356082778577134275778960917363717872" &amp; _
				"14684409012249534301465495853710507922796892589235" &amp; _
				"42019956112129021960864034418159813629774771309960" &amp; _
				"51870721134999999837297804995105973173281609631859" &amp; _
				"50244594553469083026425223082533446850352619311881" &amp; _
				"71010003137838752886587533208381420617177669147303" &amp; _
				"59825349042875546873115956286388235378759375195778" &amp; _
				"18577805321712268066130019278766111959092164201989}"
				
		Dim JSON As cJSON
		Set JSON = New cJSON
		
		JSON.SetHowToConvertJSONNumberToVBA TryDoubleElseError
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Pi")
			MsgBox TypeName(D.Item("Pi"))
		Else
			MsgBox JSON.ShowWhyNotOk()	<span class="cVBAResult">'shows {"Error":{"Type":"VBA restriction","Description":"Number 3,14159265358[etc] is not in range of double.","Position":1008,"CharacterAtPosition":"9"}}</span>
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example3Bis()

Dim JSONString As String

JSONString = "{""Pi"":3." & _

"14159265358979323846264338327950288419716939937510" & _

"58209749445923078164062862089986280348253421170679" & _

"82148086513282306647093844609550582231725359408128" & _

"48111745028410270193852110555964462294895493038196" & _

"44288109756659334461284756482337867831652712019091" & _

"45648566923460348610454326648213393607260249141273" & _

"72458700660631558817488152092096282925409171536436" & _

"78925903600113305305488204665213841469519415116094" & _

"33057270365759591953092186117381932611793105118548" & _

"07446237996274956735188575272489122793818301194912" & _

"98336733624406566430860213949463952247371907021798" & _

"60943702770539217176293176752384674818467669405132" & _

"00056812714526356082778577134275778960917363717872" & _

"14684409012249534301465495853710507922796892589235" & _

"42019956112129021960864034418159813629774771309960" & _

"51870721134999999837297804995105973173281609631859" & _

"50244594553469083026425223082533446850352619311881" & _

"71010003137838752886587533208381420617177669147303" & _

"59825349042875546873115956286388235378759375195778" & _

"18577805321712268066130019278766111959092164201989}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA TryDoubleElseError

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Pi")

MsgBox TypeName(D.Item("Pi"))

Else

MsgBox JSON.ShowWhyNotOk() 'shows {"Error":{"Type":"VBA restriction","Description":"Number 3,14159265358[etc] is not in range of double.","Position":1008,"CharacterAtPosition":"9"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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?


	Public Sub Example3Ter()
		
		Dim JSONString As String
		JSONString = "{""Pi"":3." &amp; _
				"14159265358979323846264338327950288419716939937510" &amp; _
				"58209749445923078164062862089986280348253421170679" &amp; _
				"82148086513282306647093844609550582231725359408128" &amp; _
				"48111745028410270193852110555964462294895493038196" &amp; _
				"44288109756659334461284756482337867831652712019091" &amp; _
				"45648566923460348610454326648213393607260249141273" &amp; _
				"72458700660631558817488152092096282925409171536436" &amp; _
				"78925903600113305305488204665213841469519415116094" &amp; _
				"33057270365759591953092186117381932611793105118548" &amp; _
				"07446237996274956735188575272489122793818301194912" &amp; _
				"98336733624406566430860213949463952247371907021798" &amp; _
				"60943702770539217176293176752384674818467669405132" &amp; _
				"00056812714526356082778577134275778960917363717872" &amp; _
				"14684409012249534301465495853710507922796892589235" &amp; _
				"42019956112129021960864034418159813629774771309960" &amp; _
				"51870721134999999837297804995105973173281609631859" &amp; _
				"50244594553469083026425223082533446850352619311881" &amp; _
				"71010003137838752886587533208381420617177669147303" &amp; _
				"59825349042875546873115956286388235378759375195778" &amp; _
				"18577805321712268066130019278766111959092164201989}"
				
		Dim JSON As cJSON
		Set JSON = New cJSON
			
		<span class="cVBAToNote">JSON.SetHowToConvertJSONNumberToVBA UseDouble</span>
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Pi")                 <span class="cVBAResult">shows 3,14159265358[etc]</span>
			MsgBox TypeName(D.Item("Pi"))       <span class="cVBAResult">shows String</span>
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example3Ter()

Dim JSONString As String

JSONString = "{""Pi"":3." & _

"14159265358979323846264338327950288419716939937510" & _

"58209749445923078164062862089986280348253421170679" & _

"82148086513282306647093844609550582231725359408128" & _

"48111745028410270193852110555964462294895493038196" & _

"44288109756659334461284756482337867831652712019091" & _

"45648566923460348610454326648213393607260249141273" & _

"72458700660631558817488152092096282925409171536436" & _

"78925903600113305305488204665213841469519415116094" & _

"33057270365759591953092186117381932611793105118548" & _

"07446237996274956735188575272489122793818301194912" & _

"98336733624406566430860213949463952247371907021798" & _

"60943702770539217176293176752384674818467669405132" & _

"00056812714526356082778577134275778960917363717872" & _

"14684409012249534301465495853710507922796892589235" & _

"42019956112129021960864034418159813629774771309960" & _

"51870721134999999837297804995105973173281609631859" & _

"50244594553469083026425223082533446850352619311881" & _

"71010003137838752886587533208381420617177669147303" & _

"59825349042875546873115956286388235378759375195778" & _

"18577805321712268066130019278766111959092164201989}"

Dim JSON As cJSON

Set JSON = New cJSON

JSON.SetHowToConvertJSONNumberToVBA UseDouble

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Pi") shows 3,14159265358[etc]

MsgBox TypeName(D.Item("Pi")) shows String

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.


	Public Sub Example3Quater()
		
		Dim JSONString As String
		JSONString = "{""Pi"":3." &amp; _
				"14159265358979323846264338327950288419716939937510" &amp; _
				"58209749445923078164062862089986280348253421170679" &amp; _
				"82148086513282306647093844609550582231725359408128" &amp; _
				"48111745028410270193852110555964462294895493038196" &amp; _
				"44288109756659334461284756482337867831652712019091" &amp; _
				"45648566923460348610454326648213393607260249141273" &amp; _
				"72458700660631558817488152092096282925409171536436" &amp; _
				"78925903600113305305488204665213841469519415116094" &amp; _
				"33057270365759591953092186117381932611793105118548" &amp; _
				"07446237996274956735188575272489122793818301194912" &amp; _
				"98336733624406566430860213949463952247371907021798" &amp; _
				"60943702770539217176293176752384674818467669405132" &amp; _
				"00056812714526356082778577134275778960917363717872" &amp; _
				"14684409012249534301465495853710507922796892589235" &amp; _
				"42019956112129021960864034418159813629774771309960" &amp; _
				"51870721134999999837297804995105973173281609631859" &amp; _
				"50244594553469083026425223082533446850352619311881" &amp; _
				"71010003137838752886587533208381420617177669147303" &amp; _
				"59825349042875546873115956286388235378759375195778" &amp; _
				"18577805321712268066130019278766111959092164201989}"
				
		Dim JSON As cJSON
		Set JSON = New cJSON
		
		Dim D As Dictionary
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Pi")                 <span class="cVBAResult">shows 3,14159265358979</span>
			MsgBox TypeName(D.Item("Pi"))       <span class="cVBAResult">shows Double</span>
		Else
			MsgBox JSON.ShowWhyNotOk()
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example3Quater()

Dim JSONString As String

JSONString = "{""Pi"":3." & _

"14159265358979323846264338327950288419716939937510" & _

"58209749445923078164062862089986280348253421170679" & _

"82148086513282306647093844609550582231725359408128" & _

"48111745028410270193852110555964462294895493038196" & _

"44288109756659334461284756482337867831652712019091" & _

"45648566923460348610454326648213393607260249141273" & _

"72458700660631558817488152092096282925409171536436" & _

"78925903600113305305488204665213841469519415116094" & _

"33057270365759591953092186117381932611793105118548" & _

"07446237996274956735188575272489122793818301194912" & _

"98336733624406566430860213949463952247371907021798" & _

"60943702770539217176293176752384674818467669405132" & _

"00056812714526356082778577134275778960917363717872" & _

"14684409012249534301465495853710507922796892589235" & _

"42019956112129021960864034418159813629774771309960" & _

"51870721134999999837297804995105973173281609631859" & _

"50244594553469083026425223082533446850352619311881" & _

"71010003137838752886587533208381420617177669147303" & _

"59825349042875546873115956286388235378759375195778" & _

"18577805321712268066130019278766111959092164201989}"

Dim JSON As cJSON

Set JSON = New cJSON

Dim D As Dictionary

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Pi") shows 3,14159265358979

MsgBox TypeName(D.Item("Pi")) shows Double

Else

MsgBox JSON.ShowWhyNotOk()

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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.

					
	Public Sub Example3Quinquies()
		
		Dim JSONString As String
		JSONString = "{""Name"":999E+999}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON
		
		Dim D As Dictionary
		JSON.SetHowToConvertJSONNumberToVBA TryDoubleElseError
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")
			MsgBox TypeName(D.Item("Name"))
		Else
			MsgBox JSON.ShowWhyNotOk()          'show {"Error":{"Type":"VBA restriction","Description":"Number 999e+999 is not in range of double.","Position":15,"CharacterAtPosition":"9"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example3Quinquies()

Dim JSONString As String

JSONString = "{""Name"":999E+999}"

Dim JSON As cJSON

Set JSON = New cJSON

Dim D As Dictionary

JSON.SetHowToConvertJSONNumberToVBA TryDoubleElseError

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'show {"Error":{"Type":"VBA restriction","Description":"Number 999e+999 is not in range of double.","Position":15,"CharacterAtPosition":"9"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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?

					
	Public Sub Example3Sexies()
	  		
		Dim JSONString As String
		JSONString = "{""Name"":999E+999}"
		
		Dim JSON As cJSON
		Set JSON = New cJSON
		
		Dim D As Dictionary
		JSON.SetHowToConvertJSONNumberToVBA UseDouble
		Set D = JSON.Deserialize(JSONString)
		If (JSON.IsOk()) Then
			MsgBox D.Item("Name")
			MsgBox TypeName(D.Item("Name"))
		Else
			MsgBox JSON.ShowWhyNotOk()          'show {"Error":{"Type":"VBA restriction","Description":"Number 999e+999 is not in range of double.","Position":15,"CharacterAtPosition":"9"}}
		End If
		Set D = Nothing
		
		Set JSON = Nothing
		
	End Sub

Public Sub Example3Sexies()

Dim JSONString As String

JSONString = "{""Name"":999E+999}"

Dim JSON As cJSON

Set JSON = New cJSON

Dim D As Dictionary

JSON.SetHowToConvertJSONNumberToVBA UseDouble

Set D = JSON.Deserialize(JSONString)

If (JSON.IsOk()) Then

MsgBox D.Item("Name")

MsgBox TypeName(D.Item("Name"))

Else

MsgBox JSON.ShowWhyNotOk() 'show {"Error":{"Type":"VBA restriction","Description":"Number 999e+999 is not in range of double.","Position":15,"CharacterAtPosition":"9"}}

End If

Set D = Nothing

Set JSON = Nothing

End Sub

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

All about the use of JSON in Office applications

All about the use of JSON in Office applications

VBA code: Set How To Convert JSON Number To VBA

The method `SetHowToConvertJSONNumberToVBA`

Description

Syntax

Arguments

Type of function

Remarks

Example 1: Conversion to `Long`

Example 2: Conversion to `Currency`

Example 3: Conversion to `Double`

The method SetHowToConvertJSONNumberToVBA

Description

Syntax

Arguments

Type of function

Remarks

Example 1: Conversion to Long

Example 2: Conversion to Currency

Example 3: Conversion to Double

The method `SetHowToConvertJSONNumberToVBA`

Example 1: Conversion to `Long`

Example 2: Conversion to `Currency`

Example 3: Conversion to `Double`