Excel 2007 is a BOB system (Bag’O’Bugs)

I am getting seriously aggravated with the flighty behavior of Excel 2007. Yes, I acknowledge that the spreadsheets in question is quite complicated …

• shared VBA modules in an XLA file
• database read/write using ADO
• array-manipulation for ranges of cells (see this post)
• lots of macro (VBA) logic to control look/feel of the tabs
• code to automatically insert formulae into cells

… and so on. However, some of the observed flightiness is specific to the UberSheet, while other weirdness happens with even the simplest files.

Obtuse Errors that Don’t Break in Excel 2003: One of the big, complicated macros does a lot of formatting, so tables of data look nice when printed out. And yes, I freely admit that some chunks of my code are derived from recorded macros, and not 100% hand-crafted goodness. So, when a certain set of circumstances are in place, I get the Run-Time Error 1004: Unable to Set the LineStyle Property of the Border Class error message. A Google search turned up this little item, calling out a known bug in Excel 2002. The macro recorder generates something like this:

[sourcecode language=”plain”]With Range(Cells(iRow, iDataStartCol), Cells(iRow, iDataStopCol))
.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
End With[/sourcecode]

… but if I make this minor change …

[sourcecode language=”plain”]With Range(Cells(iRow, iDataStartCol), Cells(iRow, iDataStopCol))
.Borders.Item(xlEdgeBottom).LineStyle = xlLineStyleNone
End With[/sourcecode]

… the errors cease. Again – the original error is from Excel 2002, it never breaks in Excel 2003, but fails intermittently in Excel 2007. Nice.

Taking Features Away: I can be a stickler for sharp-looking presentations, and I’ve written about one of my favorite PowerPoint best practices – creating complex pictures or tables using a separate application like Excel or Visio, and then pasting into the PPT as a metafile. Unfortunately, Excel 2007 has broken this handy feature – by inexplicably making it unbelievably difficult to copy to the clipboard, and then ruining the metafile that gets pasted. It’s not PowerPoint 2007’s fault – the metafiles I cut and paste using Visio still work perfectly!

The Pause that Refreshes … Something: When loading a file from shared folder, extra stuff happens that I can’t explain

• Lower left on the status bar – message saying Contacting \\my.domain.server for information. Press ESC to Cancel appears, and we wait a bit …
• Then, a message appears lower right on status bar – Opening filename.xls – with a nice little progress bar.
• When I save the file, a dialog pops up – Saving filename.xls – with a nice little progress bar. (Why the marked difference between Load and Save status indicators?)

The Pause that Annoys: This last one is the most frustrating (and disturbing!) for me. Among heavy Excel users, I’ve seen 50/50 split – some hate the ribbon, some are ambivalent – but all long time users love their keyboard shortcuts – especially the oldsters among us that remember Lotus 123 and Quattro Pro (first with tabs – rah!). For ages, Microsoft has supported the “/ menu” – a throwback to 123, and a boon to those speed demons who would eschew the rodent. For years now, I’ve deleted rows using /edr[enter]. I can slow that down a bit for clarity …

1. Edit (menu)
2. Delete (menu option)
3. Entire row (radio button on a dialog)
4. (Enter key makes the row disappear)

… but when I’m in the zone, I can type /edr[enter] /edr[enter] /edr[enter] /edr[enter] and nail a number of rows very quickly. Well, ok, I used to be able to do that; unfortunately, Excel 2007 (Bob) has introduced a weird little delay between steps 2 and 3. This means that the default option on the dialog (Shift cells up) does not change to Entire row. The r keystroke is lost to the bit bucket, I hit enter – and now my spreadsheet is ruined, because I’ve taken a cell out (and “broken” the rows below) instead of taking the whole row out. This can be a very tricky problem, because you may not notice it right away, and it can be difficult to see the cells you’ve deleted (instead of rows) until the sheet is a real mess.

No idea how to get to root cause on these issues, and I’m certainly not blaming Excel for the latest Wall Street problems. Still, folks need to be mindful of very subtle differences in this “new” tool.