Try catch statement in VBA using the standard VBA error handling statements

The code below is pretty self explanatory: just copy and paste it all into a module and run it, it provides a few use cases and many explanatory comments in the text. (It works but I'm interested to know what other people make of it and for any suggestions you might like to make.)

The most important facts to realise are:

-
When you use on error goto Label1 the procedure enters into a state of "I'm handling an error" as an exception has been raised. When it is in this state, if another "On Error Goto" label2 statement is executed it will NOT goto label2, but raises and error which is passed to the code that called the procedure.

-
You can stop a procedure being in the "I'm handling an error" state by clearing the exception (setting err to nothing so the err.number property becomes 0) by using

Err.clear
or

On Error Goto -1    ' Which I think is less clear!

(NOTE that On Error Goto 0 is different from the above)

Also important to note is that Err.Clear resets it to zero but it is actually equivalent to:

On Error Goto -1 
On Error Goto 0

ie Err.Clear removes an "On Error Goto" that is currently in place. So therefore it is mostly best to use:

On Error Goto -1

as using Err.clear You would often need to write

Err.Clear
On Error Goto MyErrorHandlerLabel

I use the above techniques with various labels to simulate the sometimes useful functionality that Visual basic TRY CATCH blocks give, which I think have their place in writing readable code.

Admittedly this technique creates a few more lines of code than a nice VB try catch statement, but it's not too messy and pretty easy to get your head around.

PS. Also of interest might be the procedure ManageErrSource which makes the Err.Source property store the procedure where the error occurred.

```
Option Compare Database
Option Explicit

Dim RememberErrNumber As Long
Dim RememberErrDescription As String
Dim RememberErrSource As String
Dim RememberErrLine As Integer

Priva

The problem is that runtime errors in VBA are not exceptions, and error-handling in VBA has very little in common with exception handling.

RememberErrLine = Erl()

The Erl function is a hidden member of the VBA.Information module for a reason - it returns 0 unless the error occurred on a numbered line. And if you're using line numbers in VBA, you have been living in a cave for 25 years and are probably using GoSub statements instead of writing procedures. Line numbers are supported for legacy/backward-compatibility reasons, because code written in the 1980's required them.

I like how you said it yourself:

' THEREFORE KEEP THE CODE HERE VERY SIMPLE!

..but why isn't that applied to the rest of the code? No offense, but this is spaghetti logic, written in procedures that clearly and shamelessly violate the Single Responsibility Principle. No SRP-compliant code would ever need two of such "try-catch" blocks.

This smells:

Case 0:  ' No Error, do Nothing

It means one of two things: either you have error-handling code that runs in non-error contexts, or you have dead code that should be deleted.

This smells reeks:

GoTo CatchBlock2_End:
CatchBlock2_Start:

Firstly, a colon (:) that isn't specifying a line label, is an instructions separator. Turns out a new line is also an "instructions separator", so the colon at the end of GoTo CatchBlock2_End is utterly useless and confusing, especially given the indentation level of the GoTo statement.

Speaking of GoTo...

Neil Stephenson thinks it's cute to name his labels 'dengo'

I don't like how I need to jump between labels to follow the code. IMO it is messy and needlessly spaghettified.

Fine, smartypants. So, how does one cleanly handle errors in VBA then?

1. Write clean code in the first place.

Adhere to best practices, and write small procedures that do one thing, and do it well.

1. Write Object-Oriented code.

Abstraction and encapsulation are two of the 4 pillars of OOP, and they're fully supported in VBA. Polymorphism is also somewhat of an option; only proper inheritance is ruled out, but that doesn't prevent one from abstracting concepts in class modules and instantiating specialized objects.

Procedural code written in standard modules (.bas) should be tiny little public methods (macro "hooks") that create the objects required to run the functionality.

So, how does that even remotely relate to proper error-handling?

1. Embrace idiomatic error handling, don't fight it.

Given code that adheres to the above points, there's no reason to not implement error-handling the idiomatic VBA-way.

Public Sub DoSomething()
    On Error GoTo CleanFail

    'method body

CleanExit:
    'cleanup code goes here. runs regardless of error state.
    Exit Sub

CleanFail:
    'handle runtime error(s) here.
    'Raise Err.Number '"rethrow" / "bubble up"
    Resume CleanExit
    Resume 'for debugging - break above and jump to the error-raising statement
End Sub

This pattern is analoguous to a "try-catch-finally" in the following way:

• The body is the "try" part, that does what the method name says and nothing more

• CleanFail is the "catch" part, that only runs if an error is raised

• CleanExit is the "finally" part, that runs regardless of whether or not an error was raised... unless you're rethrowing. But then if you need to bubble up an error for the calling code to handle, you shouldn't have much cleanup code to execute, and you should have a very very very good reason to do so.

If your error-handling subroutine can raise an error, then you're not adhering to SRP. For example, writing to a log file is a concern of its own, that should be abstracted into some Logger object that lives to deal with logging concerns, and exposes methods that handle their own errors. Error-handling subroutine code should be trivial.

StackExchange Code Review Q#94415, answer score: 28