Microsoft Office Tutorials and References
In Depth Information
7.5 Handling Errors in Code
7.5 Handling Errors in Code
We discussed the various types of errors in Chapter 3 , but we have scrupulously avoided the
question of how to handle run-time errors in code. Indeed, VBA provides several tools for
handling errors ( On Error , Resume , the Err object, and so on), and we could include an entire
chapter on the subject in this topic.
Proper error handling is extremely important. Indeed, if you are, or intend to become, a
professional application developer, you should familiarize yourself with error-handling procedures.
On the other hand, if your intention is to produce Excel VBA code for your own personal use, the
reasons for adding error-handling routines are somewhat mitigated. When an error occurs within
one of your own programs, VBA will stop execution, display an error message, and highlight the
offending code. This should enable you to debug the application and fix the problem. (It would be
unreasonable to expect another user of your program to debug your code, however.)
Let us undertake a brief discussion of the highlights of error handling. (For more details, may I
suggest my book Concepts of Object-Oriented Programming in Visual Basic , published by
Springer-Verlag. It has a detailed chapter on error handling.)
7.5.1 The On Error Goto Label Statement
The On Error statement tells VBA what to do when a run-time error occurs. The most common
form of the statement is:
On Error GoTo label
where label is a label. For instance, consider the following code:
Sub example()
On Error GoTo ERR_EXAMPLE
MsgBox Selection.Cells.Count
Exit Sub
ERR_EXAMPLE:
MsgBox Err.Description, vbCritical
Exit Sub
End Sub
The purpose of this procedure is simply to display the number of cells in the current selection.
When the current selection is a worksheet range, the Cells property returns the collection of cells
in the selection and the Count property then returns the number of cells.
However, if the current selection is not a worksheet range (it might be a drawing object or a chart,
for instance), then the Cells property fails. To deal with this possibility in a friendly manner, we
add some error checking. The line:
On Error GoTo ERR_EXAMPLE
tells VBA to move execution to the label ERR_EXAMPLE if an error occurs. The code following
this label is called the error-handling code . If an error should occur, the next line executed is the
 
Search JabSto ::




Custom Search