Benchmarks Online

Skip Navigation Links


Page One

Campus Computing News

Summer Hours

Making Online Teaching and Learning Accessible

Choosing a Computer for Use at UNT

Computer-Based Training: it's baaaaack! [well, almost]

EViews 5.0 is Now Available

Today's Cartoon

RSS Matters

The Network Connection

Link of the Month

WWW@UNT.EDU

Short Courses

IRC News

Staff Activities

Subscribe to Benchmarks Online
    

RSS Matters

Link to the last RSS article here: Delivering Results to the End User: Two Stata 8.0 Examples, Part I  - Ed.

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

By Dr. Rich Herrington, Research and Statistical Support Services Manager

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.
  •  "console" device (Active X control) for local/remote R server output.
  • graphics device (Active X control) for local/remote R graphics output
  • multiple local/remote server applications ("R processes") accessible by single client
  • multiple local/remote clients per R server process
  • Add-In for Microsoft Excel to use R as the computational backend

Downloading and Installing R-D(COM)

R-D(COM) can be downloaded from the main org Website:

http://www.cran.r-project.org/

The link for this download is:

http://cran.r-project.org/contrib/extra/dcom/RSrv12r.exe

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 2003 Benchmarks 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 "Run R", "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:

Excel example

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": 

Excel example 2.

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

Excel example 3.

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

Prompt

The vector of numbers will be produced:

vector of numbers

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.

Resources

http://cran.r-project.org/
CRAN - the major site for R and associated software/documentation.
http://cran.r-project.org/contrib/extra/dcom/
Download location on CRAN for the R COM server. This is the last officially released version.
http://sunsite.univie.ac.at/rcom/
The home of the R COM server and clients. You can find latest snapshots and additional documentation there.
http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l
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.