這是我的 Excel VBA 函式
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 生成集合的集合
我的問題是：如何將其轉換為 aa vba 范圍？
uj5u.com熱心網友回復：
您發布的 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
uj5u.com熱心網友回復：
將集合的集合寫入范圍
- 在這個例子中，每個內部集合都被寫入一行的單元格。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/402976.html
標籤：