Global/Generic Visual Basic Error Handling is Useless but There is a Better Way

Most languages support robust error handling such as catch and throw. The error handling in VBA is an extremely small subset of globally accepted catch and throw protocols, and we have a methodology for working with it.

Summary

Global/generic Visual Basic error handling is misleading: the more it is used, the more errors are masked and the more difficult our job is as we strive to create bug-free applications. VB error handling lacks two critical error management functions: the ability to catch and throw errors up the stack, and stack/state information. These two omissions relegate error handling to the most basic of functions: ignoring errors. This article discusses these deficiencies in more detail, why they are deficiencies, and presents methods and practices to work around these deficiencies.

Background

We have built and deployed some extensive and awesome Excel applications for many companies small and large over many years. In all but one case we have used the suggested techniques we describe below to such success that we guarantee all our work and we have rarely been called back to fix issues once our solutions are in production. In fact, we can't remember the last time we were called to fix an issue in our code once we had completed the project. And these are not applications with a few hundred lines of code, they typically have thousands of lines of code which includes Windows API calls, manipulating external files, and, in one case, pushing and pulling data from a virtual 24x80 green screen.

There are some who are adamantly opposed to our approach to error handling. They were taught early on that code that doesn't handle errors is amateur code, bad code, or code that isn't well-written. They are experts in the field who are convinced that hiding errors behind a glossy interface makes for a happier user. This works if the users have been sold a concept that applications will inevitably crash and keep crashing over time. We propose that we don't have to sell this concept. We propose that it's possible to build bug-free applications and that not using VB error handling except where absolutely necessary is how to do it.

Three Modes of Error Handling

Visual Basic error handling has three different modes: ignore all errors, jump to a specific location in the code when an error occurs, or leave the error alone and let Visual Basic figure out how to handle it (this means a debug dialog presented to the user.)

Ignore All Errors

Clearly the "ignore all errors" mode is extremely dangerous and should be avoided except in very specific and isolated cases. If this mode is set and the program allowed to run unchecked then, while the first few errors remain hidden from the user, the user experience is bound to be disastrous in the end when the application coughs up all manner of meaningless garbage with no explanation as to why it is what it is.

Custom Error Handlers

Jumping to a specific location in the code is the most reasonable approach to error handling but can result in code that is more difficult to maintain. This mode of error handling watches for any error and, when an error occurs, stops execution at the point of the error and starts execution at a predefined location presumably designed to handle errors — a custom error handler. The problem with this approach is that the moment the error is trapped and the execution shifted to the custom error handler, all aspects of the error except for the error code and description are lost. This means that all the custom error handler can do is display the error (which is exactly what the default Visual Basic error handler does) but without the Debug button. The custom error handler has two choices at this point: it can execute a Resume statement which sends execution back to the offending line of code to be retried, or it can abort the current process.

Therefore, the more global the error handling (the higher in the calling chain the custom error handler is put into effect) the more difficult it is to determine the cause of the error and address it. Simply put, if the error handler is not placed in rather close proximity to the offending line of code, it is nearly impossible to determine the cause of the error without resorting to other methods of discovery. So the user is presented with an error dialog with an apology that is friendlier than the native dry VB error dialog with the same result: the function requested is terminated. The only real effective way to use this type of error handling is to use it locally at or near the potential point of failure but the results are still the same for the most part.

Do Nothing

That leaves the third method which is to do nothing and let Visual Basic handle the error by displaying the native debug dialog. While this is the most troublesome from the user's perspective in the short term (at the moment the error occurs), it is the best method to use if the end goal is a stable and effective application. This is because the user, if suitably trained, can provide exact details of the error by clicking the Debug button and taking a screen shot of the offending line of code.

What We Want

As developers, we have a strong desire to present the best, most friendly, most effective interface to the user. We also desire to end our project at a point where the application is bug-free. Where we stumble is working on the assumption that bugs will occur later in the life of an application and that the native VB error presentation is a bad interface. It is a bad interface. So is any error. Wrapping an error in a pretty bow by adding "Sorry!" to the message displayed to the user doesn't change the fact that the application is not going to do as the user wishes. The only argument I have heard supporting generic custom error handlers is that it allows restoration of the environment (EnableEvents, ScreenUpdating, Calculation, etc.) This is valid but the cost of masking errors with custom error handlers is the lost opportunities to get bugs fixed sooner versus later. Unless the error handler is implemented for a specific situation, or we are recording our own stack/state information with every step of code, there is little chance that the custom error handler can pull together enough information to aid our efforts to correct the problem.

