vbaexcel

Excel VBA function to print an array to the workbook


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

Solution

  • 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