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.

Answer 2

Two main purposes for error handling:

1. Trap errors you can predict but can't control the user from doing (e.g. saving a file to a thumb drive when the thumb drives has been removed)
2. For unexpected errors, present user with a form that informs them what the problem is. That way, they can relay that message to you and you might be able to give them a work-around while you work on a fix.

So, how would you do this?

First of all, create an error form to display when an unexpected error occurs.

It could look something like this (FYI: Mine is called frmErrors):

Notice the following labels:

• lblHeadline
• lblSource
• lblProblem
• lblResponse

Also, the standard command buttons:

• Ignore
• Retry
• Cancel

There's nothing spectacular in the code for this form:

Option Explicit  Private Sub cmdCancel_Click()   Me.Tag = CMD_CANCEL   Me.Hide End Sub  Private Sub cmdIgnore_Click()   Me.Tag = CMD_IGNORE   Me.Hide End Sub  Private Sub cmdRetry_Click()   Me.Tag = CMD_RETRY   Me.Hide End Sub  Private Sub UserForm_Initialize()   Me.lblErrorTitle.Caption = "Custom Error Title Caption String" End Sub  Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)   'Prevent user from closing with the Close box in the title bar.     If CloseMode <> 1 Then       cmdCancel_Click     End If End Sub 

Basically, you want to know which button the user pressed when the form closes.

Next, create an Error Handler Module that will be used throughout your VBA app:

'**************************************************************** '    MODULE: ErrorHandler ' '   PURPOSE: A VBA Error Handling routine to handle '             any unexpected errors ' '     Date:    Name:           Description: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '03/22/2010    Ray      Initial Creation '**************************************************************** Option Explicit  Global Const CMD_RETRY = 0 Global Const CMD_IGNORE = 1 Global Const CMD_CANCEL = 2 Global Const CMD_CONTINUE = 3  Type ErrorType     iErrNum As Long     sHeadline As String     sProblemMsg As String     sResponseMsg As String     sErrorSource As String     sErrorDescription As String     iBtnCap(3) As Integer     iBitmap As Integer End Type  Global gEStruc As ErrorType Sub EmptyErrStruc_S(utEStruc As ErrorType)   Dim i As Integer    utEStruc.iErrNum = 0   utEStruc.sHeadline = ""   utEStruc.sProblemMsg = ""   utEStruc.sResponseMsg = ""   utEStruc.sErrorSource = ""   For i = 0 To 2     utEStruc.iBtnCap(i) = -1   Next   utEStruc.iBitmap = 1  End Sub Function FillErrorStruct_F(EStruc As ErrorType) As Boolean   'Must save error text before starting new error handler   'in case we need it later   EStruc.sProblemMsg = Error(EStruc.iErrNum)   On Error GoTo vbDefaultFill    EStruc.sHeadline = "Error " & Format$(EStruc.iErrNum)   EStruc.sProblemMsg = EStruc.sErrorDescription   EStruc.sErrorSource = EStruc.sErrorSource   EStruc.sResponseMsg = "Contact the Company and tell them you received Error # " & Str$(EStruc.iErrNum) & ". You should write down the program function you were using, the record you were working with, and what you were doing."     Select Case EStruc.iErrNum        'Case Error number here        'not sure what numeric errors user will ecounter, but can be implemented here        'e.g.        'EStruc.sHeadline = "Error 3265"        'EStruc.sResponseMsg = "Contact tech support. Tell them what you were doing in the program."       Case Else         EStruc.sHeadline = "Error " & Format$(EStruc.iErrNum) & ": " & EStruc.sErrorDescription        EStruc.sProblemMsg = EStruc.sErrorDescription     End Select     GoTo FillStrucEnd  vbDefaultFill:    'Error Not on file   EStruc.sHeadline = "Error " & Format$(EStruc.iErrNum) & ": Contact Tech Support"   EStruc.sResponseMsg = "Contact the Company and tell them you received Error # " & Str$(EStruc.iErrNum) FillStrucEnd:    Exit Function  End Function Function iErrorHandler_F(utEStruc As ErrorType) As Integer   Static sCaption(3) As String   Dim i As Integer   Dim iMCursor As Integer    Beep    'Setup static array   If Len(sCaption(0)) < 1 Then     sCaption(CMD_IGNORE) = "&Ignore"     sCaption(CMD_RETRY) = "&Retry"     sCaption(CMD_CANCEL) = "&Cancel"     sCaption(CMD_CONTINUE) = "Continue"   End If    Load frmErrors    'Did caller pass error info?  If not fill struc with the needed info   If Len(utEStruc.sHeadline) < 1 Then     i = FillErrorStruct_F(utEStruc)   End If    frmErrors!lblHeadline.Caption = utEStruc.sHeadline   frmErrors!lblProblem.Caption = utEStruc.sProblemMsg   frmErrors!lblSource.Caption = utEStruc.sErrorSource   frmErrors!lblResponse.Caption = utEStruc.sResponseMsg    frmErrors.Show   iErrorHandler_F = frmErrors.Tag   ' Save user response   Unload frmErrors                  ' Unload and release form    EmptyErrStruc_S utEStruc          ' Release memory  End Function 

