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.

Note: there are actually two ways to talk to Excel from R with BERT; using the Excel API, and using the Excel scripting (COM) interface. This page describes using the Excel API. We have another page describing the Excel scripting interface.

Which one should you use? That depends what you are trying to do, and perhaps if you are familiar with one or the other. The Excel API (this page) is simple to use and fast. The scripting interface can do a lot more, but is also more complicated to use. The best thing to do may be to experiment with both and use the one that works best for you.

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)

ReturnsReference (Class Type)
reference.textReference (String)

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)

ReturnsSuccess (Boolean)
refExcel reference (from Text.Ref) or String
formula.or.valueString, boolean, or number

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)

ReturnsSuccess (Boolean)
refExcel reference (from Text.Ref) or String
formula.or.valueString, boolean, or number

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)

ReturnsNothing
refExcel reference (from Text.Ref) or String
formula.or.valueAny scalar, vector or matrix type
repeat.valuesControls recycling values if list is smaller than target range (Boolean)
default.valueIf values are not recycled, the default value to fill
column.firstFill order

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)

ReturnsScalar or Matrix
refExcel reference (from Text.Ref) or String

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 ()

ReturnsSuccess (Boolean)

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

Run.Macro ( name )

ReturnsVaries
nameFully-qualified name of the macro

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.