Error Handling

No matter how carefully you write VBA code, there is always the possibility that an error will occur for a variety or reasons. It is essential that you include error handling and trapping so that the user doesn't just get a ugly generic error message.

The basic structure for adding error handling in you VBA code is a follows

Private Example()

  'Specify what happens when an error occurs
  On Error GoTo ErrMsg:

'Exit the function here.
ExitHere:
  'Place any code you always want to run before exiting the code, _
   e.g. set cursor to default cursor, close VBA objects

  'Finally exit the subroutine (or function)
  Exit Sub

'Error handling code
ErrMsg:
  'Handle Errors
  MsgBox "Uh-oh! An error occured!" & vbNewLine & vbNewLine & _
         "Error Number: " & Err.Number & vbNewLine & _
         "Error Description: " & Err.Description, vbCritical
  'Resume code execution @ ExitCode label
  Resume ExitHere:

End Sub

On Error Statement

On Error GoTo ErrMsg:

This statement will cause you code to go to skip the next code line when an error occurs and execute the code at a line label, in this case it will go to 'ErrMsg'.

There a few options for the On Error statement.

You can also insert On Error statements throughout the code

Sub Example()
    On Error GoTo ErrMsg:

    'Microsoft Access VBA code...

    On Error Resume Next
    x = y / 0
    If Err.Number <> 0 then
        MsgBox "An error occured"
	      Exit Sub
    End If
    'Set error to go to 'ErrMsg' again
    On Error GoTo ErrMsg:

    'More Microsoft Access VBA code...

End Sub

Error Handler

This is the code section that is executed when an error occurs that the 'On Error' statement points to. As discussed above, you can handle errors in code (or continue executing code) depending on how you decide errors should be handled.

For the purposes of this discussion, we will assume that when an error occurs and our code execution goes to 'ErrMsg'. Here we may want to give a message to the user the let them know that a message has occurred and some information as to what error occurred. We may also want to handle very specific errors by using a Select Case statement.

ErrMsg:
    'Handle Errors
    Select Case Err.Number
    Case 6 'Overflow
        Msgbox "A calculated number is too large."
    Case 7 'Out of Memory
        Msgbox "Not enough memory."
    Case 11 'Division by zero
        Msgbox "A number was divided by 0."
    Case 53 'File not found
        Msgbox "File was not found."
	      Resume SelFile 'Go back to code asking user to select file
    Case Else 'All other errors
        MsgBox "Uh-oh! An error occured!" & vbNewLine & vbNewLine & _
               "Error Number: " & Err.Number & vbNewLine & _
               "Error Description: " & Err.Description, vbCritical
    End Select

After we have handled the error, we either need to resume code execution at a particular line number or line label. If we want to exit the procedure (sub or function), we will have code section that contains code that should always run before exiting the procedure. At the very least, there must be an Exit Sub/Function statement. Without one, the code would continue to the error handler even when an error does not occur (No Error, i.e. Err.number = 0).

ExitHere:
    Exit Sub

In most cases it may be sufficient to simply exit the procedure. But you there may be other things that need to occur, e.g, the following code may have been executing prior to the error;

'MS Access example
DoCmd.SetWarnings False
DoCmd.Hourglass True
Dim myDB as DAO.Database
Set myDB = CurrentDB

'MS Excel example
With Appliction
	.Screenupdating = False
	.Cursor = xlWait
End with

If so, then you will need to add the following the 'ExitHere' section;

'MS Access example
ExitHere:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    myDB.Close
    Exit Sub

'MS Excel example
With Appliction
	.Screenupdating = True
	.Cursor = xlDefault
End with