Just a quick post to summarise the main techs for extending and customising Excel. This is a precursor to a few posts coming in the next couple of weeks so I don’t have to drip feed it.
There are two main types of Excel customisation
- Additional Worksheet functions.
- Additional Commands
There is an additional type which is an automation app where your code grabs a running excel or starts a new one, but eventually it will either add commands or functions or just manage the way the user interacts, perhaps with one of them godawful dictator apps. Where the developer removes all the useful features from the clients Excel and hopes to be able to reinstate them when their app closes. I’m not going to talk here about Automation applications, not because they are useless or anything, just they are not as common as the other types. I guess you could argue for other types of customisations that remove functionality, but I won’t be covering them either.
I’m going to look at the first one of those, Worksheet functions over the next week or so. This is some investigation I have been meaning to do since last summer, so its not as delayed as many other things on my todo list.
You can write User Defined Functions (UDFs) in a range of technologies:
- VBA – easy to write, easy to deploy, hard to manage, limited protection, stable, slow, pretty much unusable IMO
- .net – automation add-in, harder to write, harder to deploy (needs registry changes), hard to manage, reasonable protection, no idea on stability, probably not as good as VBA, much slower than VBA, even using VS2008 and Excel 2007. These are completely unusable IMO.
- xlls – harder to write, easy to deploy, not so easy to manage, good protection (fully compiled), pretty stable if written well, native Excel levels of performance (Ie fast)
- Hybrid – some combination of the above.
- XLM – yeah baby! XLM can be shockingly faster than VBA, or slower, its about as easy to write assuming an equivalent skill level, but those skills are much rarer. There is also a hint of XLMs imminent demise in Office 15 (2012?)
- There are probably some minor ones I missed – let us know in a comment.
I’m not going to cover VBA, partly because its covered to death all over the internet, but also because personally I would have to be in a serious serious mess before I would consider using a VBA UDF because their performance is so poor.
Automation add-ins I’m not going to cover because I assume its some sort of sick joke from Microsoft.
I’ve done XLM before so that basically leaves xlls and hybrids, so thats whats coming up this week. I have had a few commercial tools on trial and have requests outstanding for others.
There are 2 basic types of UDF
- Those that call back into the Excel Object Model somewhere (perhaps to get the background colour for example)
- Those that just work on the arguments passed in as values
This becomes quite an important distinction when using anything outside of VBA/XLM.
First up raw xlls tomorrow, or the next day.