How’s that for a page title?

In this tutorial, we’ll document how to do two things:

  1. Install BERT2 to embed R functions in Excel
  2. Install the R package rClr to embed DotNET dll files in R

The end result will be the ability to use a DotNET dll in Excel worksheet calculations.  You may be asking, “Why go through such a roundabout process to integrate two things that should easily talk to one another?” It’s true[1], there are many[2] documented methods[3] for accomplishing this, but honestly, we had trouble getting them to work consistently.  So we looked for a better way.  We like the BERT2 solution because

  1. It doesn’t require the use of VBA code and .xlsm workbooks
  2. It has a pleasant side effect – the integration of R into Excel, giving easy access to the entire universe of R packages from within the friendly confines of a Microsoft Excel spreadsheet.

So, let’s begin:

First, Install BERT2

A quick note: because, in this exercise, we’re ultimately aiming to install the rClr package, we won’t be installing the latest version of BERT2.  Because rClr is currently (as of January 2020) only compatible with R version 3.5.0, we will choose a vintage version of the BERT2 installer that is based on R version 3.5.0 as well, rather than a newer version, if one is available.  As of this date (again, January 2020), the most current version of BERT2 is 2.4.4, which is based on R v3.5.0 — so no problem. Long story short: pay attention to versions here.

BERT stands for Basic Excel R Toolkit.  It’s an open-source project aimed at making it easy to call R functions and control R easily from within Excel.  You install it as an Excel Add-in.

  1. Go to the BERT2 github Releases page and download and install BERT-Installer-2.4.4
  2. Once BERT2 is installed, launch Microsoft Excel, go to the Add-Ins menu bar, and launch the BERT2 Console.  You’ll now have an R console running on the right side of the BERT console.
  3. Leave the BERT console here for now

Next, Install the rClr package in BERT’s R environment

As above, version number is important.  rClr does not work with R versions beyond v3.5.0.  As of January 2020, the current version of R is v3.6.2.    In particular, we will want to install the rClr package v.0.8.3, which is documented as compatible with R v3.5.X.

Unfortunately, rClr is not available via the standard R package managers; you’ll have to manually download and install from the v0.8.3 executable.

  1. Go to the rClr github Releases page and download the rClr_windows_pkgs.7z tarball
  2. Unpack the contents of rClr_windows_pkgs.7z into a temporary directory.  You will need this file: \rClr_windows_pkgs.7z\R_pkgs\bin\windows\contrib\3.5\rClr_0.8.3.zip.  If you need help unpacking .7z files, download and install 7-Zip from www.7-zip.org
  3. Go back to the BERT2 console, and type install.packages (pkgs='c:/path/to/rClr_0.8.3.zip’)where c:/path/to/ is the full path describing the location of your extracted .zip file.
  4. This should complete the installation of rClr in BERT’s R environment.  To test this, type library(rClr) in the BERT’s R console.   If you receive no error message, then the rClr package has been successfully installed.

Congratulations!  You’re Done!

You can now use R functions (and DotNET DLLs) as Excel worksheet functions!  If you’d like an example, using a DLL created by Cognalysis MultiRate v5, head on over to the MultiRate API Guide page.