Properly Handling Errors in VBA (Excel)

Question

I've been working with VBA for quite a while now, but I'm still not so sure about Error Handling.

A good article is the one of CPearson.com

However I'm still wondering if the way I used to do ErrorHandling was/is completely wrong: Block 1

On Error Goto ErrCatcher
   If UBound(.sortedDates) > 0 Then

       // Code

   Else
ErrCatcher:
       // Code

   End If

The if clause, because if it is true, will be executed and if it fails the Goto will go into the Else-part, since the Ubound of an Array should never be zero or less, without an Error, this method worked quite well so far.

If I understood it right it should be like this:

Block 2

On Error Goto ErrCatcher
    If Ubound(.sortedDates) > 0 Then

       // Code
    End If

    Goto hereX

ErrCatcher:
       //Code
    Resume / Resume Next / Resume hereX

hereX:

Or even like this: Block 3

On Error Goto ErrCatcher
    If Ubound(.sortedDates) > 0 Then

       // Code
    End If

ErrCatcher:
    If Err.Number <> 0 then
       //Code
    End If

The most common way I see is that one, that the Error "Catcher" is at the end of a sub and the Sub actually ends before with a "Exit Sub", but however isn't it a little confusing if the Sub is quite big if you jump vice versa to read through the code?

Block 4

Source of the following Code: CPearson.com

  On Error Goto ErrHandler:
   N = 1 / 0    ' cause an error
   '
   ' more code
   '
  Exit Sub

  ErrHandler:

   ' error handling code'

   Resume Next

End Sub 

Should it be like in Block 3 ?

Answer 1

You've got one truly marvelous answer from ray023, but your comment that it's probably overkill is apt. For a "lighter" version....

Block 1 is, IMHO, bad practice. As already pointed out by osknows, mixing error-handling with normal-path code is Not Good. For one thing, if a new error is thrown while there's an Error condition in effect you will not get an opportunity to handle it (unless you're calling from a routine that also has an error handler, where the execution will "bubble up").

Block 2 looks like an imitation of a Try/Catch block. It should be okay, but it's not The VBA Way. Block 3 is a variation on Block 2.

Block 4 is a bare-bones version of The VBA Way. I would strongly advise using it, or something like it, because it's what any other VBA programmer inherting the code will expect. Let me present a small expansion, though:

Private Sub DoSomething()
On Error GoTo ErrHandler

'Dim as required

'functional code that might throw errors

ExitSub:
    'any always-execute (cleanup?) code goes here -- analagous to a Finally block.
    'don't forget to do this -- you don't want to fall into error handling when there's no error
    Exit Sub

ErrHandler:
    'can Select Case on Err.Number if there are any you want to handle specially

    'display to user
    MsgBox "Something's wrong: " & vbCrLf & Err.Description

    'or use a central DisplayErr routine, written Public in a Module
    DisplayErr Err.Number, Err.Description

    Resume ExitSub
    Resume
End Sub

Note that second Resume. This is a trick I learned recently: It will never execute in normal processing, since the Resume <label> statement will send the execution elsewhere. It can be a godsend for debugging, though. When you get an error notification, choose Debug (or press Ctl-Break, then choose Debug when you get the "Execution was interrupted" message). The next (highlighted) statement will be either the MsgBox or the following statement. Use "Set Next Statement" (Ctl-F9) to highlight the bare Resume, then press F8. This will show you exactly where the error was thrown.

As to your objection to this format "jumping around", A) it's what VBA programmers expect, as stated previously, & B) your routines should be short enough that it's not far to jump.