Can you help me understand more about good practise with VBA Error handling please?

Question

I've spent the last two days trying to better understand VBA error handling and still have issues with what is actually going on. The text below describes some of the realisations I've made and embedded in amongst the description are some questions. I'm really after people to help me refine my understanding and give me some pointers. I've got a hang of the usual basics and I'm trying to focus more on the subtle features.

I look forwards to any answers you have!

It is important to realise there are two distinct things that happen when an error occurs in VBA.

1. The error object is instanciated and has it's properties set (ie err.number, err.desciption, err.source etc)

2. The next line to be executed changes.
   Which line is executed next is determined by the last "On Error Goto" statement that was executed - if any.

These are separate but highly related topics and you will write, what is in effect, distinct but interwoven code to manage them both.

When ANY error occurs or you use Err.Raise the Err object is ALWAYS set up. Even if "On Error Resume next" or any other On error statement has been used.

So code like this could ALWAYS be used:

Dim i as integer 
On error resume next 
i = 100/0  ' raises error
if err.number <> 0 then 
   ' respond to the error
end if

It is really important to realise that when the error object has a non zero value for err.number an exception has been raised AND that if you then try and execute any "On Error Goto " statement doing so will itself raise an error and execution will be passed to any code that called the current procedure. (Or where the procedure not called by any code the usual VBA error dialogue is given). So in this example scenario below "On Error Goto ALabel1" would NOT change the next line to be the line with Label1: on it.

eg

Sub ErrorTest()

    Dim dblValue        As Double

    On Error GoTo ErrHandler1
    dblValue = 1 / 0

ErrHandler1:
    debug.print "Exception Caught"
    debug.print Err.Number

    On Error GoTo ALabel1    
    dblValue = 1 / 0   '' THIS LINE ACTUALLY WILL RAISE AN unhandled ERROR!

Exit sub
ALabel1:
    debug.print "Again caught it."

End Sub

Once the err.number property is set to non zero, you can reset it to zero by using

On Error Goto -1 

Note that Err.Clear also 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 add an extra line to re-instate the error handler that was in place or another one anyway.

Err.Clear
On Error Goto MyErrorHandlerLabel

"Alternatively, you can set the error number to zero (Err.Number = 0), but is not as effective as the Clear method since it does not clear the description property." (From MSDN pages)

I read the next paragraph on MSDN I think and don't understand how it can be the case as if the error object is instantiated (ie err.number <> 0) , it's values are passed to any calling procedure when End Sub is executed.

Q1: Help!

"It is worth noting that Err.Clear is implicitly carried out by VBA whenever it executes any type of Resume statement, Exit Sub, Exit Function, Exit Property, or any On Error statement." (From MSDN pages)

You can also set the error object it to whatever number you like using

Err.Raise Number:=, Source:=, Description:=

Err.Raise is very important as it allows you to propagate an error to the calling program and unlimately all the way "up" to the top level program that is dealing with the user. You can also raise your own error numbers known as "user defined errors". This provide a means of telling the calling program that it could not continue for some reason (ie an unexpected error occurred or a business rule was broken).

