In this post we describe a technique that allows your Excel addin to be loadable by 32 bit Excel, but lets you run calculations that require more than 2GB of RAM.
Most people in financial industry use 32 bit Excel even though they are running it on 64 bit Windows. The main reason for this is backward compatibility - if you have a set of 32 bit addins that you used with previous version of Excel you’ll stick to 32 bit Excel when upgrading. If you want to upgrade to 64 bit Excel you also need to upgrade all your addins. 32 bit Excel is also recomended by Microsoft.
Why would you want 64 bit Excel? The main reason is when your spreadsheets are massive and require more than 2GB memory when loaded into Excel. It is also possible to have small spreadsheets but have addins that require large ammount of memory to accomplish calculations. Remember that the memory that addins use are in Excels address space which cannot exceed 2GB in 32 bit Excel.
What do you do if your clients use 32 bit Excel but your addin requires a lot of memory for calculations? Read further to learn about our approach.
Calling 64 bit Calculations from 32 bit Excel
We use the following workflow:
We create a 64 bit executable that takes two arguments as inputs. The arguments are input and output files. The input file contains data for calculations. The output file contains result of the calculations. This way our calculations are performed by 64 bit executable that has access to all available memory.
Create a 32 bit xll with a function that saves input data from a spreadsheet into temporary input file, calls 64 bit executable to do calculations and then load output file to display result in the spreadsheet.
Download an example solution that implements this approach. The rest of the post will describe details of the code in the solution.
For an example we consider a useless function that allocates huge vector puts random numbers into it and then computes mean value.
We put the calculation into static library in project
bits. The function that
does the calculations is
double func(double x, void(*progress)(double)).
Notice that to make things more interesting the function accepts callback
pointer to that allows to track function progress. This is usefull when your
function takes long time to compute.
The library also includes one possible implementation of the progress callback that displays progress bar in a console.
Since we are going to save/load data to/from files using json format we included the header file of an excelent C++ json library by Niels Lohmann.
Also we created a project
bitstest to unit test implementation of
We used Catch testing framework for C++
to write our test cases.
64 Bit Executable
Next we created project
bitsexe to build a 64 bit executable that will
run out calculations. The project has just a single file (
main function to read data from input file, call
func and save
data to output file.
Build this project in
Release/x64 configuration. The resulting executable
will be located in
Release\x64 folder and later you will need to move it
to same folder where
.xll is located.
Finally we created project
xbits to build
.xll addin using
The projects exports single function to Excel:
double myfunc(double x).
Remember that when
x is small enough our calculations will fit memory
available in 32 bit Excel. In this case we can call
func() right away
and return the result to Excel.
x is large we will be calling 64 bit executable. First we save the
parameter in a temporary file. Next we find directory in which
xll file is
GetXllDirectory() helper function provided by XLW framework.
We assume that 64 bit executable is located in the same folder. Then we call
system() function that creates a process for our 64 bit executable. The
executable will read data from temporary input file and will write results to
temporary output file. In our case it will also show progress bar while it does
calculations. Finally we read output file, delete temporary files and return the
results to Excel.
To run the example solution you need to do the following steps:
x86configuration (Release or Debug).
Release/x64configuration and copy resulting
xbitsan active projects and run it with
F5(note that you need to make sure that command for debuging property is set to
Excel.exeon your computer: i.e. xbits Properties > Configuration Properties > Debugging > Command should be set to full path of