Hard Lessons

In the last post I described the new hardware setup I recommended for a client, to improve a slow and memory-hungry Excel app.  The hardware is in place and the client is happy, but the transition didn't go smoothly.

I discovered that the hardware upgrade in itself was not enough to cure our ills.  After coming up against some disappointing results, despite the bump to Windows 7 and Excel 2010, I was obligated to re-write a big chunk of the data handling logic; something I was hoping to avoid.

My initial research led me to believe that 64 bit Excel has access to as much ram as you can slot into your machine, but this is not my experience.  The spreadsheet part of Excel might be bigger, faster, stronger and fresher smelling, but the VBA object space is not.

Upgrading my VBA code to work in Excel 2010 was actually a non-event.  I only found one line, (related to checking the amount of ram available, ironically) that choked.  Otherwise the macros and formulas all behaved exactly as designed.  Excel 2010 saves files in the Office 2007 file format, so that was my first clue that this isn't really that much of an Excel upgrade.

Then, after seeing Excel freeze, just like before, when my code tried to pre-load six million+ data points into the application's memory structures, I was beginning to think the whole ordeal and expense may have been for nothing.  Further reading on-line found some comments from a Microsoft engineer who wrote that they achieved significant performance gains on Excel 2010's built-in functions, but basically didn't do a thing to optimize or beef up the VBA compiler... that will be in the next release, supposedly.

Perhaps the recent push towards Cloud-based applications is drawing some of the programming talent away from the Excel group at Microsoft.  I'm a developer, so I can't really hold a grudge.  Excel is still a miracle of engineering in my opinion, and the best software ever to run on any computer anywhere. (Yes, I like it.)

Anyway, I had no choice but to re-work the data logic so the application fetches what it needs at report time, instead of pre-loading on start-up.  I made this change, and the app became usable again - even improved, from the client's perspective, because it "starts up a lot faster."  Reports now require additional processing, but the fast new chip and fast ram more than make up for it.  Also, thanks to the solid state hdd, (and possibly Widows7's superior design,) the memory swapping issue has totally gone away.

We didn't get the 800% performance boost that Moore's Law predicted, but our app's data ceiling is now a clear blue sky, and one busy brokerage is crunching numbers with it on a daily basis.  The client is armed with an unbelievably powerful stock analysis tool, and now I know what to expect (and what expectations to instil) the next time a client wants to level up.

Office 07 - Upgrade or Not?

Many Excel developers assert that Office 2003 (SP 3) is the high-water mark of the Microsoft Office product, mainly due to resistance against the radical interface changes that arrived with Office 2007.  The infamous Ribbon, which replaces a host of "standard" toolbars and menus which users have been interacting with for decades, has been criticized for being a giant leap backward in usability, among other things.  But more than the interface has changed with Excel '07.  The question is: are the changes worth upgrading for?

Besides the user interface, another major difference is the size of the worksheet - up to 1,000,000 rows and 16,300 columns may now be used, up from around 65,500 and 256 respectively in 03. This is a significant improvement if data storage is any measure.

Under the hood, not all that much has changed.  There are a few new worksheet functions which can simplify data summary calculations, and a few extensions to the Office object model, but for the majority of business applications, there is very little difference in functionality between Excel '03 and '07.

From a programmer's perspective, weighing the Pros and Cons leads me to the conclusion that upgrading to '07 is a BAD IDEA.  Here's why:

1. Unless you really need the space, insanely large worksheets are a huge liability.  Try copying a row or column in '03.  The same task in '07 might need to move up to 50 times more cells - using 50 times more system resources to do so.  Accidently paste something into a whole column?  Go get a coffee while your screen repaints, it'll take a while before the hourglass goes away and you can click that Undo button.

2. Macro performance is way down.  One model I created in '03 takes 10.8 seconds to execute the main refresh process.  This same process, in the same file, on the same computer, on the same day, in Excel '07 takes over 23 seconds!  Weren't upgrades supposed to buy you speed improvements?

3. The new SUMIFS and COUNTIFS functions are nice, but the same functionality can be achieved using array formulas in '03, so even if you desperately need these functions, you don't actually need '07.

4. The new file formats used by 07 are not fully backwards compatible with '03, so if your customers or anyone you need to share files with hasn't upgraded, there will be blood.   If you haven't upgraded yet but they have, no problem. '03 Macros run just fine in '07.  Yes, I know that Open XML is a good idea, I'm just sayin...

5. Excel '07 takes more than twice as long to load as '03.  Use it every day and those seconds start adding up to some real lost productivity, or at best, mild aggrivation.  The new version also uses more system resources.  My CPU cooling fan seems to rev at a higher (louder) speed whenever I have '07 loaded.

6. Office '07 costs money.  If the boss is paying, that's one thing, but for those of us who work independantly, the prospect of spending several hundred dollars for no good reason is a significant downer.

There are many other, minor reasons why I'm sticking with Excel '03 for development.  (I do run both on my system, which you can do without trouble in Windows XP.  I'm not sure about vista though.)  Microsoft seems to think that we should all just upgrade because it's there.  But if upgrading is a step backward, what's the sense of that?  I'll be sticking with Excel '03 until the reasons to upgrade are more compelling.