Excel Common Error Handler
The VBA code illustrates a common error handler technique. Keep in mind there are three forms of the On Error Statement. All three of these clear any previous error codes (Err.Number and Err.Description):
(1) On Error GoTo Line (A User Defined Error Handling Routine)
(2) On Error GoTo 0 (Turns Off User Error Handling)
(3) On Error Resume Next (Ignores the Error And Executes The Next Statement)
The code examples also illustrate the following:
(1) Err.Clear (Clear Error Codes But Leave Any User Error Handlers Still Active)
(2) Resume (Clear Error Codes But Leave Any User Error Handlers Still Active)
For a list of all error codes, see VBA Error Codes.
Program Code
Option Explicit
Sub TestError()
' ************************************************************
' This Creates An Error With Divide By Zero
' ************************************************************
' ************************************************************
' Demonstrate The Following:
' (1) On Error GoTo ErrorHandler (Reset Previous Err.Number)
' (2) On Error GoTo ErrorHandler (Create User Error Handling)
' (3) On Error GoTo 0 (Removes User Error Handling)
' (4) On Error GoTo 0 (Also Resets Err.Number to 0)
' (5) Resume (Resets The Err.Number and Err.Description)
' (6) Resume (Leaves User Error Handling Intact)
' (7) Proper User of Err.Number and Err.Description
' ************************************************************
Dim intDividend As Integer
Dim intDivisor As Integer
Dim intResult As Integer
Dim varErrorReturn As Variant
intDivisor = 0
intDividend = 20
' ************************************************************
' Reset Any Previous Error Codes And Set User Error Handling
' ************************************************************
On Error GoTo ErrorHandler
intResult = intDividend / intDivisor
Exit_Procedure:
' ************************************************************
' On Error GoTo 0 Removes Any User Error Trapping
' Also Resets The Err.Number and Err.Description
' ************************************************************
On Error GoTo 0
Exit Sub
ErrorHandler:
Call CommonErrorHandler(Err.Number, Err.Description)
' ************************************************************
' The Resume Command Resets The Error Code to 0
' But It Leaves The "On Error GoTo ErrorHandler Intact
' ************************************************************
Resume Exit_Procedure
End Sub
Sub TestError1()
' ************************************************************
' This Create An Error With Divide By Zero
' ************************************************************
' ************************************************************
' Demonstrate The Following:
' (1) On Error Resume Next (Reset Previous Error Codes)
' (2) On Error Resume Next (Keep Processing Even If Errors)
' (3) How To Test For A Specific Err.Number
' (4) Err.Clear (Clear Error Code and Description)
' (5) Err.Clear (Leave Any User Error Trapping Intact)
' (6) On Error GoTo 0 (Removes User Error Handling)
' (7) On Error GoTo 0 (Also Resets Err.Number to 0)
' ************************************************************
Dim intDividend As Integer
Dim intDivisor As Integer
Dim intResult As Integer
Dim varErrorReturn As Variant
intDivisor = 0
intDividend = 20
intResult = 5
' ************************************************************
' The "On Error Resume Next" Clears Any Previous Error Codes
' Then Instructs Excel To Continue Processing The Next
' Statement Even If An Error Occurs. If an Error Occurs,
' Keep Any Err.Code And Err.Description So The Values Can
' Be Tested
' ************************************************************
On Error Resume Next
intResult = intDividend / intDivisor
If Err.Number = 11 Then
intResult = 10
' ************************************************************
' The Err.Clear Command Resets The Error Code to 0
' But It Leaves the "On Error Resume Next" Intact
' ************************************************************
Err.Clear
Exit Sub
End If
' ************************************************************
' On Error GoTo 0 Removes Any User Error Trapping
' Also Resets The Err.Number and Err.Description
' ************************************************************
On Error GoTo 0
Exit Sub
End Sub
' ************************************************************
' The Common Error Display Routine
' ************************************************************
Public Sub CommonErrorHandler(ErrorNumber As String, ErrorDescription As String)
Dim varMessageReturn As Variant
varMessageReturn = MsgBox("An Error Occurred In This Application" & vbCrLf & _
"Please Contact The Developer" & vbCrLf & vbCrLf & _
"Error Number = " & ErrorNumber & " Error Description = " & _
ErrorDescription, vbCritical)
End Sub
Option Explicit
Public NoRadius As Variant
Public NotANumber As Variant
Dim Radius As Variant
' ************************************************************
' User Defined Error Codes
' This feature is rarely used, but here is an example of
' how to create user defined error codes and test for them.
' ************************************************************
Sub AreaOfCircle()
' ************************************************************
' Note That Radius Will Be An Error Type or a String Type
' Depending On The User Input
' ************************************************************
Const PI = 3.142
NoRadius = CVErr(2010)
NotANumber = CVErr(2020)
Radius = CheckData(InputBox("Enter the radius: "))
If IsError(Radius) Then
Select Case Radius
Case NoRadius
MsgBox "Error: No radius given."
Case NotANumber
MsgBox "Error: Radius is not a number."
Case Else
MsgBox "Unknown Error."
End Select
Else
' ****************************************************
' Note That Excel Auto-Converts The String Radius To
' A Double
' ****************************************************
MsgBox "The area of the circle is " & (PI * Radius ^ 2)
End If
End Sub
Function CheckData(TheRadius)
If Not IsNumeric(TheRadius) Then
CheckData = NotANumber
ElseIf TheRadius = 0 Then
CheckData = NoRadius
Else
CheckData = TheRadius
End If
End Function