r-是否RInterface.GetArrayToVBA()总是返回一个数组

Question

参考Wilmott Forums的这个问题，我刚刚编写了以下函数：

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

当然，我以前在R中对它进行过原型化，它工作正常，然后我想这个错误的原因是：

Error -2147220501
in Module RExcel.RServer
Error in variable assignment

与CCD_ 2在从CCD_ 3到VBA的数组的维度和索引方面的错误使用有关。

有人能让上面的代码工作吗？具有仅仅五个或十个元素的阵列作为CCD_ 4和等于2或3的CCD_。

Answer 1

我不熟悉集群函数，但这会返回一个不间断的结果。

我更喜欢在R编辑器中制作函数，然后源代码，所以我在R中这样做，然后源我的R函数。

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)
}

然后你可以只

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

并使用示例数据运行它：一个评估为的2x4表

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

具有3个"集群"

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

其产生6.01