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
```