Easy Excel and R.
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.
This open a dialog which 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.
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 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 must also be added to the Windows Environment Variable, Path, which requires admin rights. This is vital otherwise the automatic loading of R packages will not work correctly, and the Add In will not load. If you want to handle the installation of packages yourself then check ‘Ignore R Path not set’. This will allow EasyER to run without setting the path but unless the R installation has the correct R packages functions will generate errors.
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.
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 just shows information about EasyER including the version number and the End User License Agreement
This open a information dialog that shows which version of R the Add In is connected to.
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.