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

<< VBA Debug Part 2 | VBA Error Handling PT2 >>


Leave a Reply