Excel VBA function to print an array to the workbook

Question

I've written a macro that takes a 2 dimensional array, and "prints" it to equivalent cells in an excel workbook.

Is there a more elegant way to do this?

Sub PrintArray(Data, SheetName, StartRow, StartCol)

    Dim Row As Integer
    Dim Col As Integer

    Row = StartRow

    For i = LBound(Data, 1) To UBound(Data, 1)
        Col = StartCol
        For j = LBound(Data, 2) To UBound(Data, 2)
            Sheets(SheetName).Cells(Row, Col).Value = Data(i, j)
            Col = Col + 1
        Next j
            Row = Row + 1
    Next i

End Sub


Sub Test()

    Dim MyArray(1 To 3, 1 To 3)
    MyArray(1, 1) = 24
    MyArray(1, 2) = 21
    MyArray(1, 3) = 253674
    MyArray(2, 1) = "3/11/1999"
    MyArray(2, 2) = 6.777777777
    MyArray(2, 3) = "Test"
    MyArray(3, 1) = 1345
    MyArray(3, 2) = 42456
    MyArray(3, 3) = 60

    PrintArray MyArray, "Sheet1", 1, 1

End Sub

chris neilsen · Accepted Answer · 2011-05-19 23:25:42Z

22

On the same theme as other answers, keeping it simple

Sub PrintArray(Data As Variant, Cl As Range)
    Cl.Resize(UBound(Data, 1), UBound(Data, 2)) = Data
End Sub


Sub Test()
    Dim MyArray() As Variant

    ReDim MyArray(1 To 3, 1 To 3) ' make it flexible

    ' Fill array
    '  ...

    PrintArray MyArray, ActiveWorkbook.Worksheets("Sheet1").[A1]
End Sub

answered May 19, 2011 at 23:25

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

1 Comment

ivan_pozdeev Over a year ago

For OLE Automation's sake, assigning to Range actually means assigning to its .Value.

Lance Roberts · Accepted Answer · 2011-05-19 19:15:39Z

4

Create a variant array (easiest by reading equivalent range in to a variant variable).

Then fill the array, and assign the array directly to the range.

Dim myArray As Variant

myArray = Range("blahblah")

Range("bingbing") = myArray

The variant array will end up as a 2-D matrix.

answered May 19, 2011 at 19:15

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Comments

Codo · Accepted Answer · 2011-05-19 19:17:54Z

3

A more elegant way is to assign the whole array at once:

Sub PrintArray(Data, SheetName, StartRow, StartCol)

    Dim Rng As Range

    With Sheets(SheetName)
        Set Rng = .Range(.Cells(StartRow, StartCol), _
            .Cells(UBound(Data, 1) - LBound(Data, 1) + StartRow, _
            UBound(Data, 2) - LBound(Data, 2) + StartCol))
    End With
    Rng.Value2 = Data

End Sub

But watch out: it only works up to a size of about 8,000 cells. Then Excel throws a strange error. The maximum size isn't fixed and differs very much from Excel installation to Excel installation.

answered May 19, 2011 at 19:17

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Comments

Thom A · Accepted Answer · 2025-05-16 15:07:10Z

As others have suggested, you can directly write a 2-dimensional array into a Range on sheet, however if your array is single-dimensional then you have two options:

Convert your 1D array into a 2D array first, then print it on sheet (as a Range).
Convert your 1D array into a string and print it in a single cell (as a String).

Here is an example depicting both options:

Sub PrintArrayIn1Cell(myArr As Variant, cell As Range)
    cell = Join(myArr, ",")
End Sub
Sub PrintArrayAsRange(myArr As Variant, cell As Range)
    cell.Resize(UBound(myArr, 1), UBound(myArr, 2)) = myArr
End Sub
Sub TestPrintArrayIntoSheet()  '2dArrayToSheet
    Dim arr As Variant
    arr = Split("a  b  c", "  ")

    'Printing in ONE-CELL: To print all array-elements as a single string separated by comma (a,b,c):
    PrintArrayIn1Cell arr, [A1]
    
    'Printing in SEPARATE-CELLS: To print array-elements in separate cells:
    Dim arr2D As Variant
    arr2D = Application.WorksheetFunction.Transpose(arr) 'convert a 1D array into 2D array
    PrintArrayAsRange arr2D, Range("B1:B3")
End Sub

Note: Transpose will render column-by-column output, to get row-by-row output transpose it again - hope that makes sense.

Andrew Strathclyde · Accepted Answer · 2012-08-02 15:18:36Z

1

My tested version

Sub PrintArray(RowPrint, ColPrint, ArrayName, WorkSheetName)

Sheets(WorkSheetName).Range(Cells(RowPrint, ColPrint), _
Cells(RowPrint + UBound(ArrayName, 2) - 1, _
ColPrint + UBound(ArrayName, 1) - 1)) = _
WorksheetFunction.Transpose(ArrayName)

End Sub

answered Aug 2, 2012 at 15:18

Andrew Strathclyde

3374 gold badges10 silver badges23 bronze badges

Comments

Steve Mallory · Accepted Answer · 2011-05-19 19:23:04Z

0

You can define a Range, the size of your array and use it's value property:

Sub PrintArray(Data, SheetName As String, intStartRow As Integer, intStartCol As Integer)

    Dim oWorksheet As Worksheet
    Dim rngCopyTo As Range
    Set oWorksheet = ActiveWorkbook.Worksheets(SheetName)

    ' size of array
    Dim intEndRow As Integer
    Dim intEndCol As Integer
    intEndRow = UBound(Data, 1)
    intEndCol = UBound(Data, 2)

    Set rngCopyTo = oWorksheet.Range(oWorksheet.Cells(intStartRow, intStartCol), oWorksheet.Cells(intEndRow, intEndCol))
    rngCopyTo.Value = Data

End Sub

answered May 19, 2011 at 19:23

Steve Mallory

4,2931 gold badge30 silver badges31 bronze badges

Collectives™ on Stack Overflow

Excel VBA function to print an array to the workbook

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related