BERT Utilities

BERT includes some utilities to make developing and using R in Excel a bit easier. Here are the basics:

File Watcher

When developing new code, it can be annoying to have to reload functions into Excel over and over. Using the file watch API, you can have BERT automatically reload functions whenever you save your file.

The file watcher can run any arbitrary code when your file changes, including recalculating your spreadsheet to see code changes immediately.

To watch a file, use

BERT$WatchFile( "C:\\path\\to\\file.R" );

You need to use a fully-qualified path when using the file watch API. Here's a tip: to watch the startup file (functions.R in the BERT install directory), use

BERT$WatchFile( file.path( BERT$HOME, "functions.R" ));

When the file changes, BERT will reload your startup file (in this case, we assume your startup file is including that R code). You can execute arbitrary code with the second parameter:

BERT$WatchFile( file.path( BERT$HOME, "functions.R" ), function(){

  BERT$ReloadStartup();

  cat( "Recalculating...\n");
  EXCEL$Application$CalculateFull();

});


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.


Function Management

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" );