Introduction

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.

Columns("A").Delete

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.