Adding R Functions to Excel (In Detail)

The first step is to write an R function. You can write functions directly in the R startup file, although for more complex functions it makes sense to use separate files and source them into the environment. You might also maintain code on a remote file or http server, for central management.

For the most part you don't have to worry about argument and return types, they will behave as expected. There are a few caveats regarding matrices and ranges, though, and we don't support complex numbers -- see [the page on types] 1.

For this walkthrough, we will use a simple factorization example and write it in the startup file.

1. Writing a Function

---

In Excel, click the ADD-INS tab. On the BERT menu, select Home Directory. This will open a file browser in the BERT home directory (by default this is in your user directory).

Open the file in your favorite editor. Here, I'm using [Scite] 2:

Note the function takes two arguments, and the second has a default value. Here is the function if you want to copy and paste:

CHOLESKY <- function( mat, pivot=FALSE ){ chol(mat, pivot) }

Then save the file and switch back to Excel.

2. Installing a Function in Excel

---

Functions in your startup file (or sourced in your startup file) are loaded automatically when Excel starts. If you make changes, though, you need to reload the code.

Back in Excel, click the ADD-INS tab. From the BERT menu, select Reload Startup File.

If there are any errors, you will see a message in the Excel status bar. If not, you're good to go. To see error messages, open [the console] 3.

All functions in the startup file are loaded into Excel's namespace (they're prefaced with R.). Internally, the add-in loads the file into the R workspace then uses lsf.str to find functions. For each function it finds, the Add-in creates a wrapper function and registers that wrapper function with Excel.

3. Using Functions

---

Once a function has been installed, you can use it like any other Excel function by typing into a cell:

If you open the Insert Function dialog in Excel (Shift+F3, or the f(x) button), R functions from the startup file will appear in the category Exported R Functions:

Inserting a specific function will show the named arguments and default values (if any):

When you type one of these functions into a cell, or when the spreadsheet recalculates, Excel calls the wrapper method installed by the BERT Add-in. The Add-in collects any arguments passed with the function and converts these into R types. It then uses do.call to run the function. Any return value is converted to an Excel type and returned.

Any messages printed by functions are sent to the [BERT console] 3. You can also use the console to test and debug your function.