Correct error handling in VBA (Excel)

Question

I have been working with VBA for a long time, but I'm still not sure about error handling.

Good article - one of CPearson.com

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

On Error Goto ErrCatcher If UBound(.sortedDates) > 0 Then // Code Else ErrCatcher: // Code End If 

The if clause is because if it is true, it will be executed, and if it does not work, Goto will go into the Else part, since the Ubound array should never be zero or less, without error, this method has worked well so far.

If I understood correctly, 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 that: 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 that I see is that the "Catcher" error is at the end of the sub, and the Sub actually ends earlier with the "Exit Sub", but this, however, is a bit confusing if the Sub is big enough if you jump vice versa to read the code?

Block 4

Source for 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 in block 3?

Thanks for reading my question. Greetings skofgar

Answer 1

I will definitely not use Block1. It seems that the wrong Error block in IF is not error related.

Blocks 2,3 and 4, I think, are variations of the theme. I prefer to use blocks 3 and 4 over 2 just because of the hostility to the GOTO statement; I usually use the Block4 method. This is one example of code that I use to check if the Microsoft ActiveX Data Objects 2.8 library has been added, and if not add or use an earlier version if 2.8 is not available.

 Option Explicit Public booRefAdded As Boolean 'one time check for references Public Sub Add_References() Dim lngDLLmsadoFIND As Long If Not booRefAdded Then lngDLLmsadoFIND = 28 ' load msado28.tlb, if cannot find step down versions until found On Error GoTo RefErr: 'Add Microsoft ActiveX Data Objects 2.8 Application.VBE.ActiveVBProject.references.AddFromFile _ Environ("CommonProgramFiles") + "\System\ado\msado" & lngDLLmsadoFIND & ".tlb" On Error GoTo 0 Exit Sub RefErr: Select Case Err.Number Case 0 'no error Case 1004 'Enable Trust Centre Settings MsgBox ("Certain VBA References are not available, to allow access follow these steps" & Chr(10) & _ "Goto Excel Options/Trust Centre/Trust Centre Security/Macro Settings" & Chr(10) & _ "1. Tick - 'Disable all macros with notification'" & Chr(10) & _ "2. Tick - 'Trust access to the VBA project objects model'") End Case 32813 'Err.Number 32813 means reference already added Case 48 'Reference doesn't exist If lngDLLmsadoFIND = 0 Then MsgBox ("Cannot Find Required Reference") End Else For lngDLLmsadoFIND = lngDLLmsadoFIND - 1 To 0 Step -1 Resume Next lngDLLmsadoFIND End If Case Else MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!" End End Select On Error GoTo 0 End If booRefAdded = TRUE End Sub 

Answer 2

You have one truly wonderful answer from ray023, but your comment is that it is probably too crowded. For the lighter version ....

Block 1 is, IMHO, bad practice. As osknows already pointed out, mixing errors with normal path code is not good. Firstly, if a new error occurs when an error condition occurs, you may not be able to handle it (unless you call from a procedure that also has an error handler where execution is “bubbling”).

Block 2 looks like an imitation of the Try / Catch block. This should be fine, but that is not the VBA way. Block 3 is an option on block 2.

Block 4 is the bare version of VBA Way. I would strongly recommend using it or something like that, because any other VBA programmer who inherits the code would expect this. Let me introduce a small extension, 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 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 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 

Please note that the second is Resume . This is a trick I recently learned: it will never be executed during normal processing, as the Resume <label> statement will send the execution elsewhere. However, this may be a godsend for debugging. When you receive an error notification, select "Debug" (or press "Ctl-Break", then select "Debug" when you receive the message "Execution was interrupted"). The next (highlighted) statement will be either MsgBox or the next statement. Use Set Next Expression (Ctl-F9) to highlight a bare Resume , and then press F8. This will show you exactly where the error was selected.

Regarding your objection to this “bouncing around” format, A) this is what VBA programmers expect, as mentioned earlier, and B) your routines should be short enough so that it cannot jump.

Answer 3

Two main goals for error handling:

• Trap errors you can predict, but cannot control the user (for example, saving a file to a flash drive when flash drives were deleted)
• For unforeseen errors, the real user has a form that tells them what the problem is. That way, they can message you, and you may be able to give them a workaround while you work on the fix.

So how would you do that?

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

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

Pay attention to the following tags:

• lblHeadline
• lblSource
• lblProblem
• lblResponse

In addition, standard command buttons:

• Ignore
• Retry
• Cancel

There is 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 clicked when the form closes.

