How to select range and output of any size?

Question

I am calling an R function in Excel using VBA code Application.run("BERT.Call",..). It is working successfully. But to do this, I need to select the cells for printing output.

Suppose I want to get output of Summary() of R in Excel using BERT.Call then at first I need to select 6 column cells where my output will be shown or else I need to pass the range through VBA code itself.

My question is:

Is there any way for which the user don't need to select/enter range and output of any size(variable size) can be obtained using BERT.Call to call R function in Excel VBA code?

score 0 · Answer 1 · answered Mar 27 '18 at 15:35

For your example, Summary() from R returned a list. In VBA, it will be a multi-dimensional array. You can write this array to anywhere you want, by VBA.

Below is the code I am using. It can handle at most 2-dim array. "Data" is the array(or single item) you want to write. "Cl " is the left-top range(in excel) where you want to output. "byRow" = True will output your array as transposed. ex. PrintArray someData, Sheet1.[A1]

Sub PrintArray(Data As Variant, Cl As Range, Optional byRow As Boolean = False)
    If (Not IsArray(Data)) Then
        Cl = Data
        Exit Sub
    End If

    On Error GoTo oneDimWriting
    If (byRow) Then
        Cl.Resize(UBound(Data, 2), UBound(Data, 1)) = Application.WorksheetFunction.Transpose(Data)
    Else
        Cl.Resize(UBound(Data, 1), UBound(Data, 2)) = Data
    End If
    Exit Sub
oneDimWriting:
        Cl.Resize(1, UBound(Data, 1)) = Data
End Sub

How to select range and output of any size?

1 Answers1