RInterface.GetArrayToVBA（）总是返回一个数组吗？

Question

参照从威尔莫特论坛this的问题，我只是写了下面的功能：

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 

是关系到RInterface.GetArrayToVBA()错误的使用情况所关注的尺寸和R到VBA数组的索引。

是否有人能够使上面的代码工作？只有五个或十个元素的阵列（priceArray和clustersNumber等于2或3）的工作示例就足够了。

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 

，并带有示例数据运行：计算结果为

 [,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 

一个2x4的表产生6.01