Calling arbitrary R code from Excel with easy data input and output (Excel↔R)

README.md

Usage

1. Run (once) the R/plumber server:

in R console: plumber::plumb('plumber.R')$run(port=9000)

or in cmd.exe: "C:\Program Files\R\R-4.3.1\bin\x64\R.exe" -e "plumber::plumb('plumber.R')$run(port=9000)"

or in powershell: &"C:\Program Files\R\R-4.3.1\bin\x64\R.exe" -e "plumber::plumb('plumber.R')`$run(port=9000)"

Feel free to change the port if needed, but then modify it in the VBA macro code too. If the "plumber.R" script is not in your current working directory/folder, add the appropriate path to that file.

2. Run (as many times as you want) the VBA macro on the selected 1-column 3-cell range in Excel:

• 1st cell should contain the string with the address of the input range in the Excel style e.g. A1:C4
• 2nd cell should contain the string with the valid R code. The input data should be referred in that code as inputData
• 3rd cell should contain the string with the address of the top-left cell of the range where the output from R (assumed to be a data.frame/data.table) will be pasted

Example

With this data:

	A	B	C
1	mycol1	mycol2	mycol3
2	101	abc	0.5
3	102	defgh	0.78
4	103	ijklmnopq	0.13

running the VBA macro with cells selected (they can be anywhere in the worksheet) with the following contents (text/strings):

A1:C4

inputData %>% .[, MyCol4 := mycol1 + 3] %>% within(MyCol5 <- mycol3*10)

E1

(The middle cell contains R code with magrittr pipe and the first operation in the data.table syntax and the second operation in the base-R syntax as examples).

you get the following result:

	A	B	C	D	E	F	G	H	I
1	mycol1	mycol2	mycol3		mycol1	mycol2	mycol3	MyCol4	MyCol5
2	101	abc	0.5		101	abc	0.5	104	5
3	102	defgh	0.78		102	defgh	0.78	105	7.8
4	103	ijklmnopq	0.13		103	ijklmnopq	0.13	106	1.3

CallPlumberAPI.vba

Sub CallPlumberAPI()
    Dim SERVER_URL As String
    Dim xmlhttp As Object
    Dim ws As Worksheet
    Dim inputRange As Range, dataRange As Range, outputRange As Range
    Dim cellAddress As String, rCode As String, outputAddress As String
    Dim i As Long, j As Long
    Dim tsvData As String, processedData As String
    Dim outputData() As String, outputRow() As String
    
    SERVER_URL = "http://localhost:9000/processData"
    
    ' Check if the server is running
    On Error Resume Next ' Ignore errors temporarily
    If IsError(WorksheetFunction.WebService(Replace(SERVER_URL, "processData", "echo?msg=test"))) Then
        MsgBox "R/Plumber server does not seem to be running." & vbCrLf & "Please start the server and try again."
        Exit Sub
    End If
    On Error GoTo 0 ' Reset error handling
    
    ' Initialize worksheet and input range based on active sheet and selection
    Set ws = ActiveSheet
    Set inputRange = Selection
    
    ' Check that the selected range is a one-column, three-cell range
    If inputRange.Rows.Count <> 3 Or inputRange.Columns.Count <> 1 Then
        MsgBox "Please select a one-column, three-cell range."
        Exit Sub
    End If
    
    ' Read the cell address for the data, the stringParam, and the output cell address
    cellAddress = inputRange.Cells(1, 1).Value
    rCode = inputRange.Cells(2, 1).Value
    outputAddress = inputRange.Cells(3, 1).Value
    
    ' Initialize the data range
    Set dataRange = ws.Range(cellAddress)
    
    ' Create TSV data from the data range
    tsvData = ""
    For i = 1 To dataRange.Rows.Count
        For j = 1 To dataRange.Columns.Count
            tsvData = tsvData & dataRange.Cells(i, j).Value
            If j < dataRange.Columns.Count Then
                tsvData = tsvData & vbTab
            End If
        Next j
        tsvData = tsvData & vbCrLf
    Next i
    
    ' Initialize the XMLHTTP object
    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    ' Make POST request to the Plumber API
    xmlhttp.Open "POST", SERVER_URL, False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlhttp.send "rCode=" & WorksheetFunction.EncodeURL(rCode) & "&inputData=" & WorksheetFunction.EncodeURL(tsvData)
    
    ' Parse the returned data
    processedData = xmlhttp.responseText
    outputData = Split(processedData, vbCrLf)

    ' Initialize the output range based on the dimensions of the returned data
    Dim rowCount As Long, colCount As Long
    rowCount = UBound(outputData)
    colCount = UBound(Split(outputData(0), vbTab)) + 1

    Set outputRange = ws.Range(outputAddress).Resize(rowCount, colCount)
    
    ' Populate the output range with processed data
    For i = LBound(outputData) To UBound(outputData)
        outputRow = Split(outputData(i), vbTab)
        For j = LBound(outputRow) To UBound(outputRow)
            outputRange.Cells(i + 1, j + 1).Value = outputRow(j)
        Next j
    Next i
    
End Sub

plumber.R

# Load the required libraries
library(plumber)
library(data.table)
library(magrittr)

safeEval <- function(rCode, inputData)
	tryCatch(eval(parse(text=rCode)),
			 error = function(e)
				 data.frame(Error = as.character(e) %>%
								sub('^Error in parse\\(text = rCode\\): <text>:(\\d+):(\\d+):',
									'Syntax error in line \\1, character \\2:', .) %>%
								sub('Error in eval(parse(text = rCode)): ',
									"", ., fixed=TRUE))) %>% # Remove the initial string Error in eval(parse(..
	`if`(!is.data.frame(.), as.data.frame(.), .) %>% # try coercing, otherwise server error and VBA macro crash
	{c(paste(colnames(.),collapse='\t'),
	   do.call(paste,c(.,list(sep='\t'))))} %>%
	paste(collapse='\r\n')

# Define the API
#* @apiTitle Sample Plumber API for Data Processing

#* Echo back the input
#* @param msg The message to echo
#* @get /echo
function(msg="") {
    safeEval(msg)
}

#* Process the data
#* @param rCode A string parameter to be processed
#* @param inputData Input data from Excel in TSV format
#* @post /processData
function(req, res, rCode, inputData) {
    # Read the data from the uploaded TSV file
    inputData <- data.table::fread(text=inputData)
    # Perform data processing
    processedData <-
        safeEval(rCode, inputData)
    # Set the content type to XML and return
    res$setHeader("Content-Type", "application/xml")
    res$body <- processedData
    return(res)
}