BERT is an XLL Excel Add-in, which means it can use the Excel C API. This is a powerful, and potentially dangerous, way to interact with Excel. It is generally faster than using COM (like VBA) because it does not have any abstraction.
Occasionally though, we need features that VBA doesn't provide. In these cases, we have to use the function calls that are available in Windows. The functions available in the Windows API are documented in the MSDN library with terminology and samples targeted for C developers, which is not familiar to VBA developers.
The file ExcelFunctions.R included in the BERT install directory includes a number of helper functions which call various Excel functions. These should be easier to use than using the Excel API directly. Excel has a lot of historical weirdness which makes it tricky to do things by hand - for example, you cannot set a cell formula using A1 notation, you have to use R1C1 notation. The wrapper functions will take care of all this for you.
Call Context
Excel API functions are context-specific, meaning that some functions can only be called in a macro context; other functions can be called from spreadsheet cells. Generally macro context functions are labeled in Excel documentation with 'macro sheets only'.
If you call a macro-only API function from a spreadsheet cell, the call will fail.
The [BERT console] [1] operates in a macro context, so you can use macro-only functions in the console. Functions that you call from the spreadsheet are naturally in a spreadsheet context and are more restricted. The Excel helper functions described below are for use in a macro context.
Excel Functions
To include the Excel helper functions, source the ExcelFunctions.R file in the BERT console or in your startup file:
See the source file for the details. Here are the basic functions:
- Set.Cell( reference, formula )
Sets a cell's formula. Only a single cell can be set at a time.