As I wrote in My First Look at Excel 2010 and in Sparklines and Data Bars in Excel 2010, and as roughly 97% of the blogs in the whole internet have reported, Microsoft has just released the Technical Preview edition of Office 2010.

I spent my first couple hours in Excel 2010 just driving around, seeing what the terrain looked like. Then I experimented with a new feature, Sparklines, and with a feature that was introduced in Excel 2007 but made useful in 2010, Data Bars.

PTS Utilities in Excel 2010 – Good news!

Finally today I got up the courage to test my commercial utilities in Excel 2010. Aside from very minor things, like building the ribbon interface not just in 2007 but in any version 2007 and later, the utilities have worked just fine.

This screen shot shows the commands for my utilities happily displayed on the PTS Charts tab of the ribbon. In the worksheet below is a waterfall chart created using the unmodified Waterfall Chart utility.

If any PTS Utility customers want to try out their utility in Excel 2010, send me an email and I will reply with a link to an updated version.

PTS Utility in 2007 – Bad News

I was putting the finishing touches on a new utility, the PTS Chart Zoomer Utility. It allows a user to select a region of a chart using the mouse, and the chart axes are rescaled to show a closer view of this region.

Select a region of a chart with the mouse…

… the region expands to fill the chart. Select another region…

… and this region now fills the chart.

I built the ribbon bits for this utility, and I was going through my final testing in Excel 2007 SP2, and nothing worked. No selection rectangle appeared in the chart, no resizing of the chart region occurred, nothing happened. I checked my code: it looked fine. I reran my code in Excel 2003: it worked fine.

I was starting to think, not another Excel 2007 upgrade hassle. I’ve had three unpleasant 2003 to 2007 upgrades of client projects in the past couple of months. The unpleasantness is due to a load of unanticipated incompatibilities, and even my best workaround attempts weren’t always successful. In one or two cases, the code might have changed, so that formerly optional arguments were required (though the documentation didn’t point this out). Mostly, though, the problems were with file formats, template handling differences, and charts. Charts, ouch. Dynamic charts would lose track of the ranges they were linked to. Charts on copied sheets would end up linked to the wrong data sheets. Labels or entire charts would move or disappear.

The problem in this case boiled down to a couple chart events not working. To draw a selection rectangle as shown in the screen shots above, you need to click the mouse (the MouseDown event), drag the mouse (the MouseMove event), and release the mouse (the MouseUp event). MouseMove is working just fine in 2007, but neither MouseDown nor MouseMove are triggered by user actions.

I think I could hack my way around this deficiency if all I were doing were drawing the rectangle and rescaling the axes. It wouldn’t be pretty, but it might work. However, the Chart Zoomer utility also has features that remember and restore previous zoom settings (Ctrl+Left or Right Click), and that resize to fit all points or to restore Excel’s default axes (Alt+Left or Right Click). I can’t figure out how to capture these mouse clicks, so I wouldd have to add a bunch of controls to the ribbon tab, or float a dialog nearby, or generate a bunch of shortcut keys.

I haven’t decided whether or how to proceed with this utility: should I scrap it, release what I have as a Classic Excel only utility, or hack my brains out and release a utility that works or at least mostly works in all versions of Excel?

Related Posts:

• Programming Excel 2007 and Excel 2010 AutoShapes with VBA (Guest Post)
• Excel Utilities in the Works
• Chart Event to Highlight a Series
• Introducing PTS Dot Plot Utility
• PTS Blog Inaugural Post
• An Undistorted Election Results Map
• Stock Charts in Excel 2007
• Chart Event Class Module to Highlight a Series
• Forms Controls and ActiveX Controls in Excel
• Cascade Chart Utility Joins the Team