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 = returnStrEnd Function

使用如下：

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

生成结果：

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