将集合集合转换为范围

Question

这是我的Excel函数

Function make_range()
    Dim the_json As String
    the_json = "[[1,2,3][4,5,6]]"
    Set the_collection = JsonConverter.ParseJson(the_json)
    make_range = 'question: how to convert the collection to range?
End Function

函数使用JsonConverter.ParseJson生成集合集合。

我的问题是:如何将其转换为vba范围？

Answer 1

发布的JSON无效(在两个内部数组之间缺少一个逗号)。您不能从头开始创建一个范围，只能参考工作表上现有的范围。

也许你想让你的函数返回一个二维数组？

Sub TestJsonToArray()
    Dim arr
    arr = JsonToArray("[[1,2,3],[4,5,6],[7,8,9]]")
    ActiveSheet.Range("B4").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub


Function JsonToArray(json As String)
    Dim col As Collection, arr, r As Long, c As Long, nc As Long
    Set col = JsonConverter.ParseJson(json)
    nc = col(1).Count 'assumes all inner collections are the same size...
    ReDim arr(1 To col.Count, 1 To nc)
    For r = 1 To col.Count
        For c = 1 To nc
            arr(r, c) = col(r)(c)
        Next c
    Next r
    JsonToArray = arr
End Function

Answer 2

将集合的集合写入范围

在本例中，每个内部集合都写入一行的单元格.。

Option Explicit

Sub CollOfCollsToRange()

    Const dwsName As String = "Sheet1"
    Const dfCellAddress As String = "A1"
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim dws As Worksheet: Set dws = wb.Worksheets(dwsName)
    Dim dCell As Range: Set dCell = dws.Range(dfCellAddress)
    
    Dim Json As String: Json = "[[1,2,3][4,5,6]]"
    Dim Coll As Collection: Set Coll = JsonConverter.ParseJson(Json)
    Dim Arr As Variant: Arr = JagCollOfColls(Coll)
    
    Dim r As Long
    For r = 1 To UBound(Arr)
        dCell.Resize(, UBound(Arr(r))).Value = Arr(r)
        Set dCell = dCell.Offset(1)
    Next r
    
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the collections of a collection in arrays of an array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function JagCollOfColls( _
    ByVal Coll As Collection) _
As Variant
    
    Dim oArr As Variant: ReDim oArr(1 To Coll.Count)
    
    Dim iArr As Variant, oItem As Variant, iItem As Variant
    Dim o As Long, i As Long
    
    For Each oItem In Coll
        o = o + 1
        i = 0
        ReDim iArr(1 To oItem.Count)
        For Each iItem In oItem
            i = i + 1
            iArr(i) = iItem
        Next iItem
        oArr(o) = iArr
    Next oItem

    JagCollOfColls = oArr

End Function