In excel VBA, how do i write a custom function to convert a range to json array?

I have only the function signature for now:

Function to_json(input As Range)

End Function

I need to convert the input to a json array in the following former

[
  [4,"foo"],
  ["bar",5]
]

how do i it?

The end goal it to post the json to a web service, get response in a similar format and return it as range object

Edit: the input range can include any combination of of numbers and strings. I need to copy these to a 2 dimensional json array as shown in the example. note that i need json array and not json objects.

i googled for a while, but couldn't find any obvious answer

edit2: in js, the answer is very simple: JSON.stringify(input), is there a simple vba equivalent?

CodePudding user response：

For a comprehensive solution use JsonConverter but for a simple specific case try

Function to_json(rng As Range)
    Dim ar, v, i As Long, j As Long, qq As String
    Dim js As String, r As String
    qq = Chr(34)
   
    ar = rng.Value
    For i = 1 To UBound(ar)
        r = "["
        For j = 1 To UBound(ar, 2)
            v = ar(i, j)
            'Debug.Print i, j, v, VarType(v)
            Select Case VarType(v)
                Case vbError
                    v = ""
                Case vbNull, vbEmpty
                    v = "null"
                Case vbDate
                    v = qq & Format(v, "yyyy-mm-dd") & qq
                Case vbString
                    v = qq & Replace(v, qq, "\" & qq) & qq
                Case Else
            End Select
            If Len(r) > 1 Then r = r & ","
            r = r & v
        Next
        r = r & "]"
        If Len(js) > 0 Then js = js & ","
        js = js & r
   Next
   to_json = "[" & js & "]"
End Function

CodePudding user response：

Thanks, all, for the referral to github.com/VBA-tools/VBA-JSON. here is the complete solution for future reference:

1. install JsonConverter as described in https://github.com/VBA-tools/VBA-JSON
2. complete the code (note the .value):,

Function to_json(input As Range)
  to_json = JsonConverter.ConvertToJson(input.Value)
End Function