Good templates for handling VBA errors

Question

What are some good error handling templates in VBA?

In particular, what should I do in this situation:

... some code ... ... some code where an error might occur ... ... some code ... ... some other code where a different error might occur ... ... some other code ... ... some code that must always be run (like a finally block) ... 

I want to handle both errors and resume execution after the code in which the error may occur. In addition, the final code at the end should ALWAYS work - no matter what exceptions were thrown earlier. How can I achieve this result?

Answer 1

Error handling in VBA

• On Error Goto ErrorHandlerLabel
• Resume ( Next | ErrorHandlerLabel)
• On Error Goto 0 (disables the current error handler)
• Err object

Err object properties are usually reset to zero or a zero-length string in the error handling routine, but this can also be done explicitly with Err.Clear .

Errors in the error handling procedure end.

The range 513-65535 is available for user errors. For custom class errors, you add vbObjectError to the error number. See the MS Documentation for Err.Raise and a list of error numbers .

For non-implemented interface members in a derived class, you must use the constant E_NOTIMPL = &H80004001 .

---

 Option Explicit Sub HandleError() Dim a As Integer On Error GoTo errMyErrorHandler a = 7 / 0 On Error GoTo 0 Debug.Print "This line won't be executed." DoCleanUp: a = 0 Exit Sub errMyErrorHandler: MsgBox Err.Description, _ vbExclamation + vbOKCancel, _ "Error: " & CStr(Err.Number) Resume DoCleanUp End Sub Sub RaiseAndHandleError() On Error GoTo errMyErrorHandler ' The range 513-65535 is available for user errors. ' For class errors, you add vbObjectError to the error number. Err.Raise vbObjectError + 513, "Module1::Test()", "My custom error." On Error GoTo 0 Debug.Print "This line will be executed." Exit Sub errMyErrorHandler: MsgBox Err.Description, _ vbExclamation + vbOKCancel, _ "Error: " & CStr(Err.Number) Err.Clear Resume Next End Sub Sub FailInErrorHandler() Dim a As Integer On Error GoTo errMyErrorHandler a = 7 / 0 On Error GoTo 0 Debug.Print "This line won't be executed." DoCleanUp: a = 0 Exit Sub errMyErrorHandler: a = 7 / 0 ' <== Terminating error! MsgBox Err.Description, _ vbExclamation + vbOKCancel, _ "Error: " & CStr(Err.Number) Resume DoCleanUp End Sub Sub DontDoThis() ' Any error will go unnoticed! On Error Resume Next ' Some complex code that fails here. End Sub Sub DoThisIfYouMust() On Error Resume Next ' Some code that can fail but you don't care. On Error GoTo 0 ' More code here End Sub 

Answer 2

I would add:

• Err global object closest to exception object
• You can effectively throw an exception with Err.Raise

And just for fun:

• On Error Resume Next is the incarnation of the devil and should be avoided because it silently hides errors.

Answer 3

So you can do something like this

 Function Errorthingy(pParam) On Error GoTo HandleErr ' your code here ExitHere: ' your finally code Exit Function HandleErr: Select Case Err.Number ' different error handling here' Case Else MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ErrorThingy" End Select Resume ExitHere End Function 

---

If you want to bake custom exceptions. (for example, those that violate business rules) use the above example, but use goto to change the flow of the method as necessary.

Answer 4

Here is my standard implementation. I like shortcuts as self-descriptive.

 Public Sub DoSomething() On Error GoTo Catch ' Try ' normal code here Exit Sub Catch: 'error code: you can get the specific error by checking Err.Number End Sub 

Or with the Finally block:

 Public Sub DoSomething() On Error GoTo Catch ' Try ' normal code here GoTo Finally Catch: 'error code Finally: 'cleanup code End Sub 

Answer 5

Professional Excel Development has a pretty good error handling scheme . If you're planning on spending time in VBA, it's probably worth getting a book. There are a number of areas where VBA is missing, and there are good suggestions for managing these areas in this book.

PED describes two error handling methods. The main one is a system in which all entry point procedures are subprocedures, and all other procedures are functions that return logical values.

