Excel VBA IsError: Check for Worksheet Errors in Code

In Excel VBA, IsError(expression) returns True when an expression evaluates to an error value and False otherwise. Use it to check a cell’s .Value or .Value2 before your macro tries to process that value.

What IsError Does in VBA

IsError is a built-in VBA function that tests whether its argument is an error value. It returns a Boolean result: True or False. In Excel automation, its most common use is checking a cell whose formula might return an error, such as #N/A or #DIV/0!, before using the cell’s result in another calculation.

A key distinction is that IsError detects an error value; it does not explain or fix the error. If it returns True, your code can skip the value, flag the cell, substitute a result, or take another action that suits the workbook. If it returns False, the value is not an error value, but it may still be blank, text, or otherwise unsuitable for your next operation.

For example, a formula in A1 may currently display #N/A. Reading the cell into a Variant and passing that variable to IsError lets VBA check the underlying value rather than relying on the text shown in the worksheet.

Checking a Cell Value

In VBA, a cell’s .Value or .Value2 property can return a Variant containing an Excel error value. Declaring the receiving variable as Variant is useful because it can hold different kinds of values, including error values. This example checks A1 on a worksheet named Sheet1:

Sub CheckA1ForError()
    Dim cellValue As Variant

    ' Read the cell result, which may be an error value.
    cellValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value2

    If IsError(cellValue) Then
        Debug.Print True
    Else
        Debug.Print False
    End If
End Sub

In the Visual Basic Editor, Debug.Print sends the result to the Immediate window. The macro prints True if A1 contains an error value and False if it does not. The example assumes that Sheet1 exists in the workbook containing the code. If the sheet name is wrong, that can cause a separate runtime error; IsError does not protect against an invalid worksheet reference.

You can open the Visual Basic Editor with Alt+F11 on Windows. On a Mac, use Option+F11; depending on the keyboard, you may also need to hold Fn. The Developer tab’s Visual Basic command is another route. Before running a macro that changes workbook contents, save a copy: VBA actions generally cannot be reversed with Ctrl+Z (or Command+Z on Mac).

Which Excel Errors It Detects

IsError checks for error values rather than a single named error. That includes familiar worksheet results such as #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, and #NULL!. Excel also has newer error types, including errors that can appear with dynamic arrays or newer calculation features. You usually do not need a separate VBA test for each displayed error: if the cell contains an error value, IsError returns True.

The displayed text is not the same thing as the error value. A cell containing the literal text #N/A is text, not the Excel #N/A error, so IsError returns False for that text. This matters when imported data contains strings that look like worksheet errors. To distinguish the two, check the actual cell value with IsError instead of searching its formatted display text.

Handling Errors in a Range

A practical use is scanning a range and recording which cells contain errors. The following macro writes a Boolean result in the cell to the right of each value in A1:A20: True means the corresponding source cell contains an error; False means it does not. Adjust the sheet and range to fit your workbook.

Sub FlagErrorsInRange()
    Dim cell As Range

    ' Mark each source cell with a Boolean result in the next column.
    For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A1:A20").Cells
        cell.Offset(0, 1).Value = IsError(cell.Value2)
    Next cell
End Sub

This approach preserves the original values and puts the check in column B. However, it overwrites anything already in B1:B20, so verify that the destination cells are available before running it. If you instead want to skip error cells while processing a range, put the processing step in the Else branch of an If IsError(cell.Value2) Then test.

For a single cell, a direct check is also valid: If IsError(Range("A1").Value2) Then. Qualifying the range with a worksheet, as in the examples, is safer than using an unqualified Range, because an unqualified reference depends on which sheet is active.

IsError Versus Worksheet Error Functions

Excel has a worksheet function called ISERROR, and VBA has the built-in function IsError. They address a similar question but are used in different places. In a worksheet cell, enter =ISERROR(A1) to display TRUE when A1 contains an error and FALSE otherwise. In a VBA procedure, call IsError and use its Boolean result in your code.

Other worksheet functions answer narrower questions. ISNA tests specifically for the #N/A error. ISERR tests for errors other than #N/A. The worksheet function IFERROR returns a chosen alternative when its first argument is any error, while IFNA substitutes a result specifically for #N/A. Those worksheet functions are not interchangeable with VBA’s built-in IsError: choose based on whether you need a worksheet formula or a decision inside a macro.

IsError Is Not VBA Error Handling

A common misunderstanding is that IsError catches every error that can interrupt a macro. It does not. It checks a value that is already an error value, such as a formula result stored in a cell. It does not handle runtime problems such as referencing a missing worksheet, trying to open an unavailable file, or performing an invalid operation in code.

VBA runtime error handling is a separate mechanism, commonly involving an On Error statement and a suitable recovery path. Use IsError when you need to inspect data. Use runtime error handling when your code must respond to an operation that may fail. Adding On Error Resume Next is not a substitute for checking cell values: it can suppress errors and allow a procedure to continue in an unintended state if used carelessly.

For example, if A1 contains #DIV/0!, reading its value and checking it with IsError is a data-validation task. If the code refers to a worksheet that does not exist, that is a VBA runtime problem. The two situations may both involve the word “error,” but they require different checks.

Common Mistakes and Useful Checks

Checking a displayed string instead of the value: IsError detects an error value, not ordinary text that happens to read #N/A.
Assuming False means valid data: a blank cell, a text label, or an unexpected number can all produce False. Add checks such as IsEmpty or IsNumeric when your task needs them.
Using the wrong object reference: qualify Range with the intended worksheet and workbook, especially in macros that may run while another sheet is active.
Expecting IsError to identify the error type: it reports only whether the value is an error. It does not return a name such as #REF! or explain the formula that caused it.
Overwriting results while flagging errors: check that any output column or destination range is clear before a macro writes to it.

If you need to act differently for different error types, first decide whether the distinction is necessary. A general “skip or flag any error” rule is simpler and less fragile. If the exact error matters, inspect the worksheet result with an appropriate worksheet-level method or redesign the calculation to return a deliberate status; do not treat the Boolean returned by IsError as an error code.

Testing IsError with a VBA Error Value

You can also create an error value in VBA to see what the function checks. The CVErr function creates a Variant error value from an error number. In this example, xlErrNA represents the #N/A error, so the Immediate window displays True:

Sub TestIsError()
    Dim testValue As Variant

    ' Create a Variant containing the #N/A error value.
    testValue = CVErr(xlErrNA)

    Debug.Print IsError(testValue) ' Prints True
End Sub

This is a controlled test of an error value, not a way to identify why a worksheet formula failed. In ordinary workbook code, the value most often comes from reading a formula cell. The same basic test applies whether the error was produced by a worksheet formula or deliberately created with CVErr.

Frequently Asked Questions

Does IsError return True for a cell containing the text #N/A?

No. If the cell contains the literal characters #N/A as text, IsError returns False. It returns True when the cell contains Excel’s actual #N/A error value, such as a formula result that evaluates to that error.

What is the difference between IsError and IsErr?

In VBA, the built-in function is named IsError. Excel’s worksheet function ISERR is a different function and excludes the #N/A error. For a VBA check that should catch any error value, use IsError.

Can IsError tell me which error a cell contains?

No. It returns only True or False. It can tell your macro that a cell contains an error, but it does not identify the particular error type or explain its cause.

Should I use Value or Value2 with IsError?

Either can be used to check a cell’s error result. Value2 is common in automation because it returns the cell value without the Date and Currency subtypes used by Value. For detecting an error in a cell, both are suitable; be consistent with the value handling elsewhere in your macro.

Does IsError stop a macro from failing?

No. It checks a value; it does not catch every runtime error or make an unsafe operation safe. Test data with IsError, and handle possible VBA runtime failures separately when your procedure needs to recover from them.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.