November 7, 2017

EasyER, Using R in Excel


Running for the first time

Once installed the Excel ribbon will show the EasyER tab. The Add  In is by default inactive when Excel starts. Once the Functions button is clicked for the first time after opening Excel the Add In will start up and attempt to connect to the local R installation.

EasyER requires the R package R.Devices to be installed. If this is not found then it will be downloaded and installed automatically. The CRAN mirror used can be set, see details below.

The Excel message bar will inform on progress. If you have R installed using the defaults then their shouldn’t be a problem and the Add In will show the Functions pane.

The Installer also generates a folder in the users MyDocuments folder, ‘EasyER’. This is the default folder for the Add Ins function files to be saved. There is also a sub folder generated also called ‘EasyER’ where R code and images generated by the Add In are saved. This allows inspection of R code that the Add In is using.

Setting up

The Set-Up button on the ribbon allows the location of the function files to be set and the R installation to connect to.

Function Folder

The Function Folder is where EasyER will look for function files. If this is left blank then it will be set to the default when EasyER is started for the first time. The demonstration function files that are bundles with the Add In are copied into this folder if it is found to be empty. This check is done every time EasyER starts so if another folder is selected then if left empty will be populated with the demo functions.

R Path and R Home

R Path and Home should be set. When run for the first time EasyER will do a simple registry search for R and the bit version of Excel. R Home is set to the R installation folder and the bit version of Excel is used to determine if R Path should point to the i386 or x64 folder.

R Path is taken and added the the Windows Environment Variable, Path. This is vital otherwise the automatic loading of R packages will not work correctly. If all this sounds too technical then with a default R installation you shouldn’t need to change these values. If you know all about R and have a custom install then you may need to adjust these settings.

CRAN Mirror

This is the CRAN mirror that will be used to download any missing packages.

R Connection

Clicking on the expander button in the bottom left hand corner of the Configure tab will display the details of the R version connected to.

The R connection is for the Add In only. There is no way to access the R session EasyER is using.

Data Size

EasyER is a tool for Excel so doesn’t do ‘Big Data’. There is a ‘Test Data’ function in the the ‘Tests’ functions which generates 10 random variables the length of which can be changed. This is to enable testing the Add In with the size of data expected, and to see if the speed is suitable. On our development machines, EasyER handles columns of data with 10 to 100 thousand values without trouble. When the data point count gets to the millions then it becomes a bit sluggish.

Here are some further details on how to use EasyER