The entry point procedure is used in Error Error operations to fix errors to a large extent, as envisioned. Procedures other than the entry point return True if there were no errors and False if there were errors. Non-dot procedures also use On Error.

Both types of procedures use a central error handling procedure to keep the error in a state and record the error.

Answer 6

I use a piece of code that I developed myself, and it is good for my codes:

At the beginning of a function or sub, I define:

 On error Goto ErrorCatcher: 

and then I handle possible errors

 ErrorCatcher: Select Case Err.Number Case 0 'exit the code when no error was raised On Error GoTo 0 Exit Function Case 1 'Error on definition of object 'do stuff Case... 'little description here 'do stuff Case Else Debug.Print "###ERROR" Debug.Print " • Number :", Err.Number Debug.Print " • Descrip :", Err.Description Debug.Print " • Source :", Err.Source Debug.Print " • HelpCont:", Err.HelpContext Debug.Print " • LastDLL :", Err.LastDllError Stop Err.Clear Resume End Select 

Answer 7

Here's a pretty decent template.

For debugging: if an error occurs, press Ctrl-Break (or Ctrl-Pause), drag the break marker (or something else that it called) to the resume line, press F8, and you will go to the line, "thrown" the error.

ExitHandler is your "Finally."

The hourglass will be killed every time. The status bar text will be cleared every time.

 Public Sub ErrorHandlerExample() Dim dbs As DAO.Database Dim rst As DAO.Recordset On Error GoTo ErrHandler Dim varRetVal As Variant Set dbs = CurrentDb Set rst = dbs.OpenRecordset("SomeTable", dbOpenDynaset, dbSeeChanges + dbFailOnError) Call DoCmd.Hourglass(True) 'Do something with the RecordSet and close it. Call DoCmd.Hourglass(False) ExitHandler: Set rst = Nothing Set dbs = Nothing Exit Sub ErrHandler: Call DoCmd.Hourglass(False) Call DoCmd.SetWarnings(True) varRetVal = SysCmd(acSysCmdClearStatus) Dim errX As DAO.Error If Errors.Count > 1 Then For Each errX In DAO.Errors MsgBox "ODBC Error " & errX.Number & vbCrLf & errX.Description Next errX Else MsgBox "VBA Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & "In: Form_MainForm", vbCritical End If Resume ExitHandler Resume End Sub Select Case Err.Number Case 3326 'This Recordset is not updateable 'Do something about it. Or not... Case Else MsgBox "VBA Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & "In: Form_MainForm", vbCritical End Select 

It also traps for DAO and VBA errors. You can put a case of choice in the VBA error section if you want a trap for specific Err numbers.

 Select Case Err.Number Case 3326 'This Recordset is not updateable 'Do something about it. Or not... Case Else MsgBox "VBA Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & "In: Form_MainForm", vbCritical End Select 

Answer 8

The code below presents an alternative that guarantees only one exit point for the sub / function.

 sub something() on error goto errHandler ' start of code .... .... 'end of code ' 1. not needed but signals to any other developer that looks at this ' code that you are skipping over the error handler... ' see point 1... err.clear errHandler: if err.number <> 0 then ' error handling code end if end sub 

Answer 9

Also relevant for discussion is the relatively unknown Erl function. If you have numeric labels inside your code procedure, for example,

 Sub AAA() On Error Goto ErrorHandler 1000: ' code 1100: ' more code 1200: ' even more code that causes an error 1300: ' yet more code 9999: ' end of main part of procedure ErrorHandler: If Err.Number <> 0 Then Debug.Print "Error: " + CStr(Err.Number), Err.Descrption, _ "Last Successful Line: " + CStr(Erl) End If End Sub 

The Erl function returns the most recent numeric string label. In the above example, if after the label 1200: a runtime error occurs, but before 1300: the Erl function returns 1200 , since this is the most satisfactory successful label for the string. I consider it a good practice to put a line mark directly above the error handling block. I usually use 9999 to indicate that the bulk of the process has exceeded its expected completion.

NOTES:

• Line labels MUST be positive integers - a label of type MadeItHere: Erl does not re-register.

