For the most part, we recommend talking to Excel directly from R, using the BERT console; see the page on the Excel scripting interface.
However there are some times you will still need to use VBA, and this page describes how you can call R functions – your own functions as well as built-in and library functions.
If you define a function using BERT, so that it’s exposed in Excel, you can call it from VBA using Application.Run. This works the same way as any other Excel spreadsheet function.
Suppose you have the R function Add (like in the sample functions.R file):
Add <- function(...){
sum(...);
}
This is exported in Excel as R.Add. To call this function from VBA, use
Sub Test()
x = Application.Run( "R.Add", 1, 2, 3, 4 )
MsgBox x
End Sub
All the arguments to Application.Run after the function name are passed to the function as arguments.
You can also call functions that are not exported by BERT, like built-in or package functions. To do that, we have a special function called BERT.Call. For example, you can call the built-in R function sum from VBA:
Sub Test2()
x = Application.Run( "BERT.Call", "sum", 1, 2, 3, 4 )
MsgBox x
End Sub
In this case, the first argument to Application.Run is our special function, BERT.Call. The next argument is the name of the function. All the arguments after that are passed as arguments.
In addition to functions, you can execute code directly. This is useful if you have a complex function or nested functions. Use BERT.Exec and pass your R code as the next argument:
Sub SamplePlot()
Application.Run "BERT.Exec", "plot( sort( rnorm( 1000 )))"
End Sub
There’s a full example of calling R from VBA, using BERT, available here:
This example uses k-means clustering to sort data into natural groups. The source data is a set of countries with various statistical characteristics. The built-in statistics package in R contains a function kmeans for performing k-means clustering.
To call the function from VBA, we use the BERT.Call function:
...
' call kmeans
v = Application.Run("BERT.Call", "kmeans", rng, cluster_count, iterations)
...
We will discuss a few details here; check R help on kmeans for full information on the function, arguments, and return type.
In the function call, the rng parameter is an Excel range; it’s automatically converted to a matrix of doubles, because all the values are numeric. If the range includes any strings, or any empty cells, it will be converted to a list instead (see the next section).
The returned value from kmeans (v in our example) is a list containing various components.
In R, these components are named, but that’s not possible in a VBA variable, so in this case we need to access them by position. We pull out the list of clusters and the centers by indexing into the list:
...
Dim clusters, centers As Variant
clusters = v(1, 1)
centers = v(2, 1)
...
The rest of the VBA in the example is concerned with collecting the data and sorting and formatting the output.
When calling R from VBA, there are some important things to know about how values go in and out. Much of this is the same as when calling R from spreadsheet functions, but there are some specific considerations for ranges and arrays.
To understand how arguments are interpreted from VBA -> R, we’ll use a function that prints out the argument and type. If you want to try it, add this function to your functions.R file or just paste it into the BERT shell:
param.test <- function(p){
print(typeof(p));
print(p);
TRUE;
}
Suppose we have a simple range in Excel:
| A | B | |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 3 | 4 |
and pass this into the R function from VBA,
Application.Run "BERT.Call", "param.test", Range( "A1:B2" )
The BERT shell will print out the type and value:
[1] "double"
[,1] [,2]
[1,] 1 2
[2,] 3 4
This is interpreted as a matrix of doubles, because all the values are numeric. If one cell in the range contains a string,
| A | B | |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 3 | x |
then when passed to R, it will be converted to a list of lists:
[1] "list"
[,1] [,2]
[1,] 1 2
[2,] 3 "x"
because R vectors can’t contain mixed types. If the inputs to your functions may contain strings, or empty cells, then you need to validate and potentially reformat the data in your R function. Empty cells are treated as NA strings, not zeros.
Excel and VBA are row-major, while R is column-major. If you are using a range, this should be transparent. But if you are constructing arrays in VBA, then you need to be aware of the difference.
Also note that when you define an Array in VBA, by default the array is 0-based instead of 1-based, and the parameter to dim refers to the upper bound of the array, not the length. If we define an array in VBA and call the param.test function,
Sub Test()
Dim x(3) As Variant
x(0) = 0
x(1) = 1
x(2) = 2
x(3) = 3
Application.Run "BERT.Call", "param.test", x
End Sub
R will print out the result as a row with 4 columns:
[1] "double"
[,1] [,2] [,3] [,4]
[1,] 0 1 2 3
Because Excel is row major, that array is treated as a row; and because R is always 1-based, the indexes are increased by 1. If you want a 1-based array in VBA, use the syntax dim(1 to UpperBound). In this case the R and VBA indexes will match up:
Sub Test()
Dim x(1 To 3) As Variant
x(1) = 1
x(2) = 2
x(3) = 3
Application.Run "BERT.Call", "param.test", x
End Sub
[1] "double"
[,1] [,2] [,3]
[1,] 1 2 3