用Rexcel运行宏 [英] Run macro with Rexcel

问题描述

我正在VBA中使用Rexcel运行宏:

I am running a macro with Rexcel in VBA:

Sub create_efficient_frontier()

 RInterface.StartRServer

 Sheets("Analys").Range("A52:K82").ClearContents

 RInterface.PutDataframe "datat", Range("ChosenData!X181:AD352")
 RInterface.PutArray "startdate", Range("Analys!K2")
 RInterface.PutArray "enddate", Range("Analys!K3")

 RInterface.RunRFile "C:/Users/Documents/EffFront.R"

 RInterface.GetDataframe "hmz$pweight", Range("Analys!A51:E76")

End Sub

来源的R代码为:

myfront=function(datat,startdate,enddate){
  library(fPortfolio)
  datat=datat[startdate:enddate,]
  datanew=datat[sapply(datat[1,], function(x) !any(is.na(x)))] 

  datatss=as.timeSeries(datanew,datanew[,1])

  Spec = portfolioSpec()
  Constraints = "Long-Only"
  globminhfrxmed=minvariancePortfolio(
    data = datatss,
    spec = Spec,
    constraints = Constraints)

  setNFrontierPoints(Spec) <- 25
  globminfrontier <- portfolioFrontier(datatss,Spec, Constraints)

  thelisttoret<-vector(mode="list")

  pweights<-globminfrontier@portfolio@portfolio$weights
  pweights<-data.frame(pweights)
  names(pweights)=colnames(globminfrontier@portfolio@portfolio$covRiskBudgets)
  thelisttoret[["pweights"]]<-pweights

  tret<-globminfrontier@portfolio@portfolio$targetReturn[,1,drop=FALSE]
  thelisttoret[["tret"]]<-tret

  trisk<-globminfrontier@portfolio@portfolio$targetRisk[,2,drop=FALSE]
  thelisttoret[["trisk"]]<-trisk

  return(thelisttoret)
}

hmz=myfront(datat,startdate,enddate)

这似乎可行，但第一行(应为名称)为#RError.但是代码可以在R中正常工作.

That seems to work but the first row (where the names should be) is #RError. But the code works correctly in R.

这很奇怪，因为当我在网上阅读时会说

It is strange because when I read online it says

RInterface.GetDataframe(变量名，范围)
将R变量var的值(需要是一个数据框)放入Excel范围范围，将变量名放在范围的第一行.

RInterface.GetDataframe(varname,range)
Puts the value of R variable var (which needs to be a dataframe) into Excel range range, putting variable names in the first row of the range.

推荐答案

答案转到@jlhoward.

Answer goes to @jlhoward.

您的VBA代码中有错字. 代替:

You have a typo in your VBA-code. Instead of:

RInterface.GetDataframe "hmz$pweight", Range("Analys!A51:E76")

您应该使用

RInterface.GetDataframe "hmz$pweights", Range("Analys!A51:E76")，

即在R代码中，您使用pweights(复数)，而在VBA代码中，您使用pweight(单数).

i.e. in your R code you are using pweights (plural) but in VBA code you use pweight (singular).

这篇关于用Rexcel运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！