How to Handle Errors in Excel 2016 VBA
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 BadEntry: 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.
On Error not working?
If an On Error statement isn’t working as advertised, you need to change one of your settings:
Activate the VBE.
Choose the Tools → Options command.
Click the General tab of the Options dialog box.
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.