Excel Customisation/Extension summary

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

  1. Additional Worksheet functions.
  2. 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:

  1. VBA – easy to write, easy to deploy, hard to manage, limited protection, stable, slow, pretty much unusable IMO
  2. .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.
  3. 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)
  4. Hybrid – some combination of the above.
  5. 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?)
  6. 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

  1. Those that call back into the Excel Object Model somewhere (perhaps to get the background colour for example)
  2. 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.

cheers

Simon

Advertisements

9 Responses to “Excel Customisation/Extension summary”

  1. Rob Says:

    Patrick O’Beirne recently discovered in the XL2010 beta help an indication that Excel 2010 is the final version that will support Excel 4.0 (XLM) macros.

  2. Mathias Says:

    Can you please elaborate on what you mean by easy/hard to manage? Are you talking about scenarios like upgrading to a new version of the function? Looking forward the next post!

  3. Harlan Grove Says:

    There was something about ditching XLM in the Excel Team blog a few weeks ago. Ditching XLM macro sheets would be one thing – I haven’t used them since the late 1990s – but ditching being able to call XLM functions in defined names or using the .ExecuteExcel4Macro method of Excel’s Applicaiton class in VBA would be a major PITA. I really don’t expect this to happen, but I’d want to see all XLM information functions, e.g., all the GET.* and some others, become worksheet functions.

    One possible design goal needs to be doing as much as possible with formulas but no other programming of any kind. Ditching XLM would run counter to that.

  4. dougaj4 Says:

    Simon – most of what I do is based on UDFs. Why? Because it makes the function of a given routine quick and easy to deploy in different situations. Is performance an issue? Rarely, but if it is I have yet to meet a situation where re-writing the UDF as an array function does not solve the problem. That’s not to say that using lots of UDFs is right for everyone, but I do think you are throwing the baby out with the bathwater.

  5. dougaj4 Says:

    I was talking VBA UDFs there by the way, but I guess it’s a bit off topic, since that’s not what you want to talk about.

    I’ll look forward to whatever you serve up :)

  6. Rob Says:

    Harlan, wasn’t the blog entry you speak of exactly about taking all of the stuff that requires XLM at the moment and exposing it in other ways?

  7. Harlan Grove Says:

    @Rob – yes, and I asked questions in my comments to the Excel team’s blog about whether Excel’s OM would still support the ExecuteExcel4Macro method of the Excel Application class because (as far as I know) it’s the *ONLY* way to fetch values from CLOSED workbooks other than using VBA to enter formulas with external references into cells in open workbooks.

    For example, if there were no workbook named foobar.xls open in the given Excel instance, you can fetch the value of ‘d:\x\[foobar.xls]Sheet1’!G3 using

    x = Application.ExecuteExcel4Macro(“‘d:\x\[foobar.xls]Sheet1′!G3”)

    or

    SomeCellRef.Formula = “=’d:\x\[foobar.xls]Sheet1’!G3”
    x = SomeCellRef.Value2
    SomeCellRef.ClearContents

    Neither work in udfs (though the approach I use is to create a second application instance which the first instance controls by Automation, then use that instance to call ExcecuteExcel4Macro). The former doesn’t trigger recalculation.

    While I don’t use either often, there are times I do use the former.

    Aside from that, is there any GOOD reason the XLM GET.* functions haven’t yet become standard worksheet functions? Granted all of them already have OM equivalents, but those properties are only available through VBA. One way to address this would be to expand the functionality of the CELL and INFO functions, but MSFT hasn’t bothered to enhance either of these in 2 decades (since Lotus enhanced 123 Release 3’s @CELL). If MSFT is finally going to do this, great, but I suspect there’ll be neither enhancements nor new worksheet functions to do this. I’ll bet using these properties in worksheets will require VBA going forward.

  8. Simon Says:

    MS have never been at all keen to increase the number of worksheet functions. Dunno if their underlying technology is too flaky, or if they think their target audience couldn’t cope. Or an other reason?

  9. Rob Says:

    Well, well, well. It’s almost as is Big Brother is watching us: http://blogs.msdn.com/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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


%d bloggers like this: