Basics of VBA / Macros Chapter – 36 (Macro Errors Part‑4)

⚡ Macro Developer Toolkit

Copy or Export this Code to VBA

Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

🖇 View Code Example
⏱ 2 min readUpdated 12 November 2026

Err Object

If you are not familiar with Error Handling yet, we recommend you to read example first. When an error in Excel VBA occurs, the properties of the Err object are filled with information.

Situation:

The program below calculates the square root of numbers.

Err Object in Excel VBA

Place a command button on your worksheet and add the following code lines:

Dim rng As Range, cell As Range
 Set rng = Selection

For Each cell In rng
    On Error GoTo InvalidValue:
    cell.Value = Sqr(cell.Value)
Next cell

Exit Sub

Advertisement

InvalidValue:

MsgBox Err.Number & ” ” & Err.Description & ” at cell ” & cell.Address

Resume Next

Result when you select Range(“B2:B6”) and click the command button on the sheet:

Err Object Result

Err Object Result

When an error occurs, the number property of the Err object is filled with an unique error number of the current error and the Description property is filled with the error description of the current error.

These descriptions may not be very helpful to the users of your program. You can make the error messages more user friendly by modifying the last part of the macro as follows:

Advertisement
InvalidValue:

Select Case Err.Number
    Case Is = 5
        MsgBox “Can’t calculate square root of negative number at cell ” & cell.Address
    Case Is = 13
        MsgBox “Can’t calculate square root of text at cell ” & cell.Address
End Select

Resume Next

Go through our Select Case program to learn more about the Select Case structure.

Result:

Friendly Message

Friendly Message

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong