This is the first of a series of posts about my perilous voyage from VBA to C++.
I am starting with the (current) design of FastExcel V3 SpeedTools Beta 2 (bit of a mouthful so lets just call it SpeedTools).
SpeedTools contains a library of over 80 additional Excel functions as well as additional calculation modes beyond Automatic and Manual.
SpeedTools has to support all Excel versions from Excel 2000 to Excel 2010 64-bit and Excel VNext (I don’t know what MSoft are going to call it!).
Using both VBA and C++
The functions are mostly implemented in C++ as an XLL (using Planatech XLL Plus), but I also need to handle a variety of application level events and a form for the settings and options. I decided it was simpler to handle these using VBA, as well as some things that are not directly available through the C XLL API. For Beta 1 I implemented this as 2 separate addins (one XLA and one XLL) both loaded using the OPTIONS registry keys.
In Beta 2 implementing XLL functions that handled full columns efficiently meant that I had to access the used range from the XLL functions via COM, which turned out to mean that the XLL had to be loaded after a workbook was opened.
If you load the XLL via registry keys it opens before the first workbook, so I decided to load it from the XLA (using Application.RegisterXLL) at the first non-XLA workbook activate.
The VBA XLA also detects whether Excel is 64 or 32 bit and loads the appropriate XLL.
There are some occasions when data needs to be passed (both directions) between the VBA XLA and the C++ XLL. This requirement is mainly down to the need to store memory lookup persistent data in the workbook. I use either C external declarations or Application.Run which handles tricky stuff like string conversions) for this.
The User Interface
Clicking one of the buttons launches a userform to allow you to select a function from a list with a short description:
Choosing a function and clicking OK launches the Function Wizard. This is done by entering the function into the selected cell using a comma as the argument to the function, for example =LISTDISTINCTS.SUM(,) , and then calling the function wizard using Application.Dialogs(xlDialogFunctionWizard).Show.
The second group on the Formulas tab allows you to control the SpeedTools calculation settings and time the calculation of ranges:
The ribbon XML and associated action code is contained in a XLAM file, which is launched by the main VBA XLA if the Excel version is 2007 or later. The action code then calls the appropriate code in the main VBA XLA using Application.Run.
For Excel 2003 and earlier the UI is provided by Toolbar code in the main VBA XLA. The toolbar is only loaded for Excel 2003 and earlier.
There are currently 12 files that all need to be copied to a single folder at installation time, so I use a custom script for Setup Factory. The custom script also writes the registry keys to load the main VBA XLA and handles the uninstall. The 12 files currently are:
- The Custom PNG images for the ribbon (the toolbar custom images are embedded in the XLA).
- The CHM help files and PDF manuals
- The 32 bit and 64 bit versions of the XLL
- The main VBA XLA
- The VBA XLAM for the ribbon UI.
- Readme.txt and License.txt
Its essential to have most of these files in a single folder and to avoid Excel’s disastrous attempts to put addins in weird and wonderful places. Using an installer makes all this simple.
I plan the next post in this series to be about using the XLL Plus function wizards to create XLL functions.