VBA Error Handling PT1
In this VBA Error Handling set (1 & 2) we’ll talk about:
Trapping run-time errors
Creating error handlers
Using the error object
There are 3 main types of VBA Errors:
1. Syntax Errors
2. Logic Errors
3. Run-Time Errors
Syntax errors result when the logic is misspelled, or a wrong keyword is used. The VBA compiler doesn’t know what you are referring to so an error results.
Logic Errors happen when the code is constucted poorly, so it does not perform correctly.
Run Time Errors result when an operation called upon is impossible to carry out, or, when a referenced object in the code does not exist.
The impact of these run time errors can be minimized by error-handling routines.
Some common types of run-time errors are:
-Overflow errors
-Division by zero errors
-Type mismatch errors
-Invalid use of the null character
When a run time error does occur, you can:
-Exit the application
-View the Debug window, at the line the caused the error, because the error line is highlighted, and you can get a clue about what caused the error.
-Go directly to the line that caused the error and try the line again.
-Skip past the error to resume execution of the procedure.
-Get Help on the error (press the F1 keyboard key)
You have to trap these run time errors, or else unexpected results will occur and a cryptic, default error box will result for the user.
Watch the video:
'********************************** '1. SAMPLE ERROR-HANDLING ROUTINE: '********************************** Public Sub AnyProcedure() 'Set Error Trap: On Error GoTo errHandler 'Does not cause error: 'x = 0 / 1 'Causes error: x = 1 / 0 MsgBox "x= " & x 'End of Main Procedure Code: Exit Sub 'Error Handling Routine: errorHandler: 'Code To Handle The Error MsgBox "error" 'End Of Error Handler Exit Sub End Sub
‘*************************************
‘2. EXITING AN ERROR-HANDLING ROUTINE:
‘*************************************
Here is a chart referencing the “Resume” command’s properties for you to reference:
Statement | Description |
---|---|
Resume | Resumes execution at the statement that caused the error, and the statement is run again. |
Resume Next | Execution is resumed at the line after that which caused the error. |
Resume (line) (ex. Resume 11) | Code execution is resumed at the line specified in the procedure containing the error handler, in the “line” argument. |
Resume (label) (ex. Resume ExitHere) | Code execution is resumed at the line specified in the procedure containing the error handler, in the “label” argument. |
…CODE EXAMPLE
Public Sub OpenForm() Dim strForm As String 'Set Error Trap On Error GoTo errHandler 'Get The Form Name strForm = InputBox("Enter the form name.") 'Open the form using the form name entered: DoCmd.OpenForm strForm Exit Sub errHandler: 'An error happens when there is no form called as what was entered. MsgBox "Invalid form name. Try again." 'Get the Report name again to use with the DoCmd object strForm = InputBox("Enter the form name.") 'Try the DoCmd command again with the new form name. Resume End Sub