Introduction

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.

Example Problem

For an example we consider a useless function that allocates huge vector puts random numbers into it and then computes mean value.

Static Library

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 func. 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.cpp) that only has 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.

XLL

Finally we created project xbits to build .xll addin using XLW framework.

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.

If 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 located (using 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.

Instructions

To run the example solution you need to do the following steps:

• Build xbits in x86 configuration (Release or Debug).

• Build bitsexe in Release/x64 configuration and copy resulting .exe file to Release or Debug folder where .xll is located.

• Make xbits an active projects and run it with Ctrl+F5 or F5 (note that you need to make sure that command for debuging property is set to Excel.exe on your computer: i.e. xbits Properties > Configuration Properties > Debugging

Command should be set to full path of Excel.exe).