What are some good patterns for error handling 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 where the error may occur. Also, the finally code at the end must ALWAYS run - no matter what exceptions are thrown earlier. How can I achieve this outcome?
This question is related to
exception
vba
exception-handling
I find the following to work best, called the central error handling approach.
You have 2 modes of running your application: Debug and Production. In the Debug mode, the code will stop at any unexpected error and allow you to debug easily by jumping to the line where it occurred by pressing F8 twice. In the Production mode, a meaningful error message will get displayed to the user.
You can throw intentional errors like this, which will stop execution of the code with 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
You need to "wrap" all subroutines and functions with any significant amount of code with the following headers and footers, making sure to specify ehCallTypeEntryPoint
in all your entry points. Note the msModule
constant as well, which needs to be put 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 is the following:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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's 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's 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 the Debug mode, run the following in the Immediate window:
SetDebugMode True
Professional Excel Development has a pretty good error handling scheme. If you're going to spend any time in VBA, it's probably worth getting the book. There are a number of areas where VBA is lacking and this book has good suggestions for managing those areas.
PED describes two error handling methods. The main one is a system where all entry point procedures are subprocedures and all other procedures are functions that return Booleans.
The entry point procedure use On Error statements to capture errors pretty much as designed. The non-entry point procedures return True if there were no errors and False if there were errors. Non-entry point procedures also use On Error.
Both types of procedures use a central error handling procedure to keep the error in state and to log the error.
Here's a pretty decent pattern.
For debugging: When an error is raised, hit Ctrl-Break (or Ctrl-Pause), drag the break marker (or whatever it's called) down to the Resume line, hit F8 and you'll step to the line that "threw" the error.
The ExitHandler is your "Finally".
Hourglass will be killed every time. 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 both DAO and VBA errors. You can put a Select Case in the VBA error section if you want to 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
My personal view on a statement made earlier in this thread:
And just for fun:
On Error Resume Next is the devil incarnate and to be avoided, as it silently hides errors.
I'm using the On Error Resume Next
on procedures where I don't want an error to stop my work and where any statement does not depend on the result of the previous statements.
When I'm doing this I add a global variable debugModeOn
and I set it to True
. Then I use it this way:
If not debugModeOn Then On Error Resume Next
When I deliver my work, I set the variable to false, thus hiding the errors only to the user and showing them during testing.
Also using it when doing something that may fail like calling the DataBodyRange of a ListObject that may be empty:
On Error Resume Next
Sheet1.ListObjects(1).DataBodyRange.Delete
On Error Goto 0
Instead of:
If Sheet1.ListObjects(1).ListRows.Count > 0 Then
Sheet1.ListObjects(1).DataBodyRange.Delete
End If
Or checking existence of an item in a 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)
The code below shows an alternative that ensures there is 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
I would also add:
Err
object is the closest you have to an exception objectErr.Raise
And just for fun:
On Error Resume Next
is the devil incarnate and to be avoided, as it silently hides errorsHere's my standard implementation. I like the labels to be 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 a Finally
block:
Public Sub DoSomething()
On Error GoTo Catch ' Try
' normal code here
GoTo Finally
Catch:
'error code
Finally:
'cleanup code
End Sub
Also relevant to the discussion is the relatively unknown Erl
function. If you have numeric labels within your code procedure, e.g.,
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 recently encountered numberic line label. In the example above, if a run-time error occurs after label 1200:
but before 1300:
, the Erl
function will return 1200
, since that is most recenlty sucessfully encountered line label. I find it to be a good practice to put a line label immediately above your error handling block. I typcially use 9999
to indicate that the main part of the procuedure ran to its expected conculsion.
NOTES:
Line labels MUST be positive integers -- a label like MadeItHere:
isn't recogonized by Erl
.
Line labels are completely unrelated to the actual line numbers of a VBIDE CodeModule
. You can use any positive numbers you want, in any order you want. In the example above, there are only 25 or so lines of code, but the line label numbers begin at 1000
. There is no relationship between editor line numbers and line label numbers used with Erl
.
Line label numbers need not be in any particular order, although if they are not in ascending, top-down order, the efficacy and benefit of Erl
is greatly diminished, but Erl
will still report the correct number.
Line labels are specific to the procedure in which they appear. If procedure ProcA
calls procedure ProcB
and an error occurs in ProcB
that passes control back to ProcA
, Erl
(in ProcA
) will return the most recently encounterd line label number in ProcA
before it calls ProcB
. From within ProcA
, you cannot get the line label numbers that might appear in ProcB
.
Use care when putting line number labels within a loop. For example,
For X = 1 To 100
500:
' some code that causes an error
600:
Next X
If the code following line label 500
but before 600
causes an error, and that error arises on the 20th iteration of the loop, Erl
will return 500
, even though 600
has been encounterd successfully in the previous 19 interations of the loop.
Proper placement of line labels within the procedure is critical to using the Erl
function to get truly meaningful information.
There are any number of free utilies on the net that will insert numeric line label in a procedure automatically, so you have fine-grained error information while developing and debugging, and then remove those labels once code goes live.
If your code displays error information to the end user if an unexpected error occurs, providing the value from Erl
in that information can make finding and fixing the problem VASTLY simpler than if value of Erl
is not reported.
I use a piece of code that i developed myself and it is pretty good for my codes:
In the beginning of the function or sub, I define:
On error Goto ErrorCatcher:
and then, I handle the 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
So you could 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 in custom exceptions. (e.g. ones that violate business rules) use the example above but use the goto to alter the flow of the method as necessary.
Source: Stackoverflow.com