BERT Utilities

Here are some built-in functions and some code samples to make developing and using R in Excel a bit easier.


User Buttons

User buttons provide a way to attach functions to the Excel ribbon menu. Buttons execute arbitrary R code, and can interact with the spreadsheet just like the console – you can use the Excel scripting interface or the Excel API functions.



See this page for more on user buttons.


Importing Packages

You can map all functions from an installed package or an environment in one step:

BERT$UsePackage( "gsl", "gsl" );

The functions BERT$UseEnvironment and BERT$UsePackage do the same thing, with the package version taking the name of a package. The second parameter is a prefix used when installing the function in Excel.

Any additional arguments are passed to ls() when listing the functions, so you can install a subset of functions using a pattern:

BERT$UsePackage( "gsl", "gsl", pattern="bessel" );

Before you do that, of course, you’ll have to install the package and load it into R. Install packages from the Packages menu in the BERT console, or using the standard R CRAN functions. Load the package with library() or require() before calling BERT$UsePackage().


Excel Range -> Data Frame

A very common question is how to convert an Excel range to a data frame.

An Excel range can contain any kind of values – like a mix of numbers, strings, and booleans. Lists in R can only contain a single type. If you try to mix strings and numbers in a list, all your numbers will be converted to strings.
This is usually not what you want.

For this reason, when a BERT function receives an Excel range, it’s usually returned as lists of lists – this preserves types, but it can be hard to manipulate. If you just want to turn a range into a list of values, use unlist. But if you want to preserve column types, or if your range includes headers, it requires a little more work.

BERT (as of v 1.24) includes a function for turning a list of lists into a data frame, optionally with headers in the first row. Use the function range.to.data.frame( rng, headers=F ) if your function receives an Excel range, or when using the COM (scripting) interface.

Let’s say you have this data in your spreadsheet:

AB
1MonthSales
2April12,002
3May13,994
4June13,405

To pull this into R with the scripting API, and turn it into a data frame:

> data <- EXCEL$Application$get_Range( "A1:B4" )$get_Value()
> range.to.data.frame(data, TRUE)

  Month Sales
1 April 12002
2   May 13994
3  June 13405

Storing R Data (or Functions) in a Spreadsheet

Perhaps you have some R variables you want to preserve with a particular spreadsheet. Or you have some R functions that you use with only one spreadsheet. Here’s a library of functions you can use to store and retrieve R data – including functions – in spreadsheets.

BERT Storage Library

See the code comments for requirements and examples.


Selection Toolbar

This Excel Add-in adds some commands to the toolbar: copy the selected range to an R variable, or copy an R variable to the selected range.

BERT Selection Tools