Errors are a fact of life in programming. Errors are unavoidable. But errors are not the same as bugs.
Errors signal a problem with the program's state. Bugs indicate a problem with the program itself.
Not all errors are bugs
For example, consider a program that saves files to a network folder. If the network folder is unavailable, the program will raise an error. As the programmer, you should expect this. Your program has no control over whether the network drive is available at any given moment (it's a leaky abstraction after all.)
But so far, all we have is an error. And there's nothing wrong with errors in your program. The problem is when you fail to anticipate them and handle them with grace. That's when errors turn into bugs. The way to avoid bugs is with proper error handling.
Not all bugs come from errors
Not every error results in a bug. Similarly, not all bugs arise from errors. In fact, the most insidious bugs of all having nothing to do with the kinds of errors that a programming language raises. No, the worst bugs of all are logic bugs.
A logic bug is one where the program appears to work fine, but the output it generates is incorrect.
Think of an online shopping cart. The retailer charges shipping based on the order total: $5 for orders up $50, $10 for orders up to $200, etc. The order qualifies for a 20% discount. The retailer wants to calculate shipping before applying the discount. Instead, the program applies the discount and then calculates shipping. This is a classic logic bug. The best way to avoid logic bugs is with automated testing.
How you handle errors says a lot about you as a programmer. Most people evolve with experience in how they handle errors. From the most naïve to the most advanced, here is what that evolution looks like.
See no evil
The most naïve approach is to simply pretend errors don't exist.
Sub MyRoutine() 'Some code End Sub
You see this most commonly in Excel or Word VBA. You see it less in Access because of what happens with unhandled errors in a runtime environment:
When Access is running in a runtime environment it will crash hard--exiting the application immediately--as soon as any unhandled error is raised.
Hear no evil
You can safely ignore many errors in VBA. But by no means should you ignore every error in VBA. The
On Error Resume Next statement allows you to ignore errors. There are times when this is useful. There are times when it is necessary.
But if your programs look anything like this, you have gone down a very dark path indeed:
Sub MyRoutine() On Error Resume Next 'Some code End Sub Function MyFunction() As String On Error Resume Next 'Some other code End Function
Speak the evil?
One of the most common approaches to error handling in VBA is to just show a message box to the user. Here's how this looks:
Sub MyRoutine() On Error Goto MyErrorHandler 'Some code Exit Sub MyErrorHandler: MsgBox Err.Description End Sub
The code above is pretty straightforward. The
On Error Goto MyErrorHandler line tells VBA to go to the line labeled
MyErrorHandler if the code that follows raises an error. In the case of an error, the routine shows the user a message box with a description of the error.
While this is infinitely better than the first two approaches, it still has many problems. (1) It relies on users to pay attention to and understand the error messages. Users will do neither of these things. (2) Errors don't bubble up. A procedure that calls
MyRoutine() will not be alerted if it throws an error. This may or may not be what you want. It's almost certainly not what you want every single time. (3) It requires lots of boilerplate code. There are plugins like MZTools that automate creating that code. But you're still left with four lines of code per routine that have nothing to do with the software's actual purpose. This lower's the signal-to-noise ratio and makes the code harder to read.
Most people move past the first two approaches very quickly. But a surprising number of people get stuck in this approach. There are better ways.
Select...Case error handling
This one is similar to the previous approach, but with a couple of important distinctions. Here is what it looks like:
Sub MyRoutine() On Error Goto MyErrorHandler 'Some code ExitRoutine: DoCmd.Hourglass False DoCmd.SetWarnings True Exit Sub MyErrorHandler: Select Case Err.Number Case 70 'Permission denied MsgBox "Can't access file. Make sure you have permission to make changes." Case 3265 'Item not found in this collection Resume Next Case Else MsgBox Err.Description End Select GoTo ExitRoutine End Sub
There are some key differences from before:
- I added an
ExitRoutine:label with some cleanup code. If you are familiar with the Try...Catch...Finally error handling approach of other languages, this section is like the Finally block. It includes code that should always be called before the routine exits, even if the routine raises an error.
- I added a
Select...Casestatement to handle specific types of errors that I anticipate the body of my routine might raise. The actual
Caseclauses will vary from one routine to another. The point is that I'm putting some thought into what errors I might encounter and how my code should handle each.
On the downside, there is even more boilerplate code than before. That's not ideal, but it's not the end of the world, either.
Dedicated error handling function
One way to cut down on some of the boilerplate code is to use a dedicated error handling function. Here is what that may look like:
Sub MyRoutine() On Error Goto MyErrorHandler 'Some code ExitRoutine: Exit Sub MyErrorHandler: Select Case Err.Number Case Else HandleError Err.Number, Err.Description, Errors End Select GoTo ExitRoutine End Sub Public Sub HandleError(ErrNum As Long, ErrDesc As String, _ Optional Errs As DAO.Errors = Nothing) 'Restore some default settings in case ' they were changed in the calling routine Application.Echo True DoCmd.SetWarnings True DoCmd.Hourglass False SysCmd acSysCmdClearStatus 'Ignore OpenForm/Report action was canceled error messages If ErrNum = 2501 Then Exit Sub 'Get DAO error message(s), if applicable Dim Msg As String, ErrItem As DAO.Error If Not Errs Is Nothing Then If Errs.Count > 1 Then For Each ErrItem In Errs Msg = Msg & ErrItem.Description & vbNewline Next ErrItem End If End If If Len(Msg) = 0 Then Msg = ErrDesc MsgBox Msg, vbCritical, "Error [" & ErrNum & "]" End Sub
Having a dedicated function lets you build some advanced functionality into your default error handling process. In my example above, I do things like making sure the screen drawing is turned on (
Application.Echo True ), the Access warnings are turned on (
DoCmd.SetWarnings True), the hourglass icon is turned off (
DoCmd.Hourglass False), and any custom status messages are cleared (
I ignore an error that gets raised any time I cancel the opening of a report because there is no data (
ErrNum 2501). The DAO Errors object contains a collection of error messages when certain database operations fail. To access the error messages, one must iterate through the collection. This is more code that we can relegate to our error handler.
This is just scratching the surface of what you can do with a dedicated error handling function. Astute readers may already be thinking, "Hey, this would be a great place to log errors!" And you would be right. A whole new world of advanced error handling opens up once you have a dedicated function in place.
If this is as far as you go, congratulations. You are likely in the top 10% of Access developers when it comes to error handling. But wait...there's more!
Automatic error handling?!?!
I spent years using the previous error handling method. I tweaked my error handler to log every error to a dedicated error log table in a centralized database server. I wrote a fallback function to log the error to a local text file if the database was unreachable.
I learned about the
Erl function and started saving line numbers with my error logging. I didn't like seeing the line numbers, though, so I created a script to add the line numbers as part of my automated release process (Joel Test #2). It was all very advanced, very impressive, and very cumbersome.
My boilerplate code had taken over. I'm not exaggerating when I say that nearly half of my lines of code were error-handling boilerplate. In other words, they were lines of code that added no value to the program. They hindered readability. I knew there had to be a better way.
And then one day--deep down a stackoverflow/ExpertsExchange rabbit hole--I stumbled upon something amazing. Like Alice through the looking glass, what I saw challenged my very conception of reality: vbWatchdog.
(I want to be clear that I have zero affiliation with this product. You know, other than the fact that it completely changed my life as an Access and VBA developer.)
This is a paid product. At the time of writing, it sells for $160 for a single-developer license. It is worth all that and more. If you have sold at least $1,000 of Access software development services and have plans to continue selling more in the future, you should buy this. Period. Full stop.
There will be some upfront investment on your part as a developer to set up your global error handling function. Of course, if you have a dedicated error handling function, then you are most of the way there already.
I will go into detail in future posts about how to incorporate vbWatchdog into your projects. But for now, I want to leave you with an example of what your typical routine looks like after implementing vbWatchdog:
Sub MyRoutine() 'Some code End Sub
That's right. We have come full circle. The most advanced VBA error handling looks exactly like the most naïve. And that is the true beauty of vbWatchdog.