The Hidden Corners of Excel Hold Many Secrets

Some projects grow and grow, especially if they are effective and well-used applications.  One such goliath is a reporting tool I've been developing for about a year now.  Lately I've been building a new charting module for it.

The program has a sophisticated report engine which allows the user to design his own reports with filters, custom fields and formulas... very powerful.  The new chart builder is an extension to this.  It's coming along, but I've been reminded how tricky Excel chart object programming can be.  Coding in the chart zone reveals some of the dark hallways in Excel's VBA implementation that seemingly never got debugged by the engineers at Microsoft.

My application is 13,000 lines of code and growing, so I've run into a fair share of head-slappers on this project.  Coding VBA can sometimes make one feel like a salmon, swimming up stream; occasional WaTerFalls (WTF?) hinder progress, and perfectly reasonable code yields beguiling results and cryptic error messages such as "User-defined type not defined", or "Error xxx-xxx" (literally, with x's! I should have screen-grabbed that one.)  But despite these obstacles, I just keep trying different ideas and eventually find an alternate way.  That's what makes Excel development so exciting.

Recently my project has started acting strangely, like a creaky building, emitting unpleasant sounds and smells.  One contributing factor could be the sheer size of the workbook.  There are so many custom dialogs, report templates, code modules and worksheets that the file is now over 12MB.  Not obesely large, by most measures, but still a bit to big to send in the mail.  It takes longer to save too, (in conflict with the programmer's mantra: "save often"), and it feels like Excel is struggling (drunk on code?)  The VBA editor has started exhibiting weirdness such as random pauses, strange screen artifacts, and the odd workbook crash. There's even a mysterious worksheet object in the project explorer now, called "Sheet3", that doesn't appear to exist anywhere in the file.  I'm not the only person to experience this particular enigma, so my trust in Excel has wavered and it's a bit scary.  

Once, back in Excel 95, I created a system that, at a certain point, hit a wall and just imploded.  I don't know exactly what happened, but I was coding away one night and some buffer overflowed and I got the blue screen of death, and the workbook thereafter was totally corrupt and un-readable.  I restored a backup file, repeated the day's work I had lost, and then Bang! The same thing happened again.  That's when I informed the client it was time to upgrade the platform to a compiled application.

That was a long time ago.  For efficiency and agility I still prefer developing in Excel.  The ease of deployment is still unmatched, and Excel is much bigger and stronger now... but then again, so am I. My apps are larger and memory-hungryer, and growing in scale; reaching out to other programs and stretching the limits of the Excel platform in new ways.  I dread that all my hard work is built on top of some kind of fault, and one day Excel is going to split apart and swallow the whole thing up.

What can I do to avoid this?  An option I don't relish is to break my program up into smaller pieces: probably not practical for this particular application, but before I go that route I need to investigate other ways of correcting the workbook bloat. A careful code review is in order, to ensure all object variables are being deallocated correctly and the logic is as memory-efficient as possible.  Also, large workbooks tend to accumulate left-over data and toxic clutter such as conditional formatting.  This wastes power and grows the file.  A real-estate audit is the next step.

As another resort, I can migrate the entire program to a brand new, innocent, lean, clean workbook file.  There are tools out there to automate this task, but major applications will require a careful effort. It involves physically copying the contents of each worksheet to a new file.  Then the code from each VBA module and the controls from each user dialog are painstakingly copy-pasted across.  Also, all the named ranges and splits need to be replicated - don't forget the code in the worksheet and workbook objects, and all those object names and properties!

The trick is to NOT copy the Excel objects - just their contents.  This is the only way to leave behind all the invisible, ghostly protoplasm that is haunting your model.  In a workbook like mine, this is a big job that will involve a lot of testing and bug mitigation.  I think, for now, I'll keep that option my back pocket, for if things get hectic.

My client is running this on Windows 7 / Excel 2010, and it appears to be more stable than my 2007 / Windows XP / Mac / Parallels development environment..  Perhaps the coders at Microsoft managed to clear out some of those dusty hallways after all. But as a programmer I also need to remain vigilant against ill-handled errors, wasted screen resources, and memory leaks in my growing code opus.

It's what makes Excel development so exciting.

Setting Traps

I believe that Excel is the ultimate expression of software as canvas - infinitely configurable and adaptable to any problem.  When computers were first invented, those scientists could not have predicted how superbly we manipulate the microchip with software such as  Microsoft's Excel.  That said, it's all too easy to send the whole thing off the rails.  Your code must be able to handle those unexpected user interactions, data anomalies and logic bombs you allow in.  I'm talking about VBA run-time errors.

An error condition arises in the VBA compiler when there is an impossible situation that stops execution of your code.  This often occurs when you attempt to put the wrong type of data in a variable, fail to declare a variable, or exceed a variable's inherent limits.  Trying to call a routine that doesn't exist, dividing by zero, and file system problems are also common causes of run-time errors.

Errors can also happen outside the compiler.  In other words, VBA doesn't actually "choke" on anything.  The code just runs and dutifully produces some results that just happen to be completely wrong.  These sorts of errors are much more difficult to detect, and are often the result of programming errors or bugs in your code.  More on these in a future posting.  Today we'll stick to trappable errors.

For example, say you have a routine that reads through a table of numbers and adds each one to a running total.  If you are only expecting numbers, what happens when the next cell contains a word?  Or an Excel error value?  This may have worked fine when you wrote it, but as the users modified the underlying spreadsheets, things changed.  Now the compiler hits a snag and triggers an error condition.

The worst thing to do is have your code fail silently, with no hint that anything wrong happened.  If it's going to crash, please let us know at least that.  A clue about which function took the hit, and how, goes a long way towards diagnosing the cause.  This information can be sent to the debug window, or show up in an alert box that your spreadsheet's operator will see.

Depending on who's running the macro, you might not want to spew out a bunch of technical detail in a dialog - that can be off-putting.  Just say "Sorry... there's a problem loading the file. Try again or Contact support..." or something friendly like that.

To handle errors superbly, they must be trapped using a combination of the On Error statement, along with an error handling section in your code. The Err object is also helpful because it "knows" what happened.

Every complex piece of code I write has, more or less, the following structure - my template for a function or sub-routine:

Sub
On Error Goto Fail ' the trap!
' - code -
Exit Sub
Fail:
' - error handler -
End Sub

Here, the word Fail refers to the code Label, which can be just about anything you like.  The On Error Goto Fail tells the compiler to jump down to the error handler and run whatever code it finds there whenever an error condition arises.


The Err object, at this stage, will contain much information about the error - most importantly the error code and description.  If you're anticipating certain types of errors, you can check the error code and handle these differently.  For example, if you are trying to read a file from disc, a specific error code might indicate that the disc is not available, or the file is not found, or the path is not found, etc.  Each code could be tested for, and custom error messages generated to help the user pinpoint exactly what went wrong and how to fix it.


Here's a routine to spit out the full set of VBA error codes.  They go up to 65,535, so be prepared for a long wait if you run this!  It appears that most codes are either un-used, or the dreaded and meaningless "Object-Defined" type.

Sub ErrorCodeDump()
 Dim idx As Long

 On Error GoTo Fail
 For idx = 1 To 65535 'watch out!
    ActiveSheet.Cells(idx, 1) = idx
    Err.Raise idx
 Next
 Exit Sub
Fail:
 ActiveSheet.Cells(idx, 2) = Err.Description
 Resume Next
End Sub

For lower-power applications, I usually just print out the Err.Description, along with the name of the function or procedure, to the debug window.  Then I can run the code, observe the debug window, and see what happened.  It all depends on what you're deploying and how it will be supported.

 Note that Error Trapping is controlled at the Excel level.  Check the setting in the options dialog - from the VBA Editor, Tools -> Options, General Tab: Select Break on All Errors to have Excel "barf" every time an error condition is raised (during early development), or, for a more controlled user experience, choose Break on Unhandled Errors.

An application that handles errors elegantly and professionally will go a long way towards winning your customer's undying loyalty, admiration and respect.  Next time: more error trapping techniques such as raising your own errors from the seed, intentionally causing errors, and other erroneous logic.