VBA Excel Error Handling - especially in functions - Professional Excel Development Style

That is an amazing book by Rob.

My two cents of Error Handling (Either for a procedure or a Function) is based on KISS (Keep it simple Silly)

Understand what do you want from your error handler?

This is usually what I want/expect from my error handler...

1. Line on which the error happened
2. Error Number
3. Error Message
4. Reset Events if applicable

Lets break the above. As you are by now already aware how your error handler looks like, Consider this example.

Sub Sample()    Dim i As Integer, j As Integer    On Error GoTo Whoa    Application.ScreenUpdating = False    i = 1111111111    For j = 1 To i        Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value    Next iLetsContinue:    Exit SubWhoa:    MsgBox Err.Description    Resume LetsContinueEnd Sub

This is a very basic error handler but it's of very less help to me. So let's now tweak it to make it more useful. If you run the above code you get an error message like shown in the screenshot below and if you notice, it's not of much help.

Let's now tackle all the points that I mentioned in the Logic above

1. Line on which the error happened

There is a property called ERL which very few people are aware of. You can actually use it to get the line number of the code where the error happened. For that you have to ensure you number your code. See this example.

Sub Sample()    Dim i As Integer, j As Integer10      On Error GoTo Whoa20      Application.ScreenUpdating = False30      i = 111111111140      For j = 1 To i50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value60      Next jLetsContinue:70      Exit SubWhoa:80      MsgBox Erl90      Resume LetsContinueEnd Sub

When you run the above code, you will get this

So now I know that the error happened on Line 30 which is i = 1111111111

Moving on to next

2. Error Number
3. Error Message

The error number and the error message can be retrieved from Err.Number and Err.Description respectively. So now let's combine Erl, Err.Number and Err.Description

Check this example

Sub Sample()    Dim i As Integer, j As Integer10      On Error GoTo Whoa20      Application.ScreenUpdating = False30      i = 111111111140      For j = 1 To i50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value60      Next jLetsContinue:70      Exit SubWhoa:80      MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _           "Error Message : " & Err.Description & vbNewLine & _           "Error Number : " & Err.Number90      Resume LetsContinueEnd Sub

When you run this code, you will get something like this.

You can choose to further customize the Error Message to make it more user friendly. For example

'~~> Message you want to deliver to the user in case the error happensConst sMsg As String = "Please take a screenshot of this message and contact the developer for a resolution"'~~> Title of your message boxConst sTitle As String = "Oopsie Daisies"'~~> Change the above as applicableSub Sample()    Dim i As Integer, j As Integer10      On Error GoTo Whoa20      Application.ScreenUpdating = False30      i = 111111111140      For j = 1 To i50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value60      Next jLetsContinue:70      Exit SubWhoa:80      MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _           "Error Message : " & Err.Description & vbNewLine & _           "Error Number : " & Err.Number & vbNewLine & vbNewLine & _           sMsg, vbCritical, sTitle90      Resume LetsContinueEnd Sub

On to the next one :)

Reset Events if applicable

When you are working with events and an error occurs, if there is no error handling, the code breaks. Unfortunately that doesn't reset the events. It is very important that you reset the events in the Error handler.

If you notice in the above code we are setting the Application.ScreenUpdating = False. When the code breaks, that event doesn't get reset. You will have to handle that in the Error handler LetsContinue in this case. See this example.

'~~> Message you want to deliver to the user in case the error happensConst sMsg As String = "Please take a screenshot of this message and contact the developer for a resolution"'~~> Title of your message boxConst sTitle As String = "Oopsie Daisies"'~~> Change the above as applicableSub Sample()    Dim i As Integer, j As Integer10      On Error GoTo Whoa20      Application.ScreenUpdating = False30      i = 111111111140      For j = 1 To i50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value60      Next jLetsContinue:70      Application.ScreenUpdating = True80      Exit SubWhoa:90      MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _           "Error Message : " & Err.Description & vbNewLine & _           "Error Number : " & Err.Number & vbNewLine & vbNewLine & _           sMsg, vbCritical, sTitle100     Resume LetsContinueEnd Sub

Like Philippe, I also strongly suggest that you use MZ-Tools for VBA. I have been using it now for donkey years...

Hope this helps.

I needed a bit more help on this specific technique so I went right to the source and Mr. Bovey was gracious enough to reply. He gave me permission to post his response to the StackOverflow community.

The instructions below refer to his preferred method of error handling for functions the "boolean error handling" technique and not to the alternate "rethrow method", both described in his book "Professional Excel Development" 2nd edition.

Hi Shari,

In answer to your questions about error handling in functions, there arethree error handling scenarios you can have with a function in VBA:

1) The function is so trivial that is doesn't need an error handler. In theunlikely event an error occurs in a function like this it will spill overinto the error handler of the calling procedure.

2) A non-trivial function needs an error handler and uses the Boolean returnvalue system described in the book. Any other values the function needs toreturn are returned through ByRef arguments. This case covers the vast majority offunctions I write. There are some things you can't do with functions likethis, feeding them directly into the argument of another function is oneexample, but I consider this a good tradeoff in order to achieve bulletproof error handling.

3) A non-trivial function needs an error handler and must return a value notrelated to its error status. This is a rare situation because I can convert99% plus of these into case 2 by restructuring my code. If you can't dothis, your only choice is to select an arbitrary return value that is out ofthe range of normal return values and use this to indicate that an error hasoccurred. If the caller of this function sees this arbitrary error flagvalue it knows it can't continue.

Rob BoveyApplication Professionalshttp://www.appspro.com/

Code Example (Shari W)

' Show how to call a function using this error handling method.Const giBAD_RESULT As Integer = -1Function TestMath()   ' An Entry Point    Dim sngResult As Single    Dim iNum As Integer    ' Call the function, actual result goes in sngResult but it returns the error handling boolean.    ' A true error like Div 0 will go to error handler.    ' Set Up Error Handling for Entry Point    Application.EnableCancelKey = xlErrorHandler    Dim bUserCancel As Boolean    Const sSOURCE As String = "TestMath()"    On Error GoTo ErrorHandler    ' End Error Set Up    iNum = 0   ' Try 0 to create error    If Not bDoSomeMath(iNum, sngResult) Then ERR.Raise glHANDLED_ERROR    ' If function does parameter checking and wants to return a bad input code, check for that.    If sngResult = giBAD_RESULT Then        MsgBox ("Bad input to bDoSomeMath " & iNum)    Else        MsgBox ("I believe the answer is " & sngResult)    End IfErrorExit:    On Error Resume Next    Exit FunctionErrorHandler:    If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then        Stop        Resume    Else        Resume ErrorExit    End IfEnd FunctionFunction bDoSomeMath(ByVal iNum As Integer, ByRef sngResult As Single) As Boolean    ' Error handling Set Up    Dim bReturn As Boolean    Const sSOURCE As String = "bDoSomeMath()"    On Error GoTo ErrorHandler    bReturn = True    ' End Error Set Up    If iNum < 0 Or iNum > 1000 Then        sngResult = giBAD_RESULT   'function failed because I only like the numbers 0 to 1000        GoTo ErrorExit    Else        sngResult = 100 / iNum   ' generate a true error by iNum = 0    End IfErrorExit:    On Error Resume Next    bDoSomeMath = bReturn    Exit FunctionErrorHandler:    bReturn = False    If bCentralErrorHandler(msMODULE, sSOURCE, , , True) Then        Stop        Resume    Else        Resume ErrorExit    End IfEnd Function

a proposal for error handling management in VBA can be found here .

The very same tool (MZ-Tools) and method (standard/generic error handler, which could be used to build an automated error reporting system) will work with Excel.