By John Walkenbach

How can you identify and handle every possible error in Excel 2016? Often, you can’t. Fortunately, VBA provides another way to deal with those pesky errors.

Revisiting the EnterSquareRoot procedure

Examine the following code. The routine uses an all-purpose On Error statement to trap all errors and then checking to see whether the InputBox was canceled.

Sub EnterSquareRoot5()
  Dim Num As Variant
  Dim Msg As String
‘  Set up error handling
  On Error GoTo BadEntry
‘  Prompt for a value
  Num = InputBox(“Enter a value”)
‘  Exit if cancelled
  If Num = ““ Then Exit Sub
‘  Insert the square root
  ActiveCell.Value = Sqr(Num)
  Exit Sub
  Msg = “An error occurred.” & 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.”
  MsgBox Msg, vbCritical
End Sub

This routine traps any type of runtime error. After trapping a runtime error, the revised EnterSquareRoot procedure displays this message box. This message box describes the most likely causes of the error.

A runtime error in the procedure generates this semihelpful error message.

A runtime error in the procedure generates this semihelpful error message.

On Error not working?

If an On Error statement isn’t working as advertised, you need to change one of your settings:

  1. Activate the VBE.

  2. Choose the Tools  →  Options command.

  3. Click the General tab of the Options dialog box.

  4. Make sure that the Break on All Errors setting is deselected.

    If this setting is selected, Excel essentially ignores any On Error statements. You normally want to keep the Error Trapping options set to Break on Unhandled Errors.

About the On Error statement

Using an On Error statement in your VBA code lets you bypass Excel’s built-in error handling and use your own error-handling code. In the previous example, a runtime error causes macro execution to jump to the statement labeled BadEntry. As a result, you avoid Excel’s unfriendly error messages, and you can display your own message to the user.

Notice that the example uses an Exit Sub statement right before the BadEntry label. This statement is necessary because you don’t want to execute the error-handling code if an error does not occur.