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.