Using R with Excel - A (D)COM Server for the
Windows Platform (Part I)

This
month we demonstrate how to download and install a COM server
used to connect a client application (e.g. Microsoft Excel) with R.
R is the GNU S statistical system
"R", for the Microsoft Windows platform. The following is an
excerpt from the R Website
http://www.r-project.org/:

R is a language and environment for statistical
computing and graphics. It is a
GNU project which is
similar to the S language and environment which was developed at Bell
Laboratories (formerly AT&T, now Lucent Technologies) by John Chambers
and colleagues. R can be considered as a different implementation of
S. There are some important differences, but much code written for S
runs unaltered under R. R provides a wide variety of statistical
(linear and nonlinear modeling, classical statistical tests,
time-series analysis, classification, clustering, ...) and graphical
techniques, and is highly extensible. The S language is often the
vehicle of choice for research in statistical methodology, and R
provides an Open Source route to participation in that activity. One
of R's strengths is the ease with which well-designed
publication-quality plots can be produced, including mathematical
symbols and formulae where needed. Great care has been taken over the
defaults for the minor design choices in graphics, but the user
retains full control. R is available as Free Software under the terms
of the Free Software
Foundation's GNU
General Public License in source code form. It compiles and runs
out of the box on a wide variety of UNIX platforms and similar systems
(including FreeBSD and Linux). It also compiles and runs on Windows
9x/NT/2000 and MacOS [from Introduction].

An R-D(COM) Server for R

R-(D)COM is a programming interface to COM and DCOM (ex ActiveX;
Microsoft distributed object interface) to access the R calculation
engine. As such, it runs only under the Windows environment. The
R-(D)COM server can be used to build a R GUI client using tools like
Microsoft C++, Microsoft Visual Basic, or Microsoft Excel. Currently, a
R-Excel addin is provided, as well as some examples to use Visual Basic
as a front-end. Some of the features of the R-(D)COM server are:

data-transfer both from
R to
the client-application and vice versa, currently supporting scalars
(Booleans, integers, doubles and strings) and arrays of these.

Downloading and Installing R-D(COM)

Installation allows you to choose between various default types of
installation.

If you want to develop client applications, you should choose a
"Development installation". This will install binaries for running
local and remote servers, test and sample applications, including
source code. An installation of
R is
required on this machine, too (See
June 2003Benchmarks Online for downloading and installing R). When
deploying your own client application to some machine, you have to
install R
(D)COM Server on this machine, too.

If running R
locally on this machine, choose "Local Server Installation". This will
install binaries for the local COM servers and a simple test
application used for testing the basic functionality of
R and
the R
(D)COM Server installation. A local installation of
R is
required, too. This machine can be used for running client
applications using a local
R
server and for providing
R
server objects for remote clients, too.

For running a client application locally and accessing
R
objects installed on a remote server, choose "Remote Server Files" as
your setup option. This will set up necessary binaries for accessing
an installation of R and R (D)COM server on a remote machine.
R is
not required on this machine. Please consult your network
administrator for potential security risks that are created when
giving remote access to D(COM) servers. For our
demonstration purposes, remote access is not given.

Select Basic Test from the
R (D)COM
Server program group in the start menu and press "Start".

RExcel - Using R from within Excel

The Excel addin RExcel.xla allows to use
R from
within Excel. The package additionally contains some Excel workbooks
demonstrating different techniques for using
R in
Excel. There are at least three different ways of using
R from
within Excel:

Scratchpad mode

Writing R
Code directly in an Excel worksheet and transferring scalar, vector,
and matrix variables between
R and
Excel

Macro mode

Writing macros using VBA and the macros supplied by
RExcel.xla, attaching the macros to menu items or toolbar items

Worksheet functions

R
can be called directly in functions in worksheet cells

Scratchpad Mode

The RExcel menu within Excel contains the following items:

R Start

Starts an R
process to be accessed from Excel in the background

Close R

When this process is running, "R Start" changes to "Close R"

Run Code

Selecting a range (only one column wide) in Excel containing valid
R code
and then choosing this menu item executes the selected code

Get

Gets the value of an
R
variable into the active Excel cell

Put

Puts the values of the selected Excel range into an
R
variable

Copy Code

Puts the content of the selected range on the Windows clipboard,
assuming that it is R code, and wraps the code in VBA procedure calls to
make it ready for inclusion in VBA macros

Debug R

Switches to debug mode: all the calls to
R will
be displayed in a popup window before they are executed

Options

Allows to change some aspects of the layout of the RExcel menu
structure

RExcel Help

Displays help file

R Help

Displays the R
help file

"Get" and "Put" interactively prompt for the name of the
R
variable. The type of the
R
variable (numeric or string) is selected from a submenu. The value if
the R
variable only may be a numeric or string scalar, vector, or matrix.

When the R
process is running, the context menu for cells (accessed by right
clicking on a cell or selecting a range and then right clicking)
contains the menu items "RunR", "Get R Var", and "Put R Var"
which perform the same functions as the corresponding menu items in the
"RExcel" menu.

Some ways of using these techniques are illustrated in the example
file RDemoDev.xls, available as "Excel 01 - R Server
Connection Demo" in the (D)COM Server menu in Windows Start
menu.

An Example Using the Scratchpad Mode

In the following example, the scratchpad mode is demonstrated.
Start R from the main menu bar in Excel:

Then, in a cell (or group of cells), using the R language, assign 10
random numbers to the object "x" ("x<-rnorm(10)"). Highlight the cell (or group of
cells) containing the R code, then right mouse click on the highlighted
cell(s) and select "Run R":

Next, right mouse click a cell, then select "R - Get - Num":

Then, retrieve the object by typing the name of the assigned object
at the prompt (in this case, object "x"):

The vector of numbers will be produced:

In the following example, two vectors are assigned to "x" and "y",
and the sum of the vectors is assigned to "z". The object "z" is
retrieved and the following vector is produced:

Combining the R and Microsoft Excel software together in this way,
brings a large degree of statistical functionality to Excel. R
draws upon thousands of functions and has the capability of handling
matrix algebra (See the list of packages and functions in the R help).

Next Time

Next time we will demonstrate the "Macro Mode" and "Worksheet
Functions" facilities of the R-(D)COM server.

The mailing list for all R COM services. Discusses all topics
related to the R COM servers and the R COM client package. See this
page for information about the list, subscription etc. See the R FAQ
for information about general mailing lists concerning R.