VBA Tips, Tricks and Best Practices, Part Four of Four

    This is the last post in the short series of posts on VBA tips and tricks. I'm sharing some techniques I use for getting the most of out VBA. In this post, I'll discuss error handling.

Better error handling

    Being exposed to Java (and Access) has me rethinking my approach to error handling. At first, I discarded the idea of using procedure-wide error handling. That's when you write On Error GoTo MyErrorHandler at the very top of a procedure, and then at the bottom use the MyErrorHandler label (or whatever you want to call it) to trap errors. It's similar to the try-catch blocks you see in Java or .NET, in that a block of code is wrapped in a try block, and any errors that occur cause the code to jump to the catch block.

    My initial thought was "Wow, that's lazy programming. Just letting any error occur without at least checking for it first and then bombing out with an error?"

    This line of thinking led me to write code that tries to test for all possible errors and account for them defensively, using If statements and On Error Resume Next throughout my code.

    While I still believe that liberal use if these techniques is appropriate, I now believe it is far better to use the former technique. It has the following major advantages:

1. You don't have to litter your code with endless If statements, checking for basic errors.

The code is more readable when it doesn't have If statements throughout, checking for errors like whether an object is properly initialized, a string has a valid value, and so on.

2. Your error handling code isn't bound up with the program's logic.

If you separate your error handling code, it isn't intertwined with the code that performs actions on your worksheet, making your code more readable. Your code is also more portable, since you can cut and paste it without taking all kinds of error handling with you.

3. You can write your error handling code separately from the program code, and it's all in one place.

This means less time spent reading a procedure to understand what is going on, and makes code writing faster, since the error handling code is in one spot. By physically separating code that has different purposes, you can logically separate it more easily, allowing you to deal with them separately. And since it's all in one place, you don't risk missing some of it when you're looking at it.

4. You can determine what type of error occurred in a structured manner.

When you hardcode error conditions into your code by placing If and On Error statements throughout, you literally have to memorize your code to remember where each error handler is and what it is supposed to do. When everything is together, you just write the code and forget it. Down below there's a code sample showing how you can determine what type of error occurred.

5. Your debugging and testing phases of development take less time.

Since you don't have to create tons of test cases, endlessly testing your code for every possible error condition and hardcoding error handling logic into your program. You spend less time testing your code and more time making money from it!

6. Your code will be more modular, encapsulated and loosely coupled.

In Wrap your method calls I mentioned that I encapsulate most functions and properties, even internal ones like the Enabled property of most form controls. I say that your code will be more modular and encapsulated simply because you will have more procedures, acting together towards your program's goal. There will be more moving parts, but they will be more easily managed. By loosely coupled I'm referring to a module's portability and independence from any one implementation of the calling program (see Wrap your method calls for code sample).

7. Your code will tend to have one exit point. And that is good.

In my sample code below, code always exits through the ProgramExit label. So if we need to exit at any other time, all we need to do is write "GoTo ProgramExit" which is not only immediately understandable, but also preferable to putting "Exit Sub" in the middle of our program. We can also put any necessary cleanup code after the ProgramExit label, and it will always run. Although you'll probably encapsulate the cleanup code into its own function, won't you?

    Here's a sample of the "better" error handling code:

    During normal execution of the code, it goes straight through to ProgramExit and exits the procedure. If an error occurs during execution, control jumps to ErrorHandler, where an error message is displayed. The Resume statement sends the procedure to ProgramExit, where the code ends.

    But you aren't limited to just displaying the error message. You can check the Err.Number and display a customized message, or run a special macro, or both.

Got any "Best of the best" VBA coding tips and tricks of your own to share?

VBA · error handling

Rate This Article:

(No Ratings Yet)

 Loading ...

What now?

Subscribe To The Blog Email This Article Follow Me On Twitter My Facebook Page

Or just keep reading:

• April 5, 2010 -- Convert multi-day appointments into multiple all-day ones
• June 29, 2010 -- Office Links for June
• September 13, 2008 -- Excel games