Trouble Running RExcel Application on Machine with Dual Processors

I run it with one CPU – all works great. With 2-CPUs – the application stops dead, EXCEL fails. When EXCEL comes back I get the message, “EXCEL had a severe problem with REXCEL ADDIN, do you want to disable this ADDIN?”

Has anyone had similar problems? Possible solutions?

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.

Debugging VBA code containing calls using RInterface

To debug VBA code contaiing calls to RInterface

  • Install the package rcom if it is not installed yet
  • From the RExcel menu do Set R Server→Foreground→RGui visible
  • In the VBA IDE put the cursor in the macro you want to debug
  • Use F8 (or Shft-F8 or Ctrl-Shft-F8) to step through you code
  • After each single command you can switch to R to see output
  • you can also inspect R variables in RGui and VBA variables

F8 only works with macros with no parameters. If the macro you want to debug has parameters, you must write another macro containing the call of the macro with the necessary parameters. Then you can start debugging from this new macro.

Duncan Murdoch's site has tips on debugging R

Problems with VBA accessing other objects

On some machines, RExcel stips working and indicates that it cannot execute

CreateObject("Scripting.FileSystemObject")

The most probable reason is a problem with 'the 'Scripting.FileSystemObject'' which is a part of the Windows Scriptimg Runtime installed as part of Windows. Reintalling the Windows Scripting Runtime usually helps,

Details can be found at Please have a look at http://phorums.com.au/archive/index.php/t-163519.htm and http://forums.iis.net/p/912841/1027622.aspx


Personal Tools