RExcel and R(D)COM specific problems

Excel does not display the RExcel menu item

Most probably you are not logged in as the user who installed RExcel.
Currently, it can happen that the RExcel addin is only visible for the installing user.
The remedy is to download the file rexceladdininstall.xls,
putting it into the RExcel directory (normally C:\Program Files\R\R(D)COM server\xls),
opening it in Excel, and having it install the addin for the current user.

RExcel cannot display results of a function call

Excel can only handle scalars, vectors, or arrays of the basic types string (=character), numeric, complex, or time and date. Additionally, it can dataframes. It cannot handle lists with components of different types.

Therefore, it is not possible to display the result of

lm(y~x)

directly in Excel. Instead, using the result of

lm(y~x)$coefficients

will work

Running R code automatically

When Excel starts the connection to R either by

  • the menu item R Start or
  • by calling the macro Rinterface.StartRServer or
  • because the Excel workbooks contains worksheet functions calling R

the following happens

  1. Excel looks for a file RExcelStart.R in the directory where RExcel.xla or RExcel.xlam lives and sources it if it finds it
  2. Excel looks for a file RExcelStart.R in the directory of the active workbook and sources it if it finds it

Using this mechanism it is possible to load packages and define functions and variables either site wide or for specific projects.

Getting the complete output of an R command into Excel

Here is a “pure R” way of creating a command that captures R output in VBA:

cmdString should contain your commands

myOutput<-capture.output(print(try({" & _cmdString & "},silent=TRUE)))"

will give you a character vector containing the output. You can then get the value of myOutput into Excel and put it into a worksheet.

This will work in rcom and in R(D)COM, or, expressed differently, with foreground and and background server.

Directories in R and in Excel

The directory separator character in R is the forward slash, in Excel (and VBA) it is the backslash. The backslash is the escape character for strings in R. Therefore, to use VBA to change Rs working directoty, the command has to be

RInterface.RRun("setwd(""c:/work/subdir/myfile.RData"")")

or

RInterface.RRun("setwd(""c:\\work\\subdir\\myfile.RData"")")

Single backward slashes will not work.


Personal Tools