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.

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.

Returns | Reference (Class Type) |

reference.text | Reference (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
```

Returns | Success (Boolean) |

ref | Excel reference (from Text.Ref) or String |

formula.or.value | String, 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
```

Returns | Success (Boolean) |

ref | Excel reference (from Text.Ref) or String |

formula.or.value | String, 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.

Returns | Nothing |

ref | Excel reference (from Text.Ref) or String |

formula.or.value | Any scalar, vector or matrix type |

repeat.values | Controls recycling values if list is smaller than target range (Boolean) |

default.value | If values are not recycled, the default value to fill |

column.first | Fill 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 )
```

Returns | Scalar or Matrix |

ref | Excel 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
```

Returns | Success (Boolean) |

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

Returns | Varies |

name | Fully-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`

.