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.Posted by ames at February 17, 2004 02:37 PM | TrackBack