How to Use the OnError Statement in Excel 2016 VBA

By John Walkenbach

When you need to deal with errors in Excel VBA, you can use the OnError statement. However, there are a few things you need to know first. You can use the On Error statement in three ways.

Syntax What It Does
On Error GoTo label After executing this statement, VBA resumes execution at the
statement following the specified label. You must include a colon
after the label so that VBA recognizes it as a label.
On Error GoTo 0 After executing this statement, VBA resumes its normal
error-checking behavior. Use this statement after using one of the
other On Error statements or when you want to remove error handling
in your procedure.
On Error Resume Next After executing this statement, VBA simply ignores all errors
and resumes execution with the next statement.

Resuming after an error

In some cases, you simply want the routine to end gracefully when an error occurs. For example, you may display a message describing the error and then exit the procedure. (The EnterSquareRoot5 example shown earlier uses this technique.) In other cases, you want to recover from the error, if possible.

To recover from an error, you must use a Resume statement. This clears the error condition and lets you continue execution at some location. You can use the Resume statement in three ways.

Syntax What It Does
Resume Execution resumes with the statement that caused the error. Use
this if your error-handling code corrects the problem and
it’s okay to continue.
Resume Next Execution resumes with the statement immediately following the
statement that caused the error. This essentially ignores the
error.
Resume label Execution resumes at the label you specify.

The following example uses a Resume statement after an error occurs:

Sub EnterSquareRoot6()
  Dim Num As Variant
  Dim Msg As String
  Dim Ans As Integer
TryAgain:
‘  Set up error handling
  On Error GoTo BadEntry
‘  Prompt for a value
  Num = InputBox(“Enter a value”)
  If Num = ““ Then Exit Sub
‘  Insert the square root
  ActiveCell.Value = Sqr(Num)
  Exit Sub
BadEntry:
  Msg = Err.Number & “: “ & Error(Err.Number)
  Msg = Msg & vbNewLine & vbNewLine
  Msg = Msg & “Make sure a range is selected, “
  Msg = Msg & “the sheet is not protected, “
  Msg = Msg & “and you enter a nonnegative value.”
  Msg = Msg & vbNewLine & vbNewLine & “Try again?”
  Ans = MsgBox(Msg, vbYesNo + vbCritical)
  If Ans = vbYes Then Resume TryAgain
End Sub

This procedure has another label: TryAgain. If an error occurs, execution continues at the BadEntry label, and the code displays the message below. If the user responds by clicking Yes, the Resume statement kicks in, and execution jumps back to the TryAgain label. If the user clicks No, the procedure ends.

If an error occurs, the user can decide whether to try again.

If an error occurs, the user can decide whether to try again.

Notice that the error message also includes the error number, along with the “official” error description.

The Resume statement clears the error condition before continuing. To see this, try substituting the following statement for the second-to-last statement in the preceding example:

If Ans = vbYes Then GoTo TryAgain

The code doesn’t work correctly if you use GoTo rather than Resume. To demonstrate, enter a negative number. You get the error prompt. Click Yes to try again and then enter another negative number. This second error is not trapped because the original error condition was not cleared.

Error handling in a nutshell

To help you keep all this error-handling business straight, here’s a quick-and-dirty summary. A block of error-handling code has the following characteristics:

  • It begins immediately after the label specified in the On Error statement.

  • It should be reached by your macro only if an error occurs. This means that you must use a statement such as Exit Sub or Exit Function immediately before the label.

  • It may require a Resume statement. If you choose not to abort the procedure when an error occurs, you must execute a Resume statement before returning to the main code.

Knowing when to ignore errors

In some cases, it’s perfectly okay to ignore errors. That’s when the On Error Resume Next statement comes into play.

The following example loops through each cell in the selected range and converts the value to its square root. This procedure generates an error message if any cell in the selection contains a negative number or text:

Sub SelectionSqrt()
  Dim cell As Range
  If TypeName(Selection) <> “Range” Then Exit Sub
  For Each cell In Selection
    cell.Value = Sqr(cell.Value)
  Next cell
End Sub

In this case, you may want to simply skip any cell that contains a value you can’t convert to a square root. You can create all sorts of error-checking capabilities by using If-Then structures, but you can devise a better (and simpler) solution by simply ignoring the errors that occur.

The following routine accomplishes this by using the On Error Resume Next statement:

Sub SelectionSqrt()
  Dim cell As Range
  If TypeName(Selection) <> “Range” Then Exit Sub
  On Error Resume Next
  For Each cell In Selection
    cell.Value = Sqr(cell.Value)
  Next cell
End Sub

In general, you can use an On Error Resume Next statement if you consider the errors to be harmless or inconsequential to your task.