Does RInterface.GetArrayToVBA() always return an array?

Question

Referring to this question from Wilmott Forums, I've just written the following function:

Public Function KmeansPrice(ByVal priceArray As Range, _
                            ByVal clustersNumber As Integer) As Double

    ' Following rows are reproducible only if RExcel has been installed
    ' on your Excel!

    Dim y() As Double

    RInterface.StartRServer
    RInterface.PutArrayFromVBA "x", priceArray
    RInterface.PutArrayFromVBA "n", clustersNumber
    RInterface.RRun "x = as.numeric(x)"
    RInterface.RRun "cluster = kmeans(x, n)$cluster"
    RInterface.RRun "bestBid = rep(NA, n)"
    RInterface.RRun "for(i in 1:n)" & _
                    "{" & _
                    "  assign(paste('group.', i, sep = ''), " & _
                    "         x[cluster == i]);" & _
                    "  bestBid[i] = max(get(paste('group.', i, sep = '')))" & _
                    "}"
    RInterface.RRun "y = min(bestBid) + 0.01"
    y = RInterface.GetArrayToVBA("y")
    KmeansPrice = y(0, 0)

End Function

Of course I've prototyped it in R before and it worked properly, then I guess that the cause of this error:

Error -2147220501
in Module RExcel.RServer

Error in variable assignment

is related to the wrong usage of RInterface.GetArrayToVBA() for what concerns dimensions and indexing of arrays from R to VBA.

Is anyone able to make the code above work? A working example with an array of just five or ten elements as priceArray and clustersNumber equal to 2 or 3 would be sufficient.

score 1 · Accepted Answer · answered Sep 03 '14 at 18:59

I'm not familiar with the clustering function, but this returns a result without breaking.

I prefer to make my functions in an R editor and then source the code, so I did this in R, then sourced my R function.

kmeansPrice <- function(priceArray,clustersNumber)
{
  `[` <- function(...) base::`[`(...,drop=FALSE) #in case we have a 1 dimensional table
  x<-priceArray
  n<- clustersNumber
  x<-matrix(as.numeric(x),nrow=dim(x)[1],ncol=dim(x)[2])
  cluster = kmeans(x, n)$cluster
  bestBid = rep(NA, n)
  for(i in 1:n)
  {
    assign(paste('group.', i, sep = ''),
    x[cluster == i])
    bestBid[i] = max(get(paste('group.', i, sep = '')))
  }
  return(min(bestBid) + 0.01)
}

Then you can just

Public Function KmeansPrice(ByVal priceArray As Range, _
                            ByVal clustersNumber As Integer) As Double

rinterface.PutArrayFromVBA "priceArray", priceArray.Value 'I think this ".Value" was your problem'
rinterface.PutArrayFromVBA "clustersNumber", clustersNumber
rinterface.RRun "theResult <- kmeansPrice(priceArray,clustersNumber)"
y = rinterface.GetRExpressionValueToVBA("theResult") 'preferred to GetArrayToVBA for single-value results'
KmeansPrice = y
End Function

and run it with example data: a 2x4 table that evaluates to

     [,1] [,2]
[1,]    5    9
[2,]    6   10
[3,]    7   11
[4,]    8   12

with 3 "clusters"

Sub runkmeans()
theResult = KmeansPrice(Range("BH2:BI5"), 3)
MsgBox (theResult)
End Sub

which yields 6.01

Thank you for your solution proposal, I cannot currentyl try it but I'll do as soon as I can and accept your answer if working. Just a question before: when you say that "_you prefer to make your functions in an R editor_ **and then source the code**", how sourcing `R` functions code in RExcel is supposed to work? — Lisa Ann, Sep 06 '14 at 12:08
in VBA, `Rinterface.rrun("source('myfile.r')")` You can put it in auto_open() to source on excel startup, or just source("myfile.r") in RProfile.site in /your r folder/etc to source it on R startup — hedgedandlevered, Sep 08 '14 at 12:42

Does RInterface.GetArrayToVBA() always return an array?

1 Answers1