Write an Array of Arrays to a Range VBA-CodePudding

I know there is a method for writing the values of a 2 dim array to a range using

Dim arr(r_len, c_len) As Variant

' Fill in arr

range = arr

However, is there a way to accomplish this using an array of arrays? Currently I have

Dim arr(r_len) As Variant
Dim i As Integer

For i = 0 To UBound(arr)
    arr(i) = Function()
Next i

where Function returns an array of length c_length. However, when I try writing to the range, the cells in the excel sheet remain empty. I have tested using the first way and it worked. Is writing to the range only possible using an explicitly 2 dim array?

CodePudding user response：

best is to iterate the array returned by the function and place the values in a 2D array:

Sub kjlkjlkj()
    Dim r_len As Long
    r_len = 10
    
    Dim c_len As Long
    c_len = 10
    
    Dim arr() As Variant
    ReDim arr(1 To r_len, 1 To c_len)
    
    Dim i As Long
    For i = 1 To r_len
        Dim arr2() As Variant
        arr2 = fnct(c_len)
        Dim j As Long
        For j = 1 To c_len
            arr(i, j) = arr2(j)   ((i - 1) * c_len)
        Next j
    Next i
    
    ActiveSheet.Range("A1:J10").Value = arr
End Sub

Function fnct(c_len As Long) As Variant()
    Dim temp() As Variant
    ReDim temp(1 To c_len)
    Dim k As Long
    For k = 1 To c_len
        temp(k) = k
    Next k
    fnct = temp
End Function

CodePudding user response：

It's not so widely known and may be a new point to many contributors that one can execute the Index function upon a jagged array by passing a double zero argument as row and column indices.

   Application.Index(jagged, 0, 0)

Example code

Note that it's necessary to provide for identical number of array elements within the container array (c.f. Shoebagel's comment). Of course you can enter the example data also sequentially.

Sub WriteJagged()
    Dim jagged() As Variant
'a) fill jagged array e.g. with 2 "rows" of sample data (4 columns each row)
    jagged = Array(Array(1, 2, 3, 4), Array(11, 12, 13, 14))
'b) write jagged to target
    Sheet1.Range("A1").Resize(2, 4) = Application.Index(jagged, 0, 0)
End Sub

Caveat

This approach reveals to be time consuming for greater data sets where I'd prefer the way @ScottCraner demonstrated.