How to Identify Specific Errors in Excel 2016 VBA - dummies

How to Identify Specific Errors in Excel 2016 VBA

By John Walkenbach

All VBA errors are not created equal. Some are serious, and some are less serious. Although you may ignore errors you consider to be inconsequential, you must deal with other, more serious errors. In some cases, you need to identify the specific error that occurs.

Every type of error has an official number. When an error occurs, Excel stores the error number in an Error object named Err. This object’s Number property contains the error number, and its Description property contains a description of the error. For example, the following statement displays the error number, a colon, and a description of the error:

MsgBox Err.Number & ": " & Err.Description

Keep in mind that Excel error messages are not always very useful — but you already know that.

The following procedure demonstrates how to determine which error occurred. In this case, you can safely ignore errors caused by trying to get the square root of a nonpositive number (that is, error 5) or errors caused by trying to get the square root of a nonnumeric value (error 13).

On the other hand, you need to inform the user if the worksheet is protected and the selection contains one or more locked cells. (Otherwise, the user may think the macro worked when it really didn’t.) Attempting to write to a locked cell in a protected worksheet causes error 1004.

Sub SelectionSqrt()
  Dim cell As Range
  Dim ErrMsg As String
  If TypeName(Selection) <> "Range" Then Exit Sub
  On Error GoTo ErrorHandler
  For Each cell In Selection
    cell.Value = Sqr(cell.Value)
  Next cell
  Exit Sub
  Select Case Err.Number
    Case 5 'Negative number
      Resume Next
    Case 13 'Type mismatch
      Resume Next
    Case 1004 'Locked cell, protected sheet
      MsgBox "Cell is locked. Try again.", vbCritical, cell.Address
      Exit Sub
    Case Else
      ErrMsg = Error(Err.Number)
      MsgBox "ERROR: " & ErrMsg, vbCritical, cell.Address
      Exit Sub
  End Select
End Sub

When a runtime error occurs, execution jumps to the code beginning at the ErrorHandler label. The Select Case structure tests for three common error numbers. If the error number is 5 or 13, execution resumes at the next statement. (In other words, the error is ignored.) But if the error number is 1004, the routine advises the user and then ends. The last case, a catch-all for unanticipated errors, traps all other errors and displays the actual error message.