Talking to Excel from R

In addition to writing spreadsheet functions, BERT is great for interacting with Excel from R. We’ve written a set of functions, included by default, for getting and setting spreadsheet values.

Heads up: we don’t recommend using these functions any more.
You should use the Excel scripting interface instead.

We’re keeping this page around for reference. These functions will still work, if you load the ExcelFunctions.R file, but going forward you should write code using the scripting interface.

If you are familiar with the Excel API, you can write your own function to call any Excel API function – have a look at the file ExcelFunctions.R, included in the startup directory, as a starting point.

We also added an Excel reference type as an S4 class. This is useful for storing references, or passing references as inputs or outputs.

If you have a suggestion, comment, or request for a particular function, please let us know.

Context Matters

These functions work from the BERT console. They won’t work within R functions that you call from the spreadsheet. This is because they are evaluated in different contexts.

But you can call them from the console any time, and you can call them from within other functions that you call from the console.

Functions

Text.Ref (reference.text)

Returns an Excel reference (as our S4 class) based on the passed string. You can pass a cell address (“A1”), range (“C2:E5”), or named range.

Example:

> ref <- Text.Ref( "A2:B4" );
> ref
Excel Reference R2C1:R4C2

Set.Cell (ref, formula.or.value)

Sets a cell or range to a particular value. Ref can be a reference (returned from Text.Ref), or a string representation (e.g. “A1”). If ref refers to a range, all cells will be set to the same value.

To map a list or matrix, see Set.Range. Value can be a value or a formula; to use a formula, pass a string starting with “=”.

Example:

> Set.Cell( "B1", 100 )
[1] TRUE
> Set.Cell( "B2", "=B1+10" )
[1] TRUE

Set.Array (ref, formula.or.value)

This is the same as Set.Cell, but uses the Excel function Formula.Array; like pressing Ctrl+Alt+Return in the spreadsheet, it creates an array formula.

Set.Range (ref, formula.or.value, repeat.values=T, default.value="", column.first=F)

Fills a cell (or range of cells) from a scalar, list, vector, or matrix. Entries in the list can be values or formulae (as strings, starting with “=”).

If the value has dimensions (like a matrix), the shape will be retained. If the value is one-dimensional, it will be applied in order.

Example:

> Set.Range( "B2:E9", seq(1,10))
>
> m <- diag(5)
> Set.Range( "B2:E9", m )

Get.Range (ref)

Gets a cell or range. Single cells are returned as scalars. Ranges are returned as matrices.

Example:

> Set.Range("B2:E8", seq(10,100,by=5))
> Get.Range("C3")
[1] 35
> Get.Range("C3:D5")
     [,1] [,2]
[1,]   35   40
[2,]   55   60
[3,]   75   80

Calculate.Sheet ()

Recalculates the active worksheet. This can be useful if you have the spreadsheet in Manual calculation mode.

Run.Macro ( name )

Runs a macro in the workbook. The macro name must be fully-qualified; typically this includes the name of the workbook, e.g. ThisWorkbook.Macro1.