VBA to R and Back Again: Running R from VBA

[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.


Today I am going to briefly go over an extremely simple example of running some R code via Excel VBA.

Let’s start by discussing each line of code one by one:

Sub CallRnorm()

This line defines a subroutine called “CallRnorm”. A subroutine is a block of code that can be executed repeatedly from any part of the code, and it starts with the “Sub” keyword followed by the subroutine name and any arguments in parentheses.

Dim R As Variant
Dim result As Variant

These two lines declare two variables named “R” and “result” as “Variant” data type. “Variant” is a data type that can store any type of data.


This line deletes the entire column A from the active worksheet.

R = "library(stats);rnorm(10) |> as.data.frame()"

This line assigns a string of R code to the variable “R”. The code will load the “stats” package and generate 10 random numbers from a normal distribution using the “rnorm()” function, and then convert the result to a data frame using the pipe operator “|>” and the “as.data.frame()” function.

result = VBA.CreateObject("WScript.Shell").Exec("C:\Program Files\R\R-4.1.2\bin\x64\Rscript.exe -e """ & R & """").StdOut.ReadAll

This line uses the “CreateObject” method to create a new object of the “WScript.Shell” class, which allows us to execute commands in the Windows command shell. It then uses the “Exec” method to execute the R code stored in the “R” variable using the “Rscript.exe” command-line tool, which runs R scripts from the command line. The result of the command is stored in the “result” variable by reading the output of the command using the “StdOut” property of the “Exec” object and the “ReadAll” method.

result = Split(result, vbCrLf)
For i = 0 To UBound(result)
    ActiveSheet.Range("A1").Offset(i, 0).Value = result(i)
Next i

These two lines split the result of the R code execution into an array of strings using the “Split” function and the newline character (vbCrLf) as the delimiter. It then loops through the array using a “For” loop and assigns each element to a cell in the active worksheet, starting from cell A1 and offsetting each cell by one row using the “Offset” method.

So, in summary, this VBA code creates a subroutine that deletes column A from the active worksheet, executes a block of R code that generates 10 random numbers from a normal distribution and converts the result to a data frame, captures the output of the R code execution, splits the output into an array of strings, and pastes the result into column A of the active worksheet.

VBA to R and Back Again

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.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)