You may have errors that will be custom only to your application. This would typically be a short list of errors specifically only to your application. If you don't already have a constants module, create one that will contain an ENUM of your custom errors. (NOTE: Office '97 does NOT support ENUMS.). The ENUM should look something like this:

Public Enum CustomErrorName   MaskedFilterNotSupported   InvalidMonthNumber End Enum 

Create a module that will throw your custom errors.

'******************************************************************************************************************************** '    MODULE: CustomErrorList ' '   PURPOSE: For trapping custom errors applicable to this application ' 'INSTRUCTIONS:  To use this module to create your own custom error: '               1.  Add the Name of the Error to the CustomErrorName Enum '               2.  Add a Case Statement to the raiseCustomError Sub '               3.  Call the raiseCustomError Sub in the routine you may see the custom error '               4.  Make sure the routine you call the raiseCustomError has error handling in it ' ' '     Date:    Name:           Description: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '03/26/2010    Ray       Initial Creation '******************************************************************************************************************************** Option Explicit Const MICROSOFT_OFFSET = 512 'Microsoft reserves error values between vbObjectError and vbObjectError + 512 '************************************************************************************************ '  FUNCTION:  raiseCustomError ' '   PURPOSE:  Raises a custom error based on the information passed ' 'PARAMETERS:  customError - An integer of type CustomErrorName Enum that defines the custom error '             errorSource - The place the error came from ' '   Returns:  The ASCII vaule that should be used for the Keypress ' '     Date:    Name:           Description: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '03/26/2010    Ray       Initial Creation '************************************************************************************************ Public Sub raiseCustomError(customError As Integer, Optional errorSource As String = "")   Dim errorLong As Long   Dim errorDescription As String    errorLong = vbObjectError + MICROSOFT_OFFSET + customError    Select Case customError      Case CustomErrorName.MaskedFilterNotSupported       errorDescription = "The mask filter passed is not supported"      Case CustomErrorName.InvalidMonthNumber       errorDescription = "Invalid Month Number Passed"      Case Else       errorDescription = "The custom error raised is unknown."    End Select    Err.Raise errorLong, errorSource, errorDescription  End Sub 

You are now well equipped to trap errors in your program. You sub (or function), should look something like this:

Public Sub MySub(monthNumber as Integer)   On Error GoTo eh      Dim sheetWorkSheet As Worksheet    'Run Some code here    '************************************************   '*   OPTIONAL BLOCK 1:  Look for a specific error   '************************************************   'Temporarily Turn off Error Handling so that you can check for specific error   On Error Resume Next   'Do some code where you might expect an error.  Example below:   Const ERR_SHEET_NOT_FOUND = 9 'This error number is actually subscript out of range, but for this example means the worksheet was not found    Set sheetWorkSheet = Sheets("January")    'Now see if the expected error exists    If Err.Number = ERR_SHEET_NOT_FOUND Then     MsgBox "Hey!  The January worksheet is missing.  You need to recreate it."     Exit Sub   ElseIf Err.Number <> 0 Then     'Uh oh...there was an error we did not expect so just run basic error handling      GoTo eh   End If    'Finished with predictable errors, turn basic error handling back on:   On Error GoTo eh    '**********************************************************************************   '*   End of OPTIONAL BLOCK 1   '**********************************************************************************    '**********************************************************************************   '*   OPTIONAL BLOCK 2:  Raise (a.k.a. "Throw") a Custom Error if applicable   '**********************************************************************************   If not (monthNumber >=1 and monthnumber <=12) then     raiseCustomError CustomErrorName.InvalidMonthNumber, "My Sub"   end if   '**********************************************************************************   '*   End of OPTIONAL BLOCK 2   '**********************************************************************************    'Rest of code in your sub    goto sub_exit  eh:   gEStruc.iErrNum = Err.Number   gEStruc.sErrorDescription = Err.Description   gEStruc.sErrorSource = Err.Source   m_rc = iErrorHandler_F(gEStruc)    If m_rc = CMD_RETRY Then     Resume   End If  sub_exit:   'Any final processing you want to do.   'Be careful with what you put here because if it errors out, the error rolls up.  This can be difficult to debug; especially if calling routine has no error handling.    Exit Sub 'I was told a long time ago (10+ years) that exit sub was better than end sub...I can't tell you why, so you may not want to put in this line of code.  It's habit I can't break :P End Sub 

A copy/paste of the code above may not work right out of the gate, but should definitely give you the gist.

BTW, if you ever need me to do your company logo, look me up at http://www.MySuperCrappyLogoLabels99.com