In this way the calling procedure get to decide what to do about the error, not the procedure where the error happened (which doesn't understand anything about what the user is in the middle of doing, where as the very top most calling program does!)

Q2: Am I right about this?
Q3: Would you raise user defined exceptions for this purpose?

You can control which line of code is executed next using statements like

On Error Goto ALabelName

On Error Goto ANonZeroLineNumber

and

On Error Goto 0

On Error Goto 0 is a special case as it in effect says "within the current scope (typically a sub or function), in the event that an error happens pass the error object back to the code that called the current sub or function, don't execute any more code in the current sub or function.

Using simply error handling in VBA is pretty straight forwards, but often find myself tying myself in knots when things get trickier.

Not having the commonly used TRY CATCH statement that SQL Server and VBA have is a shame, so I've successfully managed to simulate this now here:

https://codereview.stackexchange.com/questions/94415/try-catch-statement-in-vba-using-the-standard-vba-error-handling-statements

I hope the above post will give you some more insight into the experiments I've been doing if you're interested, and help you understand why I'm struggling.

In short although there are some useful sites out there Like:

http://www.cpearson.com/excel/errorhandling.htm

but, I feel I could do with some more guidance.

Harvey

Answer 1

There are three error modes.

1. On Error Goto 0 the default. No error handling. VBA crashes on all errors giving basic info to be fixed.

2. On Error Resume Next. You are handling all errors. After any line that may generate an error (but not every line - msgbox will always work) you do If err.number <> 0 then FixErrorOrCrash. Anything involving files, registry keys, network or internet can't be relied on to exist or be working at that moment. Likewise non system objects like Office.

3. On Error Goto Label. You are also handling all errors except you don't know where the error is. This is used to undo a function. If anything fails this gives you a chance to roll back everything the function may have done.

Also when programming it is efficient to do and catch errors rather than test then do. Accessing a file the second way requires two accesses on success compared to the first ways one disk access.

Answer 2

This answer is about the error handler which you seem to be missing in your question.

You said "two distinct things that happen when an error occurs in VBA."

1. "The error object is instanciated and has it's properties set (ie err.number, err.desciption, err.source etc)"

2. "The next line to be executed changes. Which line is executed next is determined by the last "On Error Goto" statement that was executed - if any."

The first item is correct. The second is way off base; you're entirely missing the concept of an error handler. An error handler is "enabled" by an On Error Goto <label> statement. Then when an error ocurrs the error handler becomes "active".

The active error handler is best thought of as a condition. Your code enters this special condition when an error is triggered while an error handler is active. Also the error object has it's properties set.

Typically the error handling code is at the bottom of the procedure. See Explanation of regular error handling This avoids confusion between the error handling block and the error handling condition. I suggest you go back to this because it is easily explained and reproducible. The attempt at a Try/Catch block is confusing to almost everyone.

You cannot exit the error handling condition by jumping to another place in your code. Doing so without a Resume ... statement must be avoided.

The only ways to exit the error handling condition are:

1. Resume
2. Resume Next
3. Resume <label>
4. exit the procedure
5. On error goto -1 ' This one is to be avoided with traditional error handling where the error handling code is at the bottom.

Chip Pearson: Using On Error Goto -1 can be a very dangerous thing to do, and I would avoid it at all costs.

The idea of an error handling block is not quite synonymous with the error handling condition. The code block is visually an area in your code. VBA cares nothing about this. VBA only knows about the error handling condition. Using resume simultaneously takes you out of your error handling code block and cancels the error handling condition. And it also resets the error object as well.

Answer 3

I've stopped declaring routines as "sub" procedures and instead I always use "functions" (except for subs called as a "method" ie top level procedures).

These functions return true (or false if there is an error) as doing so gets around the limitations of VBA. Other return types can be used, but the key is for there to be one or more return values that indicates an error has occurred.

I use the return value to propagate the error up as trying to re-raise an error is problematic in VBA. This method works and is generally pretty simple to use.

I'm still interested in peoples opinion. (The best answer I've had about error handling in VBA was to use C# and avoid using VBA as much as possible!)

Public Function AFunction1(ID As Long) As Boolean

    Dim Qdf as querydef 

    On Error GoTo ErrHandler

    set Qdf = .etc....

    ' Note Afunction2 is declared with the same structure as AFunction1
    If not AFunction2 (ID2 as Long)_ 
    Then
        GoTo ErrHandlerMsgAlreadyGiven
    End If

    ' True is only returned if the code gets to here
    AFunction1 = True
    GoTo CleanUp

ErrHandler:
          MsgBox "AFunction1 Blargh Blargh  Error: " & Err.Number & " " & Err.Description
          AFunction1 = False ' this line is not really needed but re-inforces what is happening
          Resume CleanUp
          Resume 'debugging only

ErrHandlerMsgAlreadyGiven:
          AFunction1 = False ' this line is not really needed but re-inforces what is happening
          GoTo CleanUp

CleanUp:

    Set Qdf = Nothing
    Exit Function ' this line is not really needed but re-inforces what is happening

End Function

Answer 4

You ask about good practise, here is my honest answer:

The really good practise is to use as less VBA as possible.

If you are planning to create a somewhat bigger project, then I recommend to create a module in C#, and call the office functions in C#.

And then use your code as an add-in or a function library. In other words: Use VBA only to call the main entry functions of your module. But implement the main functionality in C#.

We are living in 2015. With C# and Visual Studio, you now have overwhelming powerful tools at hand. But VBA was introduced in the 90's. The language and the IDE are limited. For many things, like proper error handling, you need awful workarounds.

BTW, the VBA language was even already outdated at the time of its birth. In the 90s, we had much better object-oriented concepts already. It is a pity that Microsoft did never create a better language for their office products. They sacrificed a better language for the sake of being "backward compatible" and making the transition for their users most easy. IMHO that was a bad decision. Therefore, today we live with millions of projects written worldwide in a limited and crappy language.