VBA could find R library

Question

I am trying to use EXCEL as the front end for a R script. So far, I tested my R script in Windows CMD but I could not make it work in VBA. The error message is Error in library(readxl) : there is no package called 'readxl'. So it looks like VBA environment is picky.

1. Any suggestions on fixing this error? (fixed now)
2. Is there a way to run R script and save the function returned value (now it is 5) into a variable in VBA? I can do this by saving a text file and load again, but not sure if there is a better way to handle this.

a simple example of R script, which defines a function and calls it later.

est_var_dw <- function(){
    library(readxl)
    library(minpack.lm)
    library(msm)
    return(2+3)
}
est_var_dw()

a simple example of VBA

Sub run_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 = """" & Cells.Range("B1") & """ """ & Cells.Range("B2") & """ &  Pause"

    errorCode = shell.Run(path, style, waitTillComplete)
End Sub

Update

I figured out the first issue was due locations of different R packages, which can be solved by using .libpath

.libPaths(c(R_library_pth1, R_library_pth2))

Answer 1

There is a very good function for the second part of your question here: Capture output value from a shell command in VBA?

bburns-km defines a vba function ShellRun:

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string'

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command'
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object'
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function

As long as RScript.exe is in your PATH, you can then call from VBA:

Sub Test()

Dim ScriptPath As String
Dim StringOut As String

ScriptPath = "C:\...\test.R" 'Your Path Here

'Assign
StringOut = ShellRun("RScript " & ScriptPath)

'Print
Debug.Print StringOut

End Sub

Anything that your R script prints to console during session will be returned to VBA as a string