• Line labels are completely unrelated to the actual line numbers of the VBIDE CodeModule . You can use any positive numbers you want in any order. In the above example, there are only about 25 lines of code, but line label numbers start at 1000 . There is no relation between the line numbers of the editors and the line label numbers used with Erl .

• Line label numbers do not have to be in any particular order, although if they are not in ascending order, from top to bottom, Erl efficiency and benefits are greatly reduced, but Erl will report the correct number.

• Line marks refer to the procedure in which they are displayed. If the ProcA procedure calls the ProcB procedure and an error occurs in ProcB that returns control back to ProcA , Erl (in ProcA ), then the last counter number of the line label in ProcA before it calls ProcB . From ProcA you cannot get line label numbers that can be displayed in ProcB .

Be careful when setting line labels in a loop. For example,

 For X = 1 To 100 500: ' some code that causes an error 600: Next X 

If the code following the line label 500 , but up to 600 causes an error, and this error occurs on the 20th iteration of the loop, Erl returns 500 , even if 600 occurs successfully in the previous 19 cases of the loop.

The proper placement of line labels in a procedure is critical to using the Erl function to get really meaningful information.

There are a number of free utilies on the network that will automatically insert a numerical string label into the procedure, so when developing and debugging you will get small-scale error information, and then delete these labels after the code starts to live.

If your code displays error information to the end user, if an unexpected error occurs, provided that the Erl value in this information can make finding and fixing the VASTLY problem easier than if the Erl value is not specified.

Answer 10

Watch out for elephant traps:

I did not mention this in this discussion. [Access 2010]

How ACCESS / VBA handles errors in CLASS objects is determined by a custom option:

VBA Code Editor> Tools> Options> General> Error Trap:

Answer 11

I believe the following works best, called the central error handling approach.

Benefits

You have 2 application launch modes: debugging and production. In debug mode, the code will stop if there is any unexpected error and will allow you to easily debug by going to the line where it happened by pressing the F8 key twice. In production mode, a meaningful error message will be displayed to the user.

You can throw out deliberate errors like this, which will stop the code from executing a message to the user:

 Err.Raise vbObjectError, gsNO_DEBUG, "Some meaningful error message to the user" Err.Raise vbObjectError, gsUSER_MESSAGE, "Some meaningful non-error message to the user" 'Or to exit in the middle of a call stack without a message: Err.Raise vbObjectError, gsSILENT 

Implementation

You need to “wrap” all routines and functions with any significant amount of code with the following headers and ehCallTypeEntryPoint headers, making sure that you specify ehCallTypeEntryPoint at all your entry points. Also pay attention to the msModule constant, which must be placed in all modules.

 Option Explicit Const msModule As String = "<Your Module Name>" ' This is an entry point Public Sub AnEntryPoint() Const sSOURCE As String = "AnEntryPoint" On Error GoTo ErrorHandler 'Your code ErrorExit: Exit Sub ErrorHandler: If CentralErrorHandler(Err, ThisWorkbook, msModule, sSOURCE, ehCallTypeEntryPoint) Then Stop Resume Else Resume ErrorExit End If End Sub ' This is any other subroutine or function that isn't an entry point Sub AnyOtherSub() Const sSOURCE As String = "AnyOtherSub" On Error GoTo ErrorHandler 'Your code ErrorExit: Exit Sub ErrorHandler: If CentralErrorHandler(Err, ThisWorkbook, msModule, sSOURCE) Then Stop Resume Else Resume ErrorExit End If End Sub 

