Function ConvertToJson work not correctly when the string contain continuous zero

[badoyau]

Hi, Thank for your library. it is a useful library for my project. i have one item as string in excel cells as 0012020000000000 but when i use dictionary call final and export as json by json1 = JsonConverter.ConvertToJson(Final, Whitespace:=2) it will return in differenct types of results. SO i try many cases such as following

0012020000000000 , then it return 0012020000000000 as integer, instead "0012020000000000"
001202000000000 , then it work perfectly as "001202000000000" 001202000000000A , then it work perfectly as "001202000000000A"

please kindly check this bug for many thanks!

[houghtonap]

VBA-JSON depends on how you format your Excel cells to determine the datatype to assign in JSON. If you don't specify a format for a cell, then it is General which essentially will be format Number when entering 0012020000000000. Here is an example. I opened a new blank workbook. On Sheet1 I formatted cell A1 to General, cell B1 to Number with zero decimal places and cell C1 to Text. Then added a new module with the VBA-JSON code and another module with the following code:

Public Sub test()

  Dim Worksheet As Excel.Worksheet
  Dim jsonArray As New VBA.Collection
  Dim json As String

  Set Worksheet = ActiveSheet

  jsonArray.Add Worksheet.Range("A1").Value ' Cell formated as General.
  jsonArray.Add Worksheet.Range("B1").Value ' Cell formatted as Number, zero decimal places.
  jsonArray.Add Worksheet.Range("C1").Value ' Cell formatted as Text.

  json = ConvertToJson(jsonArray, Whitespace:=2)

  Debug.Print json
  Exit Sub
End Sub

This code will print the following the Developer Immediate window in the VBA project environment:

[
  12020000000000,
  12020000000000,
  "0012020000000000"
]

So what I suspect your issue is, is that you have not appropriately set the format for cell in question and this is not a bug.

Hope that helps.

[houghtonap]

I noticed that your GitHub account had a repository with a workbook in it that contains some example data. I downloaded that and did a little digging into the VBA-JSON code which indicates in ConvertToJson:

    Case VBA.vbString
        ' String (or large number encoded as string)
        If Not JsonOptions.UseDoubleForLargeNumbers And json_StringIsLargeNumber(JsonValue) Then
            ConvertToJson = JsonValue
        Else
            ConvertToJson = """" & json_Encode(JsonValue) & """"
        End If

Your cells in column C have 0012020000000000 which VBA-JSON considers a large number. The default for JsonOptions.UseDoubleForLargeNumbers is FALSE thus the condition in the code makes it TRUE and the code will return it as a JSON number and not a string. I"m not sure why it didn't do this in the example I presented above, but it didn't... The solution is to change the VBA-JSON option JsonOptions.UseDoubleForLargeNumbers to TRUE in your code. Here is an updated example using the data from your workbook:

Public Sub test()

  Dim Worksheet As Excel.Worksheet
  Dim jsonArray As New VBA.Collection
  Dim json As String

  Set Worksheet = ActiveSheet

  jsonArray.Add Worksheet.Range("C2").Value
  jsonArray.Add Worksheet.Range("C3").Value
  jsonArray.Add Worksheet.Range("C4").Value

  JsonOptions.UseDoubleForLargeNumbers = True

  json = ConvertToJson(jsonArray, Whitespace:=2)

  Debug.Print json
  Exit Sub
End Sub

which produces the following the Developer Immediate window in the VBA project environment:

[
  "0012020000000000",
  "0012020000000000",
  "0012020000000000"
]

Your issue doesn't appear to be a bug, but you stumbled upon a feature!!

[badoyau]

Hi houghtonap, Many thanks for your kindly help! After revise the LargeNumbers setting to True JsonOptions.UseDoubleForLargeNumbers = True Issue is fixed perfectly! Yes, as you said this is not a bug. i also found the detail decription from the code remark

VBA only stores 15 significant digits, so any numbers larger than that are truncated ' This can lead to issues when BIGINT's are used (e.g. for Ids or Credit Cards), as they will be invalid above 15 digits ' See: http://support.microsoft.com/kb/269370 ' ' By default, VBA-JSON will use String for numbers longer than 15 characters that contain only digits ' to override set JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True UseDoubleForLargeNumbers As Boolean

Really thanks for your kindly reply~!

[Nick-vanGemeren]

There are a number of open issues aboutUseDoubleForLargeNumbersandjson_StringIsLargeNumber(including #66 by @timhall ) and reading/writing ECMA-404 numbers. The string 0012020000000000 is not valid as an ECMA number (has leading zeros) and should always be written as ECMA string. But that should be the default in any case. I agree that settingUseDoubleForLargeNumbers=Truewill solve the immediate issue, but I want to get my head round the justification for this option 'fix' and maybe make some better code.

BTW note that there is a small performance issue with thatif ... and ...condition. VBA does not do logical shortcuts. Sojson_StringIsLargeNumberwill always be called for every string converted.

[houghtonap]

I agree with @Nick-vanGemeren about UseDoubleForLargeNumbers and it is unclear from the comments why this option was created and why it is not the default. My only guess is that the VBA runtime might take very large integer constants and convert them to variant strings, hence the reason behind the option, but I would need to investigate that and my focus was helping @badoyau move forward.

However, the bigger underlying question for me is why my test code was successful using the same string as @badoyau, but it failed in the workbook @badoyau was using. It wasn't until I downloaded @badoyau workbook that I could see it failing. My environment is Windows 10, Excel 2016 and I was using VBA-JSON v2.3.0. However, the code, in this area, looks the same between v2.3.0 and the current version.