Write an Array of Arrays to a Range VBA

Question

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?

You will need to loop arr and assign each item to the cells. Or better, loop the array of arrays and create a new 2D array then assign it to the cells. — Scott Craner
– Scott Craner, Commented Jul 20, 2022 at 22:09
Also, if the array in arr(i) is 1D and you use Range = arr(i) it will write to one row many columns. If you want to write to rows, you could use Transpose, but if arr is large that will be slow — chris neilsen
– chris neilsen, Commented Jul 20, 2022 at 22:27
We can see that your array is a 1D zero-based array. But what about the arrays inside? Are they all of the same length? Should they be written to the rows or columns of the worksheet? Are they 2D one-based with a variable number of rows ('acquired' from worksheet ranges)? You can share these and any other clarifications by editing your post at any time. — VBasic2008
– VBasic2008, Commented Jul 20, 2022 at 23:50
@VBasic2008 The arrays inside all have the same length. I want my array of arrays to function like a 2 dim array and write to a 2D range in the excel sheet. — Shoebagel
– Shoebagel, Commented Jul 21, 2022 at 15:17
Fyi Posted a direct and widely unknown alternative via Application.Index() coming close to OP's request without the need to loop. — T.M.
– T.M., Commented Jul 21, 2022 at 16:10

T.M. · Accepted Answer · 2022-07-21 16:04:58Z

3

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.

answered Jul 21, 2022 at 16:04

T.M.

10k3 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Scott Craner Over a year ago

As you noted this method, which I did know of, is much slower. When answering on this site that may be seen by others, I tend to create code that works well regardless of size, that way we do not get the next question from a new user that it runs too slow and how can we speed it up. But good job showing something that is more like what the OP thought they wanted.

T.M. Over a year ago

Thanks for feedback - that's why I noted that "it's necessary to provide for identical number of array elements within the container array". - Unrelated side note: It may be instructive to compare a jagged array with a 2-dim array's notation in the VB Editor's Local Window, too. @ScottCraner

Scott Craner Over a year ago

sorry, I missed that note.

chris neilsen Over a year ago

This is a great example of why trying to "avoid looping" is often a bad idea.

chris neilsen Over a year ago

@t.m. my comment was not intended as a criticism, but rather an affirmation of your stance. Too often we see questions asking for a solution "without loops" and answers proudly provided without critical evaluation of their merits. It's refreshing to see someone provide a non loop answer and explain its not the best solution

|

Scott Craner · Accepted Answer · 2022-07-20 22:16:35Z

2

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

answered Jul 20, 2022 at 22:16

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

2 Comments

Shoebagel Over a year ago

Thank you. I would upvote, but I do not have 15 reputation yet.

T.M. Over a year ago

FYi In addition to your (helpful) answer , I posted a direct and widely unknown approach coming close to OP's request without the need to loop. :-) @ScottCraner

VBasic2008 · Accepted Answer · 2022-07-22 12:00:23Z

Write the Values From the Arrays in a Jagged Array to Rows of a Range

The Function

It is assumed that the jagged array is one-dimensional and each of its elements contains a one-dimensional array of any reasonable size or limits.

Function GetJaggedArrayInRows( _
    ByVal sJag As Variant) _
As Variant
   
    ' Write the limits of the source jagged array ('sJag')
    ' to variables ('jLower', 'jUpper').
    Dim jLower As Variant: jLower = LBound(sJag)
    Dim jUpper As Variant: jUpper = UBound(sJag)
    
    ' Calculate the destination number of rows ('drCount').
    Dim drCount As Long: drCount = jUpper - jLower + 1
    
    ' Define a 2D one-based two-column array, the lower-upper array ('luData'),
    ' to populate it with each array's lower and upper limits
    ' in each row ('dr'), and at the same time determine the size of the largest
    ' array i.e. the number of columns of the destination array ('dcCount')
    
    Dim luData() As Long: ReDim luData(1 To drCount, 1 To 2)
    
    Dim j As Long
    Dim dr As Long
    Dim dc As Long
    Dim dcCount As Long
    
    For j = jLower To jUpper
        dr = dr + 1
        luData(dr, 1) = LBound(sJag(j))
        luData(dr, 2) = UBound(sJag(j))
        dc = luData(dr, 2) - luData(dr, 1) + 1
        If dc > dcCount Then dcCount = dc
    Next j
    dr = 0 ' reset to later use in similar fashion ('dr = dr + 1').
    
    ' Define the destination array ('dData').
    Dim dData() As Variant: ReDim dData(1 To drCount, 1 To dcCount)
    
    ' Using the information in the lower-upper array,
    ' write the values from the source jagged array to the destination array.
    
    Dim dcOffset As Long
    
    For j = jLower To jUpper
        dr = dr + 1
        dcOffset = 1 - luData(dr, 1)
        For dc = luData(dr, 1) To luData(dr, 2)
            dData(dr, dc + dcOffset) = sJag(j)(dc)
        Next dc
    Next j
    
    ' Assign the destination array to the result of the function.
    GetJaggedArrayInRows = dData

End Function

Example1

