Designing the SpeedTools function library – my VBA to C Journey part 1

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

For Excel 2007 and later the user interface is created as 2 groups on the formula tab using Ribbon XML, with help from the Ken Puls Ribbon X book, and PED Version 2.

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.

Getting it all together with an Installer

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.

This entry was posted in Calculation, UDF, VBA, XLL. Bookmark the permalink.

5 Responses to Designing the SpeedTools function library – my VBA to C Journey part 1

  1. Mawdo says:

    I love Fast Excel but hate the UI in 2010, so I was wondering when you might be movingh things forward in that respect.
    Are you aware that you can embed the Custom PNG images for the ribbon into the XLAM using the Custom UI Editor for MSO? I don’t beleive that gives you access to the getImage function but as you have to write the xml anyway, it might be easier & certainly reduce the risk of the images going AWOL.

    • fastexcel says:

      Its no great problem to include the PNG files in the install package. I am using a dual 32/64-bit API version of the LoadPictureGDI from PED to put the custom images on the ribbon controls, and will probably use the same technology for the other FastExcel V3 components: but current thinking is to ship SpeedTools first.

  2. jonbudget says:

    Did you consider using Excel-DNA for the XLA instead, that way you could handle your ribbon/tool bar easier. Also, you could embed a primary library into your code (I believe that is for .NET Framework 4.0 and higher???), which would take less space/smaller foot print.

    It does seem like XLA/XLAM solution might be simpler, just more things to handle. Have you thought of using some of the code from the DNA project for just the things you need?

    This will be neat to read about your experience going to C. For me going from VBA to VB.NET was quite the experience itself! Thanks for taking the time to tell us how it’s going!

    • fastexcel says:

      Yes I did look at Excel-DNA and Addin Express as candidates for the .NET route. See . It was quite a close call between .NET and C++, but in the end I decided that C++ had the speed advantage, and if you were going to have to rewrite anyway why not go the whole way? But it was always going to be a hybrid COM/XLL approach and the idea was (and still is) to only rewrite and extend the FastExcel functions in C++ and leave most of the Profiling/Name Manager code (probably about 30K quite complex lines of code) in VBA. Its still hard to beat VBA for manipulation of the Excel object model. Thats not to say that I won’t do some things in .NET if I think its the best solution.

  3. Ross says:

    Brilliant post Charles, off to read part 2 now!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s