Content
- 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()
- Const Procedure_Name = "Error_Handler".
- Dim N As Integer.
- Dim Msg As String.
- On Error GoTo Error_handler:
- N = 1 / 0.
- MsgBox "This line will not be executed".
- Exit Sub.
- Error_handler:
- Msg = "Error_in:" & Procedure_Name.
- Msg = Msg & vbNewLine & "Error_Line: " & Erl.
- Msg = Msg & vbNewLine & "Error_Number: " & Err. Number.
- Msg = Msg & vbNewLine & "Error_description: " & Err.Description.
- 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 ()
- Dim N As Integer
- Dim Msg As String
- On Error GoTo Error_handler:
- N = 1 / 0
- MsgBox "This line will not be executed"
- Exit Sub
- Error_handler:
- 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()
- Dim N As Integer
- Dim Answer As Integer
- N = 1
- On Error Resume Next
- Answer = N / 0
- 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 |
Conclusion
I hope this article is useful for Excel VBA beginners. Thanks for reading.
HAVE A NICE DAY