Easy Excel and R.
The Function Folder is the location that the Add In will look for function files, the default, if left empty is set to the users documents folder and the sub folder EasyER which is generated by the EasyER installer.
If this folder is changed and no function XML files are placed in it. When EasyER is next run it will automatically copy the 3 example files, Charts, Regression and Tests into it.
R Path and Home should be left blank. These only need to be set if you are connecting to a non standard R installation.
Only version of R with the same bit version as Excel can be connected to. So if you want to connect to 64 bit R you need to be running a 64 bit version of Excel.
If you want EasyER to install any required packages required then leave this option checked. EasyER will create a folder to install package in the users documents folder, ..Documents\R\win-library\3.4 where the number is the version of R installed. Uncheck this option if you want to manage the packages yourself. There are still issues with the automatic installing of packages on some systems, so if you experience problems then install packages manually, see the download page for details.
The CRAN Mirror defines which of the CRAN Mirrors will be used to automatically install R packages from. Any R package defined in a function file that the connected to R hasn’t got installed will attempted to be installed when the function file is loaded.
This last options allows the image properties of the R charts generated to be changed. The width and height are in centimeters. The resolution sets the dpi of the image. The defaults are width 15 height 12 and resolution 600.
This display the details of the R session that EasyER has connected to.
This just shows information about EasyER including the version number and the End User License Agreement
This button opens the chart builder pane. For this to succeed there must be a range of data selected on the worksheet. The selected data will then be imported into R allowing the chart builder to generate plots from it.
The theme option brings up the options that can be set to alter the look of the plots produced by the chart builder.
This toggle button makes the function pane automatically update. When selected along with a function selecting different ranges of data will result in the Function pane updating the data to match the new range.
This button opens the Function pane and when clicked for the first time after opening Excel starts the Add In by connecting to R. Once started the connection can only be stopped by closing Excel.
If R is found and the function files are valid the Function pane will open with menu options for each of the function files. Each menu option contains a button for each function defined.
The Function Pane
The function pane is used to control the selection and generation of results from the available functions.
The basic operation of the tool is that a range of data is selected and then the required function is selected. If the data is suitable then the Function pane will load the function and either attempt to perform the function or wait until the ‘Execute’ button is pressed.
Currently data is treated on a column by column basis. Trying to run a function on a row of data will not work.
The contents of the first cell of a selected column will be treated as the label for the data. If this is a number or begins with a number then the name appearing in the Function options will have a ‘V_ ‘automatically added to it. This enables R to handle the data correctly.
If the data column required has no further data in cells underneath then the whole column can be selected. If the data is part of a bigger sheet then the specific data needs to be selected.
The function pane is split into 5 sections.
The menu bar at the top of the pane shows the function files that are loaded. Each option in the menu is a function that will operate on the selected data range.
This displays any charts that have been generated by the functions. If more than one chart is generated then a scroll bar will appear that allows each chart to be viewed. Right clicking any chart allows it to be copied and placed onto a work sheet once a location is selected.
This box is displayed when relevant, and contains all the options available to the user to modify parameters of the function.
This box is displayed when relevant, and shows three buttons.
This will run the function using the parameters as selected in the ‘Function Options’ control. Charts and Text Output will be updated with any results.
Clicking this button brings up an option to select a cell on a worksheet. Once set then all results, charts and text output will be copied to the worksheet.
This option will attempt to load the current active range into the function for calculation.
Here any text output from functions will be displayed. It can be copied just like any text document.
EasyER is still being developed and there are some aspects of the structure of the functions that may change. For that reason we have not posted detailed notes on how the functions are defined. The example files that ship with EasyER incorporate all the current functionality available, so examination of those is the best way to get to know the system.