How to Handle Various Errors and Use Error Handling in Event Driven Programming


  • About error handling
  • Types of errors
  • Types of error handling
  • Error handling references

About error handling

This chapter contains some of the most common error checking methods in Excel VBA. When working with VBA you should be aware of two broad classes of errors, programming errors and runtime errors. This chapter is about run-time errors. Error handling refers to the anticipation and resolution of programs. All application programs must deal with errors. Errors occur when execute unexpected conditions in programs. Error handling code must be added to each function call in application development. The ultimate goal of error handling is to write code that avoids displaying Excel's error message as much as possible. VBA includes several tools to help you identify errors and the handle them gracefully. When we use error handling in our application code it may look very professional. Error handling is one of the most important parts for application development. Well-written applications include error handling code that allows us to track the occurring unexpected errors.

Type Description
On Error Goto < label> After executing this statement program execution is redirected to the line label specified.
On Error Goto 0 After executing this statement any error handler within the current procedure is disabled.
On Error Resume next After executing this statement it tells VB to continue with the line of code following the line where the error occurred.

Normal flow of program execution

Figure 1: Program Execution

On Error Goto <label>

The "On Error Goto <label>" statement redirects program execution to the line label specified. The specified line label must be in the same procedure as the On Error statement otherwise it produces compile-time error. The following sample program defines how to use On Error Goto< label>l.

Option Explicit

Sub Error_handler()

  1. Const Procedure_Name = "Error_Handler".
  2. Dim N As Integer.
  3. Dim Msg As String.
  4. On Error GoTo Error_handler:
  5. N = 1 / 0.
  6. MsgBox "This line will not be executed".
  7. Exit Sub.
  8. Error_handler:
  9. Msg = "Error_in:" & Procedure_Name.
  10. Msg = Msg & vbNewLine & "Error_Line: " & Erl.
  11. Msg = Msg & vbNewLine & "Error_Number: " & Err. Number.
  12. Msg = Msg & vbNewLine & "Error_description: " & Err.Description.
  13. MsgBox Msg, vbInformation.

End Sub

Figure 2 shows the error message in a Message box.

Figure 2: Messege Box

On Error Goto 0

This is also called VBA default exception handling. After execution of this statement it disables any error trapping currently present in the procedure. Any error will cause VBA to display its standard error message box. The following sample program is a sample use of On Error Goto 0.

Option Explicit

Sub Error_handler ()

  1. Dim N As Integer
  2. Dim Msg As String
  3. On Error GoTo Error_handler:
  4. N = 1 / 0
  5. MsgBox "This line will not be executed"
  6. Exit Sub
  7. Error_handler:
  8. On Error GoTo 0

End Sub

On Error resume next

After executing this statement it tells VB to continue with the line of code following the line where the error occurred.

Sub Error_handler()

  1. Dim N As Integer
  2. Dim Answer As Integer
  3. N = 1
  4. On Error Resume Next
  5. Answer = N / 0
  6. MsgBox "Welcome to Csharpcorner !!!"

End Sub

Figure 3: Generate a Window

If an On error statement is not working, use the following procedure.

Press Alt+F11 to activate VBA.

On the Tools tab choose "Options...".

Figure 4: Activate VBA

Click the General tab of the Options dialog box.

Be sure that the Break on All Errors setting is de-selected.

Figure 5: All Errors

If this setting is selected, Excel essentially ignores any On Error statements. You want to keep the error trapping option set to break on unhandled error.

Figure 6: Break on unhandled Errors

Common Error codes in VBA

Error Number Error Description
3 It returns without Go Sub
5 It produces invalid procedure call
6 It Returns Overflow error alter
7 It shows Out of memory message
9 Its shows subscript out of range
10 Declare same name, duplicate definitions
11 Division by zero
13 Type mismatching errors
14 Out of string space
55 Its return bad file mode
53 Its return file not found
58 File already exits
76 Path not found
336 ActiveX component not correctly registered
337 ActiveX component not found
338 ActiveX component did not correctly run
1000 Occur when does not have name property
1001 Class name does not have method name
1002 Missing required argument
1003 Invalid number of arguments
1004 Method name or class name failed
1006 Unable to get the property name
31032 Unable to create embedded object


I hope this article is useful for Excel VBA beginners. Thanks for reading.