Wrapping very long functions in RExcel VBA?

Question

When you want to use R functions in VBA via RExcel, you have to use

RInterface.RRun "..."

Then, if you'd like to define your own R function, you can simply

RInterface.RRun "y <- function(x) { ... }"

If y is made up by more than one command line, you can separate each line with ;, as you're used to do in R environment.

But... what if your y function is very very long?

A 20 ~ 30 rows R function is damn difficult to be written in such a way in VBA; and there's a limit to the length of VBA sentences.

So: how may I wrap?

Here's an example of a quite long R function: can you show me how to put in VBA using RExcel?

bestIV <- function(dT, IVTS.t, Spot, r) {
  b <- r
  xout <- seq(0, max(T), dT)
  sfm <- matrix(NA, nrow = length(K), ncol = length(xout))
  for(i in 1:length(K)) {
    sfm[i,] <- approx(x = T, y = IVTS.t[i,], xout = xout, rule = 2)$y
  }
  sfm[,1] <- sfm[,1] + sfm[,2] - sfm[,3]
  rownames(sfm) <- K
  colnames(sfm) <- xout
  Option <- matrix(NA, nrow = length(K), ncol = length(xout))
  for(i in 1:length(K)) {
    for(j in 1:length(xout)) {
      TypeFlag <- ifelse(K[i] < Spot, 'p', 'c')
      Option[i,j] <- GBSOption(TypeFlag = TypeFlag, S = Spot, X = K[i], 
                               Time = xout[j] / 365, r = r, b = b, 
                               sigma = sfm[i,j] / 100)@price
    }
  }
  rownames(Option) <- K
  colnames(Option) <- xout
  dP <- (cbind(0, -t(apply(X = Option, MARGIN = 1, FUN = diff))) / Option)[,-(1:2)]
  dV <- dP / dT
  min.V <- which(dV == min(dV), arr.ind = TRUE, useNames = TRUE)
  Strike <- as.numeric(dimnames(min.V)[1])
  Maturity <- as.numeric(unlist(dimnames(dV)[2]))[min.V[2]]
  Days <- dT
  Mat <- c(dV[which(dV == min(dV))], Strike, Maturity, Days)
  names(Mat) <- c('Value', 'Strike', 'Maturity', 'Days')
  return(Mat)
}

Thanks,

Answer 1

You are passing a string as an argument to a VBA function. Thus your question reduces to "how can I concatenate strings in VBA".

The answer is to use the concatenation operator &, like this:

"a" & "b"

Say you have an R function:

y <- function(x, a, b){
  return(x)
}

Then you can do this in VBA:

RInterface.RRun "y <- function(x, a, b) {" &
  "return(x)" &
  "}"