Call R from Excel. Create new Excel functions that make use of R packages. Integrate both tools seamlessly.
A work in progress...
For documentation, check the spreadsheet file "xlRcode - Main.xlsx".
New Excel functions
- XLRCODE
- Run R code in a local environment and transfer values from and to Excel. Function memory is cleared after calculation.
- XLRCODE_ENV
- Run R code in a pre-specified environment and transfer values from and to Excel.
- XLRSCRIPT
- Run R script in a local environment and transfer values from and to Excel. Function memory is cleared after calculation.
- XLRSCRIPT_ENV
- Run R script in a pre-specified environment and transfer values from and to Excel.
- XLRFUNC
- Call an R function in a local environment and transfer values from and to Excel. Function memory is cleared after calculation.
- XLRFUNC_ENV
- Call an R function in a pre-specified environment and transfer values from and to Excel.
- XLRDATE
- Convert Excel dates to R dates or vice-versa.
It only works with Windows.
This add-in has been tested on Microsoft Excel 365 64-bit. It may work with other versions of Excel for Windows, but it is not guaranteed.
You are going to need that R is installed on your machine. You can download an installer from here: https://cran.r-project.org/index.html.
This add-in was tested with R v. 4.0.2. It may work with other versions, though.
If you already have R installed on your machine, you may wish to make an independent installation just for this add-in. It is up to you.
- On this webpage, download the ZIP file with the latest version of xlRcode.
- Find on your computer the zip file you have just downloaded.
- Extract all files in the Release subfolder of the zip file to a folder on your computer. This is where the add-in will be installed.
- Open Excel. Go to File / Options / Add-ins. Then on the drop-down named "Manage", select "Excel Add-ins" and click on "Go...".
- It will pop-up a window named "Add-ins". Click on "Browse...".
- Find the folder you have created for installing the add-in. From there access the subfolder "Release" and select the file xlRcode-AddIn64.xll. If your Excel is 32-bits, you should select xlRcode-AddIn.xll instead. Click on the "Open" button.
- Back to the "Add-ins" window, click on the "OK" button.
Whenever you open Excel, the add-in should be automatically loaded now!
- In the Ribbon, you should see a new tab named "xlRcode". Click on it.
- Click on the button "SetUp". A window named "xlRcode SetUp" will pop-up.
- You will need to set the paths for the folders of your R installation.
- R Home should be equivalent to "C:\Program Files\R\R-x.x.x", where x.x.x stands for the version of your R installation.
- R Path should be equivalent to "C:\Program Files\R\R-x.x.x\bin\yyy", where x.x.x stands for the version of your R installation and yyy is either x64 or i386. If your Excel is 32-bits, you should choose i386.
- Scripts folder could be any folder, but I strongly advise you to create a new folder and copy to it the scripts of the "R Scripts" subfolder located inside the folder you installed xlRcode.
- You will not be able to pick a packages repository until you restart Excel. Later on, feel free to do it.
- Click on the "Save" button.
- Restart Excel!
That should do the trick!
- Close Excel.
- If, by any chance, your Scripts folder is inside the folder you previously installed this addin and you have added or made changes to any of its scripts, make a backup copy of your Scripts folder.
- Go to the folder you previously installed this addin and delete all of its contents.
- On this webpage, download the ZIP file with the latest version of xlRcode.
- Find on your computer the zip file you have just downloaded.
- Extract all files in the zip file to the same folder in your computer you had previously installed the add-in.
- If step-2 advice applies to you, this is the time to bring the backup scripts back to their original folder.
- Open Excel.
- In the xlRcode ribbon tab, click on the "About" button and check whether the version displayed matches the version just installed.
That should do the trick!
- In general, tips for making R code faster also apply to xlRcode. You can find some tips in the following links: https://www.dartistics.com/fast-r-code.html https://bookdown.org/content/d1e53ac9-28ce-472f-bc2c-f499f18264a3/speedtips.html
- Use array formulas whenever possible as they reduce the amount of individual calls from Excel to R and therefore each function overhead time toll.
- Time-demanding commands/scripts should be called with care from cells because the R status while running code may be opaque.
- You can split commands in different cells and provide the reference to their joint address as the code argument in XLRCODE or XLRCODE_ENV functions. Cells will be read in the same environment in order from left to right and then from top to bottom.
- You can split script paths in different cells and provide the reference to their joint address as the code argument in XLRSCRIPT or XLRSCRIPT_ENV functions. Cells will be read in the same environment in order from left to right and then from top to bottom.
Major improvement and bug handling.
- Major improvement: all XLRCODE functions are now asynchronous!
- Major improvement: all XLRCODE functions run in an exclusive thread with stack size of 4Mb. This solves stack overflow exceptions that were raised when loading some popular packages such as 'devtools'.
- Improvement: functions now distinguish between NA and NAN, so that they return #N/A! and #NUM!, respectively.
- Improvement: functions XLRCODE and XLRCODE_ENV now read commands in multiple cells in order.
- Improvement: functions XLRCODE, XLRCODE_ENV, XLRFUNC, and XLRFUNC_ENV now read parameter names and values listed in multiple cells.
- Improvement: functions XLRSCRIPT and XLRSCRIPT_ENV now read script paths in multiple cells in order.
- Improvement: search mechanism in the form of installed packages is now case insensitive by default, but allows case sensitive searches as well.
- Improvement: functions that return NULL to Excel are displayed as #NULL!.
- Improvement: R commands with no return value, such as comments, now return an empty string when called from XLRCODE functions.
- Improvement: Packages form now exhibits a description for each package installed.
- Improvement: Packages form now a tab of "Old Packages" that shows for which packages there are updates available on the CRAN server.
- Improvement: ExcelDNA logging is now redirected to an external file.
- Improvement: Calculate range button on the ribbon now allows for recalculation of the entire active worksheet or the entire active workbook.
- Improvement: a new group of buttons to refresh pictures was added.
- Improvement: installing/uninstalling and loading packages now specify their library.
- Bug fix: function XLRDATE was not successfully converting Excel dates to R ones because the latter were being reconverted to Excel format before returning to the spreadsheet.
- Bug fix: when one tried to open R console as administrator but process failed, one would have to restart Excel to try it again.
- Bug fix: when XLRDATE was applied to a two-dimensional matrix of Excel dates it was returning all values stacked up in a single vector.
- Bug fix: error was raised when a XLRCODE function was called before the Console form was displayed and "Register Excel function calls" was enabled.
- Bug fix: XLRCODE_ENV and XLRFUNC_ENV functions raised error when "Environment" argument contained invalid characters, such as " " and "=".
- Bug fix: XLRCODE and XLRCODE_ENV functions raised error when the "commands" argument was a range of cells that included an empty cell. Now, it ignores empty cells.
- Bug fix: when user passed an invalid environment argument to XLRCODE functions, Excel crashed.
- Bug fix: some forms and error messages were being displayed for the first time in a different monitor than the one currently occupied by the Excel app.
- Bug fix: R console only displayed lists whose elements had names.
- Bug fix: code arguments in XLRCODE functions that included cells with comments were raising error.
Minor improvement and bug handling.
- Improvement: ExcelDna was updated to version 1.6.1-beta3.
- Improvement: when user opens R terminal through ribbon, xlRcode now asks whether admin privileges should be requested.
- Improvement: dataframe datatypes "dfh", "dfc", and "dfr" now check whether dataframe content is only numerical or not.
- Improvement: R code error messages returned from XLRCODE functions are displayed as in R.
- Improvement: "Graphic" button on the ribbon now brings to front current X11 graphic device in case there is one open.
- Bug fix: String outputs were being parsed from UTF-8 (R's default encoding) to UNICODE without due conversion.
- Bug fix: R console messages were duplicated in case of error.
Minor improvement and bug handling.
- Improvement: xlRcode is still not passing through some anti-virus detectors due to a false alert raised for ExcelDna XLL files. ExcelDna was updated to version 1.6.1-beta1. Problem persists, though.
- Improvement: Matrix arguments with mixed datatypes can now be parsed by xlRcode functions. Each element is converted to the string datatype as in R.
- Bug fix: String arguments were being parsed from UNICODE to UTF-8 (R's default encoding) without due conversion.
- Bug fix: Function XLRCODE_RTD still under development was visible.
- Bug fix: Logical matrix arguments were not being parsed by xlRcode functions.
Minor improvement and bug handling.
- Improvement: xlRcode is still not passing through some anti-virus detectors due to a false alert raised for ExcelDna XLL files. ExcelDna was updated to version 1.6.1-alpha3. Problem persists, though.
- Bug fix: Empty cells and Excel error values in numerical dataframes were raising errors or being wrongly parsed. Now, they are parsed as NaN.
- Bug fix: Reload Scripts' message box was being displayed behind other forms.
Minor improvement and bug handling.
- Improvement: xlRcode is still not passing through some anti-virus detectors due to a false alert raised for ExcelDna XLL files. ExcelDna was updated to version 1.6.1-alpha2. Problem persists, but for a lower amount of anti-virus programs.
- Improvement: XLRCODE functions now accept the argument suffix "(asis)", which parses a string as literal code. For example, this allows parsing a funcion code as a function object.
- Improvement: visible output of R commands invoked from the console were being suppressed as the internal function XLRCODE_Routine was running in a different thread.
- Improvement: code console execution is stopped if user presses ESC.
- Bug fix: an error was raised when an already visible form was asked to be showed again.
- Bug fix: xlRcode functions which have an environment parameter raised an unhandled error when environment name was invalid.
- Bug fix: code console intellisense raised untreated error if R engine was not working.
Minor improvement.
- Improvement: xlRcode was not passing through some anti-virus detectors due to a false alert raised for ExcelDna XLL files. Packing was disabled for it is unnecessary and ExcelDna was updated to version 1.6.0-rc1.
Minor improvements and bug handling.
- Improvement: xlRcode functions have now full descriptions, including their arguments, in the Function Wizard.
- Improvement: xlRcode functions have now intellisense.
- Improvement: user can specify a packages library folder at the SetUp form.
- Bug fix: arguments of functions XLRCODE, XLRFUNC and XLRSCRIPT were being processed in the global environment instead of a temporary one.
- Bug fix: non-modal forms were being exhibited at the top of all windows, including those outside Excel. Now, they are exhibited at the top of Excel.
- Bug fix: listing Environment objects when object was boolean raised untreated error.
Bug handling.
- Bug fix: boolean arguments in the xlRcode functions were not being correctly parsed from Excel to R.
- Bug fix: strict type parsing of arguments in "Make it Lambda" functions was not working and it was disabled for now.
- Bug fix: last optional argument of xlRcode functions was not being transferred from Excel to R.
Major change, minor improvements and bug handling.
- Major change: functions XLRFUNCTION and XLRFUNCTION_ENV were renamed to XLRFUNC and XLRFUNC_ENV, respectively;
- Improvement: functions XLRFUNCTION and XLRFUNCTION_ENV now accept datatype coercion suffixes in their parameter names.
- Improvement: the R datatype Sys.Date is now recognized and automatically converted to Excel dates when it is the output of a xlRcode function.
- Improvement: the R datatype Sys.Date is now displayed in the format 'YYYY-mm-dd' in the console. It was being displayed as an integer.
- Improvement: new function XLRDATE allows the user to convert dates from Excel to R and vice-versa.
- Improvement: new functionality allows the user to automatically convert R functions into Excel Lambda ones; the button "Make it Lambda" was added to the Ribbon tab.
- Improvement: functions will not recalculate if the Function Wizard is open. This avoids long waitings and freezing in case of very slow calculations.
- Improvement: registering/logging of Excel calls to xlRcode is optional now. It was added a checkbox to the SetUp form to control this. When disabled (default), it should improve speed.
- Improvement: the button "Reload scripts" was added to the Ribbon tab.
- Improvement: when button "Calculate range" is pressed, a message is displayed at Excel's Status Bar while calculating.
- Improvement: added an informative handler for unhandled exceptions with ExcelDna (ExcelIntegration.RegisterUnhandledExceptionHandler).
- Bug fix: loading scripts from the Scripts folder with syntax errors or dependency of resources in uninstalled/unloaded packages was raising untreated error. Informative message was implemented.
- Bug fix: calling xlRcode functions and leaving the argument of the last parameter missing was making that parameter to be ignored instead of treated as missing.
- Bug fix: listing Environment objects when there was a missing object raised untreated error.
- Bug fix: script files in subfolders of the Scripts folder were being ignored during start-up.
Minor improvements and bug handling.
- Improvement: Set-Up form is now displayed above others.
- Improvement: the first time a form is opened, it is now located at the center of the screen of the Excel monitor (multiple monitors issue). Modal forms still remember their position before hiding.
- Bug fix: an error handler was added to the AutoOpen routine to obtain clearer info on start-up errors.
- Bug fix: trying to select an online packages repository was prompting untretaed error if R was not loaded.
- Bug fix: trying to open the Scripts folder from the Console form before it was set by the user prompted an error.
First beta release.
=======