BERT Utilities
Here are some built-in functions and some code samples to make developing and using R in Excel a bit easier.
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 to 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:
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | April | 12,002 |
| 3 | May | 13,994 |
| 4 | June | 13,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.
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.
