Microsoft Office Tutorials and References
In Depth Information
Trapping Runtime Errors
in any procedure that you write. As a rule, you want the On Error GoTo
label statement to execute early in the procedure. That way, no matter where
an error occurs in the procedure, execution passes to the error handler.
The label text can be any text at all, provided that it starts with a letter and
contains no blank spaces. Using the word Err and an underscore, followed
by the procedure name and a colon, is customary. (The colon is mandatory.)
Place the error-handling code at the bottom of the procedure, just before the
End function or End Sub statement. You also need to place an Exit Sub
statement before the error handler to prevent code execution from reaching
the error-handler code when no runtime error occurs.
Because you can’t anticipate every conceivable runtime error, having the
error handler display the error number and error description is best; that
way, at least you know what caused the error. The following example shows
an error message, where [main body of code] stands for all the code
that makes up the actual procedure:
On Error GoTo Err_myProcedure
[main body of code]
Exit Sub ‘Returns control to whomever called procedure.
‘Error handler starts below.
Msg = Err.Description & “ - “ & Err.Number
The following list details what happens when a runtime error occurs while
code in [main body of code] executes:
✦ On Error GoTo Err_MyProcedure: Because this statement tells VBA
to transfer execution to the Err_MyProcedure label, execution doesn’t
stop cold. Instead, execution continues at the first line after the Err_
✦ Msg = Err.Description & “ ‑ “ & Err.Number: This statement
creates a string of text that contains the description of the error and the
✦ MsgBox Msg: This statement displays the error message text and
number in a message box with an OK button. Code execution stops until
the user clicks the OK button in the message box.
✦ Resume Exit_MyProcedure: This statement causes execution to
resume at the first line after the Exit_MyProcedure label.
✦ Exit Sub: This statement causes the procedure to exit without any
further error messages.