Error-handling has always been a bother. I've experimented with various techniques. Here's my solution.
This approach brings together my preferred methods on this SO page, plus a few of my own techniques.
The question asker mentions only the simple case -- a single procedure. I also cover sub-procedures, custom errors, logging, error-related processing, and other error-related topics.
No error-handling
The simplest case: Don't assume you always need handling. Procedures which are never going to error out don't need error-handling.
Ignored Errors
It's acceptable to simply ignore some errors. This is perfectly acceptable example of an ignored error, because you know there's no other error that can reasonably occur on that statement.
...
On Error Resume Next
Set bkCars = Workbooks("Cars.xlsx")
On Error GoTo 0
If (bkCars Is Nothing) Then MsgBox "Cars workbook isn't open."
Set bkCars = Workbooks("Wheelbarrows.xlsx")
...
I've never heard of any other error ever happening on that statement. Use your judgement. Ignore extremists. VBA is supposed to be easy. On Error Resume Next isn't "the Devil incarnate"; it's one way to implement Try..Catch in VBA. For more examples, see Jordi's answer.
Unhandled Errors
The remainder of this answer is about unhandled errors. An unhandled error is an unexpected error which breaks your program.
Handler
Here's my basic handler:
Sub Main()
On Error GoTo HANDLER
Dim x As Long
x = "hi"
HANDLER:
' cleanup
x = 0
' error-handler
If (Err = 0) Then Exit Sub
MsgBox Error
End Sub
- Flow-through: Inspired by @NickD and others here, it completely eliminates "Exit Sub" and "Resume" from your code. Code flows in one direction, instead of jumping around. There's a single exit point in the procedure. All are important, if you like less typing, less code, and less spaghetti.
- *Cleanup: This approach ensures the same cleanup code runs whether there is or isn't an error. Error and non-error conditions share the same cleanup code. Straightforward pattern handles a wide variety of scenarios regarding cleanup and custom-handling.
Convenience
Make your life easier.
Function IsEr() As Boolean
IsEr = (Err <> 0)
' or IsEr = CBool(Err)
End Function
Special Handlers
The basic style can handle more complex cases. For example, you can insert handling or cleanup for specific errors.
...
HANDLER:
If Not IsEr Then Exit Sub
If (Err = 11) Then Call_TheBatphone
MsgBox Error
End Sub
Procedure Calls, No Cleanup
A called procedure which doesn't have any special cleanup code doesn't need any error-code. It's errors, and those of it's sub-procedures, will automatically bubble up to the entry-procedure. You can have cleanup-code at each sub.
Sub Main()
On Error GoTo HANDLER
Sub_1
HANDLER:
If Not IsEr Then Exit Sub
MsgBox Error
End Sub
Sub Sub_1()
Dim x
x = 5/0 <.. will jump to Main HANDLER
End Sub
Procedure Calls, Cleanup
However, a sub-procedure which must always run cleanup-code (even in case of an error) needs a bit of extra help. The sub's error-handler resets the error-event, so the error must be retriggered with Err.Raise.
This means your handler for subs must be different than the handler for the kickoff-procedure (aka "entry-point", meaning the first procedure that runs at the beginning of the roundtrip code-loop).
Sub-handlers shouldn't show any message boxes or do any logging -- that should remain with the Main handler. Sub handlers should only be used for special cleanup, special processing, and to append extra error-info to the error object.
Sub Main()
On Error GoTo HANDLER
Sub_1
HANDLER:
If Not IsEr Then Exit Sub
MsgBox Error
End Sub
Sub Sub_1()
On Error GoTo HANDLER
Dim x
x = 5/0
' More processing here
HANDLER:
If Not IsEr Then Exit Sub
Err.Raise Err.Number, Err.Source, Err.Description & vbNewline & "Some problem with divisor"
End Sub
Run
Beware: any procedure executed with the Run statement requires special handling. If you raise an error within the procedure, the error will not bubble up to the entry procedure, and whatever into the Raise puts into the Err will be lost when execution returns to the caller. Therefore, you need to create a workaround. My workaround is to put Err.Number into a global variable, and then on return from the Run, check that variable.
Public lErr As Long
Sub Main()
On Error GoTo HANDLER
Run "Sub_1"
If (lErr <> 0) then Err.Raise lErr
Dim x
x = 5
HANDLER:
If Not IsEr Then Exit Sub
MsgBox Error
End Sub
Sub Sub_1()
On Error Goto HANDLER
Dim x
' will NOT jump to Main HANDLER, since Run
x = 5/0
HANDLER:
If (Err <> 0) Then lErr = Err
End Sub
Alerts
If your intention is produce professional code, then you must communicate all unexpected errors appropriately to the user, as shown above. You never want users to see a "Debug" button or find themselves dropped into VBA.
Centralized Handling
The next evolution is centralized handling. This gives you a really quick and easy way to replicate your perfect error-handling everywhere. As mentioned by @igorsp7, centralized handling makes it simpler and easier to implement consistent, reliable error-handling everywhere. It makes it easy to reuse complex handler logic. It is so easy and simple to just place ErrorHandler at the bottom of every procedure. Reminder: Err is a global object, so there's no need to pass it around as an argument.
Sub Main()
On Error GoTo HANDLER
Sub_1
HANDLER:
MainCleanup
ErrorHandler_Entry
End Sub
Sub Sub_1()
On Error GoTo HANDLER
Dim x
x = 5/0
HANDLER:
SubCleanup
ErrorHandler_Sub
End Sub
Sub ErrorHandler_Entry()
If Not IsEr Then Exit Sub
' log error to a file for developer to inspect.
Log_Error_To_File
' Then alert user. InputBox provides simple way to let users copy with mouse
InputBox "Sorry, something went haywire. Please inform the developer or owner of this application.", _
"Robot Not Working", Err.Number & vbNewLine & Err.Source & vbNewLine & Err.Description
End Sub
Private Sub ErrorHandler_Sub()
If Not IsEr Then Exit Sub
' bubble up the error to the next caller
Err.Raise Err.Number, Err.Source, Err.Description
End Sub
Custom Errors
Numbering
Use = vbObjectError + 514 for your first one, as 1 to 513 are reserved for native VB errors. I'm still researching custom error numbering. There's a lot of conflicting information. It may be simply
- Native errors are positive integers, to 65535?
- Custom errors are negative integers, 0 to -2,000,000,000?
But I don't know yet if that's correct! Your error handlers will work even if you use native error numbers. However, if your error handling is based on whether it's a native vs custom error, or if your application is reporting the error to a developer, then to avoid confusion or more bugs, the best practice is to not reuse native numbers.
Syntax
Enum CustomError
UserPause = vbObjectError + 514
UserTerminate
End Enum
Function CustomErr()as Boolean
CustomErr = (Err >= 514)
End Function
Sub Test
On Error Goto HANDLER
Err.Raise CustomError.UserPause
HANDLER:
Cleanup
If CustomErr Then Handle_CustomError
End Sub
Sub Handle_CustomError()
Select Case Err
Case UserPause
MsgBox "Paused"
Resume Next
Case UserTerminate
SpecialProcessing
MsgBox "Terminated"
End
End Select
End Sub
Error Categories:
You may want custom errors in an addin, an application workbook, and a data workbook. You should reserve a range of allowed error numbers for each type. Then your handlers can determine the source of the error by its number. This enum uses the starting number for each range.
Enum AppError
UserPause = vbObjectError + 514
UserTerminate
End Enum
Enum AddinError
LoadFail = vbObjectError + 1000
End Enum
Enum DataError
DatabaseLocked = vbObjectError + 1500
End Enum
Enum ErrorType
VB
App
Addin
Data
End Enum
Function Get_ErrorCategory() As ErrorType
If (Err < 514) Then
Get_ErrorCategory = VB
ElseIf (Err <= 1000) Then
Get_ErrorCategory = App
ElseIf (Err <= 1500) Then
Get_ErrorCategory = Addin
Else
Get_ErrorCategory = Data
End If
End Function
Sub ErrorHandler_Entry(Optional sInfo As String)
If Not IsEr Then Exit Sub
Select Case Get_ErrorCategory
Case VB
InputBox "Sorry, something went haywire. Please inform the developer or owner of this application.", _
"Robot Not Working", Err.Number & vbNewLine & Err.Source & vbNewLine & Err.Description & vbNewLine & sInfo
Case Addin
Log_Error_To_File
Case Data
' do nothing
End Select
End Sub
Developer Mode
As developer, you'll want to debug unhandled errors, instead of getting friendly messages. So you want to temporarily disable your handler when you're in development. That's conveniently done by manually setting a "Debug" state someplace. There are a couple of ways to do it:
Custom "ExecMode":
Get_DebugMode is a function that you need to write, which pulls your Debug mode from wherever you stored it. Can be stored in an Excel defined-name, a module constant, a worksheet cell -- whatever you prefer.
...
If Not Get_DebugMode Then _
On Error GoTo HANDLER
...
Conditional Compilation Arguments:
This needs to be applied in the VB IDE.
...
#If Not DEBUGMODE Then _
On Error GoTo HANDLER
...
Changing code behavior at compile time
