VBA序列化指定区域的数据

研究了一下VBA序列化JSON，网上没现成的，自己参考人家写的改了一下。

实现功能是，传递一个Range区域的数据，自动生成JSON字符串，行首为列名。

函数如下：

Function GetJSON(myrange)
    Dim returnStr As String
    Dim count As Integer
    Dim colunms As Integer
    count = UBound(myrange, 1)
    colunms = UBound(myrange, 2)
    
    returnStr = "{["
    
    For i = 2 To count
        returnStr = returnStr + "{"
        For j = 1 To colunms
            returnStr = returnStr + """" & myrange(1, j) & """:""" & Replace(myrange(i, j), """", "\""") & """"
            
            If j <> colunms Then
                returnStr = returnStr + ","
            End If
            
            If i = count And j = colunms Then
                returnStr = returnStr + "}"
            ElseIf j = colunms Then
                returnStr = returnStr + "},"
            End If
        Next
    Next
    returnStr = returnStr + "]}"
    GetJSON = returnStr
End Function

使用如下：

Function getFactory()
    getFactory = GetJSON(Range("F1:G5").Value)
End Function

生成结果：

{[{"编号":"1","供应商简称":"aa"},{"编号":"2","供应商简称":"bb"},{"编号":"3","供应商简称":"cc"},{"编号":"4","供应商简称":"dd"}]}