The contents of the central error handler module:

 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Comments: Error handler code. ' ' Run SetDebugMode True to use debug mode (Dev mode) ' It will be False by default (Production mode) ' ' Author: Igor Popov ' Date: 13 Feb 2014 ' Licence: MIT ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Option Explicit Option Private Module Private Const msModule As String = "MErrorHandler" Public Const gsAPP_NAME As String = "<You Application Name>" Public Const gsSILENT As String = "UserCancel" 'A silent error is when the user aborts an action, no message should be displayed Public Const gsNO_DEBUG As String = "NoDebug" 'This type of error will display a specific message to the user in situation of an expected (provided-for) error. Public Const gsUSER_MESSAGE As String = "UserMessage" 'Use this type of error to display an information message to the user Private Const msDEBUG_MODE_COMPANY = "<Your Company>" Private Const msDEBUG_MODE_SECTION = "<Your Team>" Private Const msDEBUG_MODE_VALUE = "DEBUG_MODE" Public Enum ECallType ehCallTypeRegular = 0 ehCallTypeEntryPoint End Enum Public Function DebugMode() As Boolean DebugMode = CBool(GetSetting(msDEBUG_MODE_COMPANY, msDEBUG_MODE_SECTION, msDEBUG_MODE_VALUE, 0)) End Function Public Sub SetDebugMode(Optional bMode As Boolean = True) SaveSetting msDEBUG_MODE_COMPANY, msDEBUG_MODE_SECTION, msDEBUG_MODE_VALUE, IIf(bMode, 1, 0) End Sub ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Comments: The central error handler for all functions ' Displays errors to the user at the entry point level, or, if we're below the entry point, rethrows it upwards until the entry point is reached ' ' Returns True to stop and debug unexpected errors in debug mode. ' ' The function can be enhanced to log errors. ' ' Date Developer TDID Comment ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 13 Feb 2014 Igor Popov Created Public Function CentralErrorHandler(ErrObj As ErrObject, Wbk As Workbook, ByVal sModule As String, ByVal sSOURCE As String, _ Optional enCallType As ECallType = ehCallTypeRegular, Optional ByVal bRethrowError As Boolean = True) As Boolean Static ssModule As String, ssSource As String If Len(ssModule) = 0 And Len(ssSource) = 0 Then 'Remember the module and the source of the first call to CentralErrorHandler ssModule = sModule ssSource = sSOURCE End If CentralErrorHandler = DebugMode And ErrObj.Source <> gsNO_DEBUG And ErrObj.Source <> gsUSER_MESSAGE And ErrObj.Source <> gsSILENT If CentralErrorHandler Then 'If it an unexpected error and we're going to stop in the debug mode, just write the error message to the immediate window for debugging Debug.Print "#Err: " & Err.Description ElseIf enCallType = ehCallTypeEntryPoint Then 'If we have reached the entry point and it not a silent error, display the message to the user in an error box If ErrObj.Source <> gsSILENT Then Dim sMsg As String: sMsg = ErrObj.Description If ErrObj.Source <> gsNO_DEBUG And ErrObj.Source <> gsUSER_MESSAGE Then sMsg = "Unexpected VBA error in workbook '" & Wbk.Name & "', module '" & ssModule & "', call '" & ssSource & "':" & vbCrLf & vbCrLf & sMsg MsgBox sMsg, vbOKOnly + IIf(ErrObj.Source = gsUSER_MESSAGE, vbInformation, vbCritical), gsAPP_NAME End If ElseIf bRethrowError Then 'Rethrow the error to the next level up if bRethrowError is True (by Default). 'Otherwise, do nothing as the calling function must be having special logic for handling errors. Err.Raise ErrObj.Number, ErrObj.Source, ErrObj.Description End If End Function 

To set yourself in debug mode, do the following in the Immediate window:

 SetDebugMode True 

Answer 12

My personal look at the expression made earlier in this thread:

And just for fun:

In the “Error” section: “Resuming Errors” is the devil's incarnation, which should be avoided because it hides errors.

I use On Error Resume Next in procedures where I do not want the error to stop my work and where some operator does not depend on the result of previous statements.

When I do this, I add the global variable debugModeOn and I set it to True . Then I use it as follows:

 If not debugModeOn Then On Error Resume Next 

When I deliver my work, I set the variable to false, thus hiding errors only for the user and showing them during testing.

Also use it when doing something that might fail, like calling a DataBodyRange of a ListObject, which might be empty:

 On Error Resume Next Sheet1.ListObjects(1).DataBodyRange.Delete On Error Goto 0 

Instead:

 If Sheet1.ListObjects(1).ListRows.Count > 0 Then Sheet1.ListObjects(1).DataBodyRange.Delete End If 

Or check for an item in the collection:

 On Error Resume Next Err.Clear Set auxiliarVar = collection(key) ' Check existence (if you try to retrieve a nonexistant key you get error number 5) exists = (Err.Number <> 5)