Printing matrix content to worksheet

Question

I consulted you guys yesterday with a very vague question. I have now managed to isolate the problem, but obviously not solved it as I am writing here.

The problem for me is to assign a variable the value/content of matrix (or variant of variant). Not sure if this is redundant, but I want to have something like the following in my spreadsheet:

   A   B   C   D   E   F
1  a   b   c   d      
2  e   f   g   h
3  aa  bb  cc  dd
4  ee  ff  gg  hh

Here is the code:

Public Sub Test()

    Dim sub_data As Variant
    Dim sheet_name As String
    Dim str As String
    Dim rng As Range

    sheet_name = "Sheet1"
    Set rng = Sheets(sheet_name).Range("A1")
    Worksheets(sheet_name).Cells.ClearContents

    On Error Resume Next

    str = "A" & CStr(print_row)

    ReDim sub_data(0 To 1, 0 To 1, 0 To 3)

    sub_data(0, 0, 0) = "a"
    sub_data(0, 0, 1) = "b"
    sub_data(0, 0, 2) = "c"
    sub_data(0, 0, 3) = "d"

    sub_data(0, 1, 0) = "e"
    sub_data(0, 1, 1) = "f"
    sub_data(0, 1, 2) = "g"
    sub_data(0, 1, 3) = "h"

    sub_data(1, 0, 0) = "aa"
    sub_data(1, 0, 1) = "bb"
    sub_data(1, 0, 2) = "cc"
    sub_data(1, 0, 3) = "dd"

    sub_data(1, 1, 0) = "ee"
    sub_data(1, 1, 1) = "ff"
    sub_data(1, 1, 2) = "gg"
    sub_data(1, 1, 3) = "hh"

    Call PrintArray(sub_data, str)

End Sub

Public Sub PrintArray(Data As Variant, Cl As String)

    Dim ubnd_1, ubnd_2 As Integer
    Dim sub_data As Variant

    ubnd_1 = UBound(Data, 2)
    ubnd_2 = UBound(Data, 3)

    sub_data = Data(0) 'THIS LINE WON'T WORK. HOW TO ASSIGN CORRECTLY?

    'here I want to print the content of the Data-variable onto the sheet
    Range(Cl).Resize(ubnd_2 + 1, ubnd_1 + 1) = Application.Transpose(sub_data)
End Sub

It's not perfectly clear to me what you are trying to do but have a look in [**`here`**](http://stackoverflow.com/questions/18481330/2-dimensional-array-vba-from-cell-contents-in-excel/18481730#18481730), see if that helps — , Sep 17 '13 at 07:12
@mehow I want to print the content of the `sub_data` onto my spreadsheet `Sheet1`. Your link was great, although it uses looping through each element of the matrix. However, I read somewhere that it is faster just resizing a range and then assigning the range with the matrix-values. This is what I'm trying to do in the line `Range(Cl).Resize(...) = Application.Transpose(sub_data)`. Does that make it clearer? — gussilago, Sep 17 '13 at 07:16
There really is no way to print out a 3D array to spreadsheet using the `transpose()` function. Transpose only works with 1D arrays. What you would have to do is create three 1D arrays, iterate through your 3D array and assign each of the arrays one column. Then you would have to call the transpose function on each of 1D arrays. — , Sep 17 '13 at 07:28
Yes, you're absolutely right, and this is what I'm trying to do with the call `sub_data = Data(0)` (save obviously that I'm *not* looping here). Do you know if there is a way to assign `sub_data` all of the contents of `Data(0)` in *one* call, without having to loop through it...@mehow — gussilago, Sep 17 '13 at 07:31
well, you can return *a row* of an array using the `WorksheetFunction.Index(array, 1, 0) ` — , Sep 17 '13 at 07:38
it's as though `Data(0)` is not a allowed call. I can do `Data(0,0,0)`, but even `Data(0,0)` won't work — gussilago, Sep 17 '13 at 08:06

score 1 · Accepted Answer · 2013-09-17T08:23:30.553

You do not need a 3D array. I have changed your 3D to a 2D as two dimensions are all you need for your example. Spreadsheet is 2D anyways so transposing a 3D array just sounds impossible.

The easiest way

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

but you have to make sure you change the dimensions of your matrix/array

Option Explicit

Public Sub Test()

    Sheets(1).Cells.ClearContents

    ReDim sub_data(1 To 4, 1 To 4)

    sub_data(1, 1) = "a"
    sub_data(1, 2) = "b"
    sub_data(1, 3) = "c"
    sub_data(1, 4) = "d"

    sub_data(2, 1) = "e"
    sub_data(2, 2) = "f"
    sub_data(2, 3) = "g"
    sub_data(2, 4) = "h"

    sub_data(3, 1) = "aa"
    sub_data(3, 2) = "bb"
    sub_data(3, 3) = "cc"
    sub_data(3, 4) = "dd"

    sub_data(4, 1) = "ee"
    sub_data(4, 2) = "ff"
    sub_data(4, 3) = "gg"
    sub_data(4, 4) = "hh"

    Call PrintArray(sub_data)

End Sub

Public Sub PrintArray(Data As Variant)

    Range("A1:A" & UBound(Data, 2)) = WorksheetFunction.Transpose(WorksheetFunction.Index(Data, 1, 0))
    Range("B1:B" & UBound(Data, 2)) = WorksheetFunction.Transpose(WorksheetFunction.Index(Data, 2, 0))
    Range("C1:C" & UBound(Data, 2)) = WorksheetFunction.Transpose(WorksheetFunction.Index(Data, 3, 0))
    Range("D1:D" & UBound(Data, 2)) = WorksheetFunction.Transpose(WorksheetFunction.Index(Data, 4, 0))

End Sub

So I have changed your sub_data to a 2D variant. The structure remains the same as you expected it to be.

Thanks @mehow. I actually re-wrote the `PrintArray` to just be: `Public Sub PrintArray(Data As Variant) Dim ubnd_1, ubnd_2 As Integer ubnd_1 = UBound(Data, 1) ubnd_2 = UBound(Data, 2) Range("A1").Resize(ubnd_1, ubnd_2) = Application.Transpose(Data) End Sub` — gussilago, Sep 17 '13 at 08:20

Printing matrix content to worksheet

1 Answers1