Sub GetJaggedArrayInRowsTEST()

    ' Using the Array function, define and populate a 1D array,
    ' the source jagged array ('sJag') containing three 1D arrays.
    Dim sJag() As Variant
    sJag = Array(Array(1, 2, 3), Array(4, 5, 6, 7), Array(1, 2))

    ' Write the values from each array of the source jagged array
    ' to the rows of a 2D one-based array, the destination array ('dData').
    Dim dData() As Variant: dData = GetJaggedArrayInRows(sJag)
    
    ' Reference the destination worksheet ('dws').
    Dim dws As Worksheet: Set dws = ActiveSheet ' improve!
    
    ' Reference the destination first cell ('dfCell').
    Dim dfCell As Range: Set dfCell = dws.Range("A1")
    
    ' Clear the destination area, the range from the first cell
    ' to the last worksheet cell (in this case 'A1:XFD1048576').
    'dfCell.Resize(dws.Rows.Count - dfCell.Row + 1, _
        dws.Columns.Count - dfCell.Column + 1).Clear
    
    ' Reference the destination range ('drg').
    Dim drg As Range
    Set drg = dfCell.Resize(UBound(dData, 1), UBound(dData, 2))
    
    ' Write the values from the destination array to the destination range.
    drg.Value = dData

End Sub

Result of Both Examples

Example2

Sub GetJaggedArrayInRowsBoundsTEST()

    ' Define and populate three 1D arrays ('Arr1', 'Arr2' ,'Arr3').
    Dim Arr1() As Variant: ReDim Arr1(5 To 7) ' 3 elements
    Dim Arr2() As Variant: ReDim Arr2(2 To 5) ' 4 elements
    Dim Arr3() As Variant: ReDim Arr3(9 To 10) ' 2 elements
    Arr1(5) = 1
    Arr1(6) = 2
    Arr1(7) = 3
    Arr2(2) = 4
    Arr2(3) = 5
    Arr2(4) = 6
    Arr2(5) = 7
    Arr3(9) = 8
    Arr3(10) = 9
    
    ' Define a 1D array of 3 elements, the source jagged array ('sJag')
    ' and populate it with the three arrays.
    Dim sJag() As Variant: ReDim sJag(3 To 5)
    sJag(3) = Arr1
    sJag(4) = Arr2
    sJag(5) = Arr3
    
    ' Write the values from the jagged array
    ' to a 2D one-based array, the destination array ('dData').
    Dim dData() As Variant: dData = GetJaggedArrayInRows(sJag)
    
    ' Reference the destination worksheet ('dws').
    Dim dws As Worksheet: Set dws = ActiveSheet ' improve!
    
    ' Reference the destination first cell ('dfCell').
    Dim dfCell As Range: Set dfCell = dws.Range("A1")
    
    ' Clear the destination area, the range from the first cell
    ' to the last worksheet cell (in this case 'A1:XFD1048576').
    'dfCell.Resize(dws.Rows.Count - dfCell.Row + 1, _
        dws.Columns.Count - dfCell.Column + 1).Clear
    
    ' Reference the destination range ('drg').
    Dim drg As Range
    Set drg = dfCell.Resize(UBound(dData, 1), UBound(dData, 2))
    
    ' Write the values from the destination array to the destination range.
    drg.Value = dData

End Sub

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ · Accepted Answer · 2023-05-27 17:07:25Z

2

With a long delay but after the suggestion of @T.M in one of my answers https://stackoverflow.com/a/76347217/15794828 I will also give to this question what I consider to be a simpler answer. Yes, we can copy an array of arrays to a sheet or by rows or by columns

Option Explicit

Sub arrayOfArraysToRange()
   'Dim arrayOfArrays(1) As Variant
   Dim arrayOfArrays(0 To 1) As Variant, c As Long
   
   'array consumer emulator
   For c = 0 To 1
      arrayOfArrays(c) = getAnArrayExample(c)
   Next
   
   With Application.WorksheetFunction
      'copy to range, each array in master array is a row
      Range("H1").Resize(UBound(arrayOfArrays) + 1, UBound(arrayOfArrays(0)) + 1) = .Transpose(.Transpose(arrayOfArrays))
   
      'copy to range, each array in master array is a column
      Range("N1").Resize(UBound(arrayOfArrays(0)) + 1, UBound(arrayOfArrays) + 1) = .Transpose(arrayOfArrays)
   End With
   
End Sub

Function getAnArrayExample(i As Long) As Variant
   If i = 0 Then
      getAnArrayExample = Array(1, 2, 3, 4, 5)
   Else
      getAnArrayExample = Array(11, 12, 13, 14, 15)
   End If
End Function

answered May 27, 2023 at 17:07

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ

1,6901 gold badge6 silver badges16 bronze badges

2 Comments

T.M. Over a year ago

Appreciate this smart code alternative to my post using transpositions instead of double zero parametrized function Application.Index(arrayOfArrays, 0, 0) +:)

T.M. Over a year ago

Further links fyi: Insert new first column in datafield array...; Shift array columns; Add/Insert a string in array

Collectives™ on Stack Overflow

Write an Array of Arrays to a Range VBA

4 Answers 4

6 Comments

2 Comments

Write the Values From the Arrays in a Jagged Array to Rows of a Range

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Write the Values From the Arrays in a Jagged Array to Rows of a Range

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related