Search code examples
rexcelrexcelvba

To use RInterface.GetArrayToVBA inside VBA function with arrays

Let an Excel spreadsheet to be connected to R through RExcel and you want to write a VBA function which calls some R functions.

In your Excel spreadsheet you have a couple of simple arrays like the following ones:

enter image description here

The code could be something like this:

Function foo(x As Range, y As Range) As Variant

    RInterface.StartRServer

    If IsNumeric(x) = True Then
        RInterface.PutArrayFromVBA "x", x
    End If

    If IsNumeric(y) = True Then
        RInterface.PutArrayFromVBA "y", y
    End If

    foo = RInterface.GetArrayToVBA("cbind(x, y, y ^ x)")

End Function

and its evident purpose is to return a matrix containing cbind(x, y, y ^ x) in Excel.

I'm not able to get it, I get strange results as I slightly amend the code: sometimes output is equal to 1, sometimes it's equal to #VALUE!... it doesn't work, though, and I am not able to understand the syntax needed in such a case.

Solution 
  • Function foo(x As Range, y As Range) As Variant

RInterface.StartRServer
RInterface.PutArrayFromVBA "x", x.Value 'you were missing this .Value'
RInterface.PutArrayFromVBA "y", y.Value
foo = RInterface.GetArrayToVBA("cbind(x, y, y ^ x)")

End Function

    I'm omitting your error checks since they will only cause other errors. If you want to do error checking do a on error goto