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

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()

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

Conclusion

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

HAVE A NICE DAY