How’s that for a page title?
In this tutorial, we’ll document how to do two things:
- Install BERT2 to embed R functions in Excel
- 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, there are many documented methods 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
- It doesn’t require the use of VBA code and .xlsm workbooks
- 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.
- Go to the BERT2 github Releases page and download and install BERT-Installer-2.4.4
- 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.
- 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.
- Go to the rClr github Releases page and download the rClr_windows_pkgs.7z tarball
- 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
- Go back to the BERT2 console, and type
c:/path/to/is the full path describing the location of your extracted .zip file.
- 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.