Make SerializeElement work with range().value output multidimensional array

[bmclean-nexamp]

The current code only handles single dimensional, 0 based arrays. The code below that I'm too lazy to PR ( and might want some cleanup ) makes it correctly encode json from a structure with one of the two dimensional 1 based arrays that range returns. Test cases would be nice, etc, but I'm on deadline and can't be bothered :(

        Case Is >= vbArray
            If UBound(vElement) = -1 Then
                .item(.Count) = "[]"
            Else
                .item(.Count) = "[" & vbCrLf
                dimensions = Bounds(vElement).Count
                For i = LBound(vElement) To UBound(vElement)
                    If dimensions > 1 Then ' Note that this works only for 2 dimensions, which is what range returns
                        .item(.Count) = sIndent & "["
                       For j = LBound(vElement, 2) To UBound(vElement, 2)
                            .item(.Count) = sIndent & vbTab
                            SerializeElement vElement(i, j), sIndent & vbTab
                            If Not (j = UBound(vElement, 2)) Then .item(.Count) = ","
                            .item(.Count) = vbCrLf
                        Next
                        .item(.Count) = sIndent & "]"
                        If Not (i = UBound(vElement)) Then .item(.Count) = ","
                    Else
                        .item(.Count) = sIndent & vbTab
                        SerializeElement vElement(i), sIndent & vbTab
                        If Not (i = UBound(vElement)) Then .item(.Count) = ","
                        .item(.Count) = vbCrLf
                    End If
                Next
                .item(.Count) = sIndent & "]"
            End If

and from a StackOverflow answer:

Function Bounds(A As Variant) As Collection
Dim c As New Collection
Dim v As Variant, i As Long

  On Error GoTo exit_function
  i = 1
  Do While True
      v = Array(LBound(A, i), UBound(A, i))
      c.Add v
      i = i + 1
  Loop

exit_function:
Set Bounds = c
End Function

[omegastripes]

@bmclean-nexamp thanks for the input! Support for non 0-based arrays is important. Regarding 2d array - I'm pretty sure that somewhere out of JSON parser scope it's necessary to keep a function, which converts 2d array into nested (jagged) array.

[bmclean-nexamp]

I think that's a reasonable interpretation as well. In my case, I'm getting a rather large range from the spreadsheet, and I prefer to convert it only once to JSON rather than twice into a jagged array, and then to JSON. There's a noticeable multi-second delay in my use case, which is why I added the support to match what the native function returns.

The way I see it, in the current code if you hand it a one dimensional array, it works. If you hand it a two dimensional array, it errors and does nothing. With the change, if you hand it a two dimensional array, it creates the only JSON equivalent that I can imagine. I'd argue that a generic solution that handled N dimensional arrays would also be legitimate for the JSON code to handle, but that would get expensive and complicated, and the Excel VB api does not return arrays larger than 2 dimensions.

It's really a question of if you see two dimensional arrays as a fundamental VBA data type or not, and I argue that because Excel returns them, they must be.