4

I am able to execute R in batch mode via Excel VBA with the code below, but this method limits interactivity because R closes after completion of the script. I would like to be able to make further calls to R based on Excel user input, while utilizing the R objects created in the first call. Is there a way to keep R active while still utilizing VBA to send messages to R?

Sub Run_R()

Dim shell As Object, Rcmd As String, retval As Variant

Set shell = VBA.CreateObject("WScript.Shell")
Rcmd = "Rscript C:\test.R"
retval = shell.Run(Rcmd, 0, True)

End Sub
Community
  • 1
  • 1
R.Liehr
  • 41
  • 3
  • 1
    Not tried it since not on a Windows machine right now but maybe calling R instead of Rscript will be viable for an interactive session? But note that Windows will buffer the output so some maybe not be returned without either forcing the buffer free or by terminating R. We've used this in the past to achieve what you are after but later moved to using web services that exposed R to Excel in a centralized manner: http://www.statconn.com/products.html#RExcel – Hansi Sep 04 '16 at 15:18
  • I tried to implement live connection between R and Excel - see https://github.com/gdemin/excel.link/blob/master/inst/doc/R_connection_examples.xlsm - sub EvaluateInR. But connection is not very stable now. – Gregory Demin Sep 04 '16 at 16:49
  • Thanks for the idea Hansi. I tried calling "R", which activated Rterm.exe from within the command window, which essentially seems to be an R console. Now if I could just send more R commands to that active Rterm window, I might have a solution. Any ideas on how to send another command? Using shell.Run() opens a new session, so that will not work. – R.Liehr Sep 06 '16 at 14:13

1 Answers1

3

Consider segmenting the two processes. Have Excel VBA obtain all needed user-input values through spreadsheet or userform, and then pass them into R script. R reads such values as command line args for a longer routine of operations.

Here, R is spawned as a child process in your application akin to aa separate macro or function which receives input parameters and processes an output:

VBA

Sub Run_R()

    Dim shell As Object, Rcmd As String, retval As Variant
    Dim var1, var2 As Double

    var1 = Range("A2").Value
    var2 = Range("A5").Value

    Set shell = VBA.CreateObject("WScript.Shell")
    Rcmd = "Rscript C:\test.R " & var1 & " " & var2
    retval = shell.Run(Rcmd, 0, True)

End Sub

R

args <- commandArgs(trailingOnly=T)

var1 <- args[1]
var2 <- args[2]

# ... run other operations ...
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • 1
    I like what you are thinking Parfait, but your proposal still limits interactivity because the user must know all inputs before calling R. The idea I am going for is that the user will react to the first R call, by making a new informed call with inputs that depend on the output of the first R call. – R.Liehr Sep 06 '16 at 14:18
  • What is the output of R? Why not several back-and-forth calls to R? User runs R the first time, assesses output (csv, img, etc. whatever R produces), then makes input again with second call or more thereafter. R should be seen as backend code with Excel the frontend. – Parfait Sep 06 '16 at 17:34
  • 2
    In fact, consider even using R as the GUI frontend, using [gWidgets](https://cran.r-project.org/web/packages/gWidgets/vignettes/gWidgets.pdf) or a [Shiny app](http://shiny.rstudio.com/) to run in browser. Shameless plug, but I recently created a [Git repo](https://github.com/ParfaitG/GUI_Program) for database-driven GUI programs. Check out the R script. Of course Excel userform is also included! – Parfait Sep 06 '16 at 17:36
  • 1
    The back-and-forth to R in batch mode is exactly what I am trying to avoid for efficiency. Every time it is executed, the OS must open R, load packages, Run R, then close R. This takes too much time for the application I am working on, which will have a lot of user interaction. (The outputs of R are multivariate parameter estimates and graphics.) I wanted to exhaust my options in VBA before I move on to Shiny, which will require some time to learn. – R.Liehr Sep 08 '16 at 18:57