Writing good code for Excel (VBA) and R

Using R and Excel together can be very productive, but it is also difficult because one has to think in 2 or even 3 rather different programming models simultaneously.

R itself is extremely powerful. It combines features of functional, object oriented, and imperative programming. Typically, an R functions is applied to some data and a complex object containing many different subunits is returned. In many cases, parts of these compound objects then need to be transferred to Excel.

Excel has two different programming models.
Worksheet functions take inputs from worksheet cells and compute new values. RExcel (through the worksheet function RApply and its relatives) allows to use R functions as Excel worksheet function. RExcel ships with a demo workbook illustrating this kind of use. The interface between R and Excel only handles dataframes and matrices. An R function returning a more complex compound data type cannot be used to directly transfer R results into Excel. RApplyC, however, allows to extract a component from a compound R object.

Excel also includes VBA, an imperative and object oriented programming language. RExcel enhances VBA by RInterface.RRun which accepts a complete and valid R statement and runs it. RInterface.GetArray and RInterface.GetDataframe allow to transfer arrays and dataframes from R to Excel. Transfer in the other direction is also possible.

RExcel is not intended to teach you programming in either VBA or R. For both purposes there are quite a few good books.

The demo files coming with RExcel (currently 5) illustrate the different techniques for combining R and Excel.

Personal Tools