Next, create the error handler module that will be used in your VBA application:

 '**************************************************************** ' 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 'eg '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 available only for your application. This will usually be a short list of errors, especially for your application. If you don’t have a constant module yet, create one that will contain ENUM of your custom errors. (NOTE: Office '97 does NOT support ENUMS.). ENUM should look something like this:

 Public Enum CustomErrorName MaskedFilterNotSupported InvalidMonthNumber End Enum 

Create a module that will generate 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 prepared for the error traps in your program. 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 (aka "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 habit I can't break :P End Sub 

Copying / pasting the above code may not work right from the gate, but it must give you the gist.

By the way, if you ever need to make your company logo, find me at http://www.MySuperCrappyLogoLabels99.com

Answer 4

I make it simple:
At the module level, I define two variables and set it to the name of the module itself.

  Private Const ThisModuleName As String = "mod_Custom_Functions" Public sLocalErrorMsg As String 

Inside each Sub / Function module, I define a local variable

  Dim ThisRoutineName As String 

I set ThisRoutineName to the name of a sub or function

 ' Housekeeping On Error Goto ERR_RTN ThisRoutineName = "CopyWorksheet" 

Then I send all errors to ERR_RTN: when they happen, but I first set sLocalErrorMsg to determine what is actually an error and provide some information for debugging.

  If Len(Trim(FromWorksheetName)) < 1 Then sLocalErrorMsg = "Parameter 'FromWorksheetName' Is Missing." GoTo ERR_RTN End If 

At the bottom of each sub / function, I direct the logical flow as follows

  ' ' The "normal" logic goes here for what the routine does ' GoTo EXIT_RTN ERR_RTN: On Error Resume Next ' Call error handler if we went this far. ErrorHandler ThisModuleName, ThisRoutineName, sLocalErrorMsg, Err.Description, Err.Number, False EXIT_RTN: On Error Resume Next ' ' Some closing logic ' End If 

Then I have a separate module that I put in all projects called "mod_Error_Handler".

  ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Subroutine Name: ErrorHandler ' ' ' ' Description: ' ' This module will handle the common error alerts. ' ' ' ' Inputs: ' ' ModuleName String 'The name of the module error is in. ' ' RoutineName String 'The name of the routine error in in. ' ' LocalErrorMsg String 'A local message to assist with troubleshooting.' ' ERRDescription String 'The Windows Error Description. ' ' ERRCode Long 'The Windows Error Code. ' ' Terminate Boolean 'End program if error encountered? ' ' ' ' Revision History: ' ' Date (YYYYMMDD) Author Change ' ' =============== ===================== =============================================== ' ' 20140529 XXXXX X. XXXXX Original ' ' ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Public Sub ErrorHandler(ModuleName As String, RoutineName As String, LocalErrorMsg As String, ERRDescription As String, ERRCode As Long, Terminate As Boolean) Dim sBuildErrorMsg As String ' Build Error Message To Display sBuildErrorMsg = "Error Information:" & vbCrLf & vbCrLf If Len(Trim(ModuleName)) < 1 Then ModuleName = "Unknown" End If If Len(Trim(RoutineName)) < 1 Then RoutineName = "Unknown" End If sBuildErrorMsg = sBuildErrorMsg & "Module Name: " & ModuleName & vbCrLf & vbCrLf sBuildErrorMsg = sBuildErrorMsg & "Routine Name: " & RoutineName & vbCrLf & vbCrLf If Len(Trim(LocalErrorMsg)) > 0 Then sBuildErrorMsg = sBuildErrorMsg & "Local Error Msg: " & LocalErrorMsg & vbCrLf & vbCrLf End If If Len(Trim(ERRDescription)) > 0 Then sBuildErrorMsg = sBuildErrorMsg & "Program Error Msg: " & ERRDescription & vbCrLf & vbCrLf If IsNumeric(ERRCode) Then sBuildErrorMsg = sBuildErrorMsg & "Program Error Code: " & Trim(Str(ERRCode)) & vbCrLf & vbCrLf End If End If MsgBox sBuildErrorMsg, vbOKOnly + vbExclamation, "Error Detected!" If Terminate Then End End If End Sub 

The end result is a pop-up error message telling me in which module what soubroutine is and what error message was specific. In addition, he will also enter a Windows error message and code.

Answer 5

Block 2 does not work because it does not reset the error handler, potentially causing an infinite loop. To work correctly with an error in VBA, you will need a Resume statement to clear the error handler. Resume also activates the previous error handler. Block 2 fails because the new error will revert to the previous error handler, causing an infinite loop.

Block 3 error because there is no Resume statement, so any attempt to handle errors after this will fail.

Each error handler must be completed exiting the procedure or Resume statement. The usual routing around an error handler is confusing. This is why error handlers are usually found below.

But here is another way to handle the error in VBA. It handles a built-in error code like Try / Catch in VB.net. There are several pitfalls, but properly managed it works very well.

 Sub InLineErrorHandling() 'code without error handling BeginTry1: 'activate inline error handler On Error GoTo ErrHandler1 'code block that may result in an error Dim a As String: a = "Abc" Dim c As Integer: c = a 'type mismatch ErrHandler1: 'handle the error If Err.Number <> 0 Then 'the error handler has deactivated the previous error handler MsgBox (Err.Description) 'Resume (or exit procedure) is the only way to get out of an error handling block 'otherwise the following On Error statements will have no effect 'CAUTION: it also reactivates the previous error handler Resume EndTry1 End If EndTry1: 'CAUTION: since the Resume statement reactivates the previous error handler 'you must ALWAYS use an On Error GoTo statement here 'because another error here would cause an endless loop 'use On Error GoTo 0 or On Error GoTo <Label> On Error GoTo 0 'more code with or without error handling End Sub 

Sources:

• http://www.cpearson.com/excel/errorhandling.htm
• http://msdn.microsoft.com/en-us/library/bb258159.aspx

The key to doing this is using the Resume statement, followed by another On Error statement. Resume is located inside the error handler and redirects the code to the EndTry1 label. You must immediately set another On Error statement to avoid problems, as the previous error handler will “resume”. That is, it will be active and ready to handle another error. This can cause the error to repeat and enter an infinite loop.

To avoid reusing the previous error handler, you need to install On Error in a new error handler or just use On Error Goto 0 to cancel all error handling.