Michael Ames

Automation, Extensibility, and Attitude

We started out with VSTO and Excel a few weeks ago, hoping to re-implement some existing standalone software into Excel.  I’ve been grouchy ever since.  In fact, that’s why I haven’t blogged in a few days – I’ve been waiting until I had something positive to say, and it wasn’t coming.  But today I had a revelation that I think will improve both the software we're writing and my attitude.

Epiphany: The Excel object model is designed for automation, not extensibility.

I define automation as the ability to programmatically access features of an application that are usually accessed through a user interface.  Extensibility, on the other hand, is the ability to add to or modify the application's feature set.  The Excel object model seems to be better suited for automation.  This should not be surprising; it was, after all, a macro framework to begin with.  But it managed to catch me off guard anyway.

Adding dynamic data to a chart is one example.  Let’s say you want a feature to calculate and add a new kind of trend line to a series.  The Values property of a Series object will return an array of values from the source series, and set an array of values to a new series for the trend line.  Nice.  But when the property is set, the array is converted to a comma-delimited text string.  This string is limited to 256 characters, apparently to make it editable in the chart’s Source Data dialog.  The result is that you’ll get an error if your array contains more than 25 or so floating point numbers.  Bummer.

The solution to this is simple: Put the array values into cells somewhere in the workbook, and set the Values property to a range reference.  These are the steps a user would take if they were working manually.  It is the automation approach, and is certainly valuable; but it lacks the kind of built-in elegance of the other trend lines. 

In an object model designed for extensibility, the Values property would behave the same for both reading and writing, and the entire Chart class would be inheritable so that all this new functionality could be nicely integrated with existing Chart features.  The .NET Framework will let you create a class inherited from Excel.ChartClass, but the Excel object model doesn’t provide any way to attach an instance of that class to a workbook.

Don’t get me wrong.  Code-behind classes in the .NET Framework give you lots of ways to add powerful new functionality to Excel… not to mention Smart Documents, Smart Tags, VBA add-ins, and the rest.  But my early assessment is that wherever these technologies intersect with the Excel object model, software designs must take into account it's automation- and macro-oriented nature.

So I’m adjusting my expectations.  We’re reworking some designs to be more Excel friendly.  And I’m going to stop banging my head against the wall, trying to get the object model to do things it wasn’t designed to do.  If nothing else, it should dramatically improve my mood.