Site icon R-bloggers

VBA to R and Back Again: Running R from VBA Pt 2

[This article was first published on Steve's Data Tips and Tricks, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
< section id="introduction" class="level1">

Introduction

Yesterday I posted on using VBA to execute R code that is written inside of the VBA script. So today, I will go over a simple example on executing an R script from VBA. So let’s get into the code and what it does.

First, let’s look at the Function called “Run_R_Script”. This function takes four arguments, where the first two are mandatory, and the last two are optional.

The first line inside the Function defines two variables: sPath and shell.

Next, we wrap the R path with double quotations to avoid any issues with spaces in the path.

After that, the script deletes Column A.

Then, instead of using the “shell.Run” function, the code uses the “shell.Exec” function to execute the R script. This function returns an object that has a “StdOut” property, which contains the output of the script.

The output is then read using the “ReadAll” method, and the resulting string is split into an array using the “Split” function. The array is then iterated using a “For” loop, and each element of the array is written to Column A, starting at cell A1.

Finally, the Function returns an Integer value, which is the result of the “shell.Run” function.

The Subroutine called “Demo” just demonstrates how to use the “Run_R_Script” function by calling it with the appropriate parameters.

< section id="full-code" class="level1">

Full Code

Here is the R Script

data.frame(
    x = 1:10,
    y = rnorm(10)
)

list(
    data.frame(
        x = 1:10,
        y = rnorm(10)
    ),
    data.frame(
        x = 1:10,
        y = rnorm(10)
    )
)

Full VBA

Function Run_R_Script(sRApplicationPath As String, _
                        sRFilePath As String, _
                        Optional iStyle As Integer = 1, _
                        Optional bWaitTillComplete As Boolean = True) As Integer

    Dim sPath As String
    Dim shell As Object

    'Define shell object
    Set shell = VBA.CreateObject("WScript.Shell")

    'Wrap the R path with double quotations
    sPath = """" & sRApplicationPath & """"
    sPath = sPath & " "
    sPath = sPath & sRFilePath

    'Delete Coumn A
    Columns("A").Delete
    
    'Get Result
    result = shell.Exec(sPath).StdOut.ReadAll
    result = Split(result, vbCrLf)
    For i = 0 To UBound(result)
        ActiveSheet.Range("A1").Offset(i, 0).Value = result(i)
    Next i
    
End Function

Sub Demo()
    Dim iEerrorCode As Integer
    iEerrorCode = Run_R_Script("C:\Program Files\R\R-4.1.2\bin\x64\Rscript.exe", "C:\Users\ssanders\Desktop\test.R")
End Sub
< section id="picture" class="level1">

Picture

Example Output, VBA, and R

< section id="reference" class="level1">

Reference

https://stackoverflow.com/a/54816881

To leave a comment for the author, please follow the link and comment on their blog: Steve's Data Tips and Tricks.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Exit mobile version