Simple R-Excel interface
During the development work of ACOMED statistics for external clients, customers expressed a desire to be able to perform complex statistical analyses themselves. This is possible with the open-source software R. However, users need to be able to program in R. Data input and output, as well as graphical representation, are not as easy to implement in R as in MS Excel.
For this reason, ACOMED statistik developed a simple interface in 2009 as a basic solution, which can be adapted to the specific requirements of individual customers. Since external statisticians expressed interest, it is being made available for download here. We now use a more modern version (see the commented-out script in the VBA code), but its functionality under Excel 2016 and later versions has not been tested.
I thank S. Weber and M. Harteis, who were interns at ACOMED statistik at the time, for creating this.
How it works
Data and parameters for the analysis are entered into Excel. The following steps are implemented in the VBA program:
- Data and parameters are stored in transfer files. Starting R,
- Automatic transfer of the names of the transfer files.
- Waiting for the R calculation to complete, then loading the result files provided by R.
- Results and charts are available to the user in Excel format as usual.
Preparation steps
- Programming complex analysis in R by statisticians
- The directory where the Excel file is stored should contain 3 subdirectories: prog (R script), parameters_and_data (transfer files Excel to R), results (result file(s) R to Excel)
- Providing the program name in an Excel spreadsheet
- Providing the path for RSript.exe in an Excel spreadsheet
- Adjusting filenames for transfer
- If necessary, adjust the transfer procedure in MS Excel.
Implementation steps
- Data entry
- Input of specific analysis parameters
- Starting the analysis
- ...and wait if analysis in R is complex (e.g., bootstrapping)