Working the Problem

Assume for the sake of discussion that the users are willing to help us create a bug-free application. Let's also assume that the users can be trained to be good testers. When I build applications, I tell my client that the users' first job is to help me test the application, not to use the application. I make it clear that they must help me help them. And, until I tell them so, the product is not to be used in a production mode. This is a crucial step in any development effort since it is impossible for the engineer developing the application to provide sufficient unbiased testing to take the application from beta to production. With this phase clearly delineated, the client has no choice but to test and support the debugging process. Our experience is that, when presented this way, users actually want to help with the testing process and enjoy that role. One motivator is letting them influence how the application evolves. This experience, in turn, propagates nicely into production mode and the user continues to effectively support the process and understands that Excel may need to be reset now and then after an error occurs. Those users not involved in the testing/beta phase are trained by their peers who were.

Part of this training is to provide the user with tools to work in a tenuous environment. Providing these tools helps them feel empowered and in control in the face of Excel throwing up all over the carpet as they test while continuing to use Excel to do tasks required to get their paycheck. One such tool is the classic key-press invoked macro. Key-press invoked macros work wonders in doing things like resetting the environment and switching between development and production mode. And they are not subjected to environmental states such as when events are disabled.

Two Scenarios

One scenario is where we have home-grown error handling that we hope generates enough information to quickly lead us to the problem, and that resets the environment to a workable state. The other scenario requires some limited straightforward user training, and no additional code beyond possibly a few high-level macros to let the user easily switch between testing and production modes. This scenario pinpoints problems down to the offensive line but requires the user to do something to restore the environment. In either case the desired end result is the same: a bug-free application which never displays an error. The question is: which is easier to do, and which results in success sooner.

Building Robust Solutions That Don't Break

Perhaps the latter scenario can be made more palatable with some additional development ideas and tools. Here are a few we employ regularly:

• Develop and use an extensive set of libraries that contain generic routines. Over time, these libraries represent years of testing and use in production. They also speed development.
• Use structured coding techniques to keep routine sizes small and focused. This keeps routines focused on specific tasks which aids unit and regression testing.
• Unit test new routines. Unit testing helps to narrow down and focus the routine to do one thing and do it flawlessly.
• Use preflighting which is logic included at the beginning of a routine to ensure that the parameters passed are within certain boundaries. This can mean, if things are not right, returning an error, self-correcting the parameters, or just stopping with a comment as to why the code stopped.
• Use regression testing to ensure that something that worked doesn't break when making changes to the code. This is useful in complex environments where routines depend on other routines.
• In places where errors can occur because of environmental conditions (a missing or locked file, for example), handle the error as if it is a normal condition with specific messages instructing the user how to fix that specific issue.
• Give the user an interface to address external issues such as column names changing in an input file or a file location.

An Alternative

There is an alternative to doing nothing that supports catch/throw and stack/state logging/retrieval, but it isn't for the casual developer and it does consume resources. It involves writing our own stack/state management logic and error handler, and using it extensively throughout our code. Stack/state tracing is crucial to understanding what went wrong and where it went wrong. To implement this requires creating our own stack which means including pre- and post-routine code in every routine that saves routine names and variables in a home-grown stack that is available for perusing and logging when an error occurs. This requires more code in a project which must be written and requires more CPU cycles when running, especially in lower-level routines which are called repeatedly in computation-intensive functionality. It's an effective solution if implemented well and completely, but it requires a lot of additional work and diligence to ensure consistent use.

Conclusion

Error-free code is hard to write but it can be done. Avoiding the native VBA error handler is only part of the solution to creating error-free applications faster. We must engage the users in the testing process as testers, not as users in a production mode. Managing the client's expectations to support this process has been proven repeatedly to be more effective at arriving at error-free applications sooner than by masking the errors behind pretty dialogs with nurturing comments apologizing for the application not being able to do what the user wants it to do.