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

User · Accepted Answer

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

User · Answer

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

User · Answer

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   HTH

User · Answer

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

User · Answer

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

User · Answer

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

[vba] Excel VBA function to print an array to the workbook

Examples related to vba

Examples related to excel