Run R function in VBA macro

Question

I'm trying to run a R code from excel VBA macro.

The R function is using two inputs: DataFilter(Input1,Input2) The function does some filtering and then saves a new excel file in the folder N:\DataFolder

Can you please help in creating an VBA macro that runs the DataFilter function with two new text inputs (lets say cells A1 and B1 from the sheet1 in the spreadsheet). From there I will be able to open the excel file in the vba macro.

Thank you!

Answer 1

You can run an R script in VBA by creating Windows Shell obejct and passing it a string that executes an R script

Sub RunRscript()
'runs an external R code through Shell
'The location of the RScript is 'C:\R_code'
'The script name is 'hello.R'

Dim shell As Object
Set shell = VBA.CreateObject("WScript.Shell")
Dim waitTillComplete As Boolean: waitTillComplete = True
Dim style As Integer: style = 1
Dim errorCode As Integer
Dim path As String
path = "RScript C:\R_code\hello.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

(source)

You can pass in your cell values as command line args to the R script. See ?commandArgs for more.

Answer 2

I am not sure this is a direct answer. You might want to look at how it is done using the Bert toolkit. This seems to help a lot in integrating R and Excel with each other.

https://bert-toolkit.com/