Back in December and January I was working on the initial phases of a system to allow the development of custom Excel worksheet functions in managed code using the Excel XLL ‘C’ interface (Excel4v). Phase 1 finished with us having a pretty workable system that the client could go live with. The only problem was that we skipped the key ease of use part of the project due to time and budget constraints. Whilst the C++ side of the system worked and we could marshal values from Excel types to managed types and back again and we could dispatch calls to managed code we had to hardcode the marshalling information in the C++ code and rebuild the XLL to add new functionality.
I’ve just completed phase 2 of this project and it now works just like we originally envisaged. Managed code can be decorated with custom attributes and the XLL uses reflection to work out which classes within an assembly are being exposed to Excel. It then looks for
[WorksheetFunction] attributes and exposes individual functions to Excel whilst working out all of the marshalling requirements itself. Additional attributes allow you to specify that some parameters are optional or have default values. More attributes allow for the definition of custom Excel menus and the provision of the code behind them. By annotating a suitable constructor you can have an
IManageAddins interface passed into your managed class when it’s constructed and this lets you call back into the XLL framework and/or Excel to do things like enable and disable menu items, etc. Several custom types allow us to pass ranges of Excel cells into and out of managed code, and our standard marshalling can deal with converting Excel ranges that contain consistent types to arrays of native managed types. Volatile and command equivalent worksheet functions are supported and, all in all, it all seems to work pretty nicely.
When the XLL is loaded it reads its configuration from a config file and loads the specified assemblies, parses them for attributes, builds and registers the appropriate data structures and wires up the XLL entry points to the appropriate marshaller and from there into managed code.
The next phase is likely to be the inclusion of ‘results object’ which can be used to store and manipulate returned ranges by name. At this point I’ll have something similar to the system I wrote in C++ back in 2001, but this time all of the complex stuff just works and the developers can settle back and get on with writing the business logic.