Convert given range in Excel to JSON string

Excel Range To Json.vb

Public Function ToJSON(rng As Range) As String
    Dim checkType ' https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function
    Dim valuePart ' value part of the json
    
    ' Make sure there are two columns in the range
    If rng.Columns.count < 2 Then
        ToJSON = CVErr(xlErrNA)
        Exit Function
    End If

    Dim dataLoop, headerLoop As Long
    ' Get the first row of the range as a header range
    Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).address)
    
    ' We need to know how many columns are there
    Dim colCount As Long: colCount = headerRange.Columns.count
    
    Dim JSON As String: JSON = "["
    
    For dataLoop = 1 To rng.Rows.count
        ' Skip the first row as it's been used as a header
        If dataLoop > 1 Then
            ' Start data row
            Dim rowJson As String: rowJson = "{"
            
            ' Loop through each column and combine with the header
            For headerLoop = 1 To colCount
                rowJson = rowJson & """" & headerRange.Value2(1, headerLoop) & """" & ":"
                
                valuePart = rng.Value2(dataLoop, headerLoop)
                checkType = VarType(valuePart)
                If checkType >= 2 And checkType <= 6 Then
                    rowJson = rowJson & valuePart
                Else
                    rowJson = rowJson & """" & valuePart & """"
                End If
                
                rowJson = rowJson & ","
            Next headerLoop
            
            ' Strip out the last comma
            rowJson = Left(rowJson, Len(rowJson) - 1)
            
            ' End data row
            JSON = JSON & rowJson & "},"
        End If
    Next
    
    ' Strip out the last comma and append ]
    JSON = Left(JSON, Len(JSON) - 1) & "]"
    
    
    
    ToJSON = JSON
End Function


' One | Two |Char
'  1  |  2  | a
' 11  | 22  | aa
'
' =ToJSON(A1:C3)
' [{"one":1,"two":2,"char":"a"},{"one":11,"two":22,"char":"aa"}]