Home > R > XLLoop framework

XLLoop framework

Today I want to highlight the XLLoop framework : Excel User-Define Functions in in any language.

The XLLoop consists of two main components:

  • An Excel addin implementation (XLL written in c++).
  • A server and framework written in R (or/and in many other languages).

The XLLoop allows you to connect Excel and R in very simple way with almost zero installation.

To get started, please download and unzip the xlloop-basic.zip archive that contains all files you need to make my sample application work.

Next, start Excel and add XLLoop addin.
I.e. in Office 2007/2010 Click File->Options->Add-Ins, press Alt+G, Click Browse and locate xlloop-0.3.2.xll
in Office 2003, Click Tools->Add-Ins, Click Browse and locate xlloop-0.3.2.xll

Next, edit runr.bat that was extracted from the zip archive. Enter correct path to your R installation.

Finally, execute runr.bat, you will see a command window popup, next go to Excel and type following formula
=FS(“ProductTest”, 32, 1886.5)

If all works well, you would see 60368

This might seem like a bit of black magic, so let me explain what is going on:

The runr.bat batch file starts a new R session and executes rstart.r script. In the rstart.r script we load/define any libraries / functions that we want to access in Excel. Next we load code for the XLLoop server and start the server. Here is the code in the rstart.r script

# define function
ProductTest <- function(x, y) x*y

# start xlloop server
source('xlloop.R')
XLLoopServer()

Next to access R functionality in Excel, we use FS function, the first parameter is the R function that we want to execute, following by function parameters. For example the =FS("ProductTest", 32, 1886.5) formula calls ProductTest R fucntion with x = 32 and y = 1886.5

If you get “Cannot connect to the server” error message, please make sure that runr.bat batch is running (i.e. there is a command window) and try recalculating Excel (i.e. press F9)

Once, you are done working with Excel, you can just close the command window created by runr.bat batch file.

I have included a few examples in the xlloop.xls for you to explore.

I will show a few more examples of the XLLoop framework in the next post.

Please let me know what problems you run into while experimenting with XLLoop.

A side note. There are many options for connecting Excel and R. For example I have previously showed examples of RExcel to execute R functions and display their output in Excel.

About these ads
Categories: R
  1. Pete
    December 8, 2012 at 9:06 am

    It works, thank you

  2. December 8, 2012 at 11:26 am

    I’m amazed I didn’t know about this before, such a useful tool. Truly a shame they don’t support C# though.

  3. December 8, 2012 at 11:30 am

    what about the speed comparison against rexcel? any advantage of using xlloop?

  4. Paolo
    December 10, 2012 at 5:49 pm

    Similar question to Jozef…which advantages would see against RExcel?

  5. December 11, 2012 at 1:45 am

    The XLLoop uses a socket connection to communicate data between R and Excel, so I would expect it to be slower than RExcel.

    There are a few interesting features that XLLoop provides (that are not easily available in RExcel):

    • * XLLoop can communicate with R installed on the remote (different) computer
    • * XLLoop can communicate with multiple R sessions, just start XLLoop server on a different ports / computers
    • * XLLoop allows you to define User Defined Functions (UDF) in Excel, so you can call:
        =FS(“ProductTest”, 32, 1886.5) or simply
        =ProductTest(32, 1886.5)
  6. Nick Iversen
    December 11, 2012 at 8:49 am

    Will XLLoop work under Windows 64 bit? RExcel doesn’t.

    • QP
      December 19, 2012 at 7:16 am

      RExcel does work (quite well) on Windows 64 bit with Office 32x. It’s COM architecture has stood the test of time.
      Thanks for posting code on an alternative using XLLoop.

  7. December 12, 2012 at 1:07 am

    Yes, but you need the 32bit version of Excel. Please read more at

    https://github.com/poidasmith/xlloop/issues/5

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 237 other followers

%d bloggers like this: