VBA ISERROR

Excel VBA ISERROR Function

VBA IsError the function name itself sums up the functionality. This function will identify whether the value we have supplied is an error value or not. If the supplied value or range reference value is an error value then we will get the result as “TRUE”, if the value is not an error value then we will get the result as “FALSE”.

Syntax

IsError Syntax

The expression is nothing but the value we are testing or the cell reference value or formula expression. And as you can see, the result will be “Boolean.”

Examples

You can download this VBA ISERROR Excel Template here – VBA ISERROR Excel Template

Example #1

We will see a simple example to find whether the value is an error or not. For example, we have below value in cell A1.

Example 1.1 (Excel Data)

We will test whether this value is an error value or not.

  • Start the macro code.

Code:

Sub IsError_Example1()

End Sub

Example 1.2

  • Declare a variable to store the cell A1 value.

Code:

Sub IsError_Example1()

    Dim ExpValue As Variant

End Sub

Example 1.3

  • Now assign the value of cell A1 to this variable in VBA.

Code:

Sub IsError_Example1()

    Dim ExpValue As Variant
    ExpValue = Range("A1").Value

End Sub

Example 1.4

  • Now test whether this variable value is an error or not.

Code:

Sub IsError_Example1()

    Dim ExpValue As Variant
    ExpValue = Range("A1").Value

    IsError (ExpValue)

End Sub

Excel VBA ISERROR - Example 1.5

  • Enclose this result in a message box in VBA.

Code:

Sub IsError_Example1()

    Dim ExpValue As Variant
    ExpValue = Range("A1").Value

    MsgBox IsError(ExpValue)

End Sub

Excel VBA ISERROR - Example 1.6

Ok, let’s run the code and see the result of the ISERROR function.

ISERROR Output 1

The result is TRUE because the value in cell A1 is #DIV/0! which is the division error.

Now we will change the value of the cell A1 to “Hello.”

VBA ISERROR - Example 1.7

Now run the code and see the result.

Excel VBA ISERROR Output 2

So, the result is FALSE now because the value in cell A1 is not the error value.

So, first, we need to understand what are the error types and why they occur in excel worksheet. Below are the detailed error values and explanations.

  • #DIV/0: This error occurs mainly because when we try to divide the number by zero. This error is called “Division by Zero.”
  • #N/A: When you try to fetch the data from different tables, and if the no value found, then we will get this error, and this error is called “Not Available.”
  • #NAME?: If excel not able to recognize the formula or name, then we will get this error.
  • #NULL!: When you specify a space character in between the cell references instead of a comma.
  • #NUM!: The numerical value supplied to the data isn’t a valid one.
  • #VALUE!: When you reference the cell values for mathematical calculations, and if the number format is not correct, we will get this error.
  • #REF!: If the cell is a formula, it has cell references, and if that referenced cell is deleted, then we will get this reference error.

Example #2

Now, look at the below data set.

Example 2 (Excel Data)

We need to identify what are the error values from this list and store the result, either TRUE or FALSE, in the next column.

Since we need to test more than one cell, we need to include this in loops; the below code will identify the error values.

Code:

Sub IsError_Example2()

    Dim k As Integer

    For k = 2 To 12
        Cells(k, 4).Value = IsError(Cells(k, 3).Value)
    Next k

End Sub

Example 2 (VBA Code)

When you run this code, we will get the below result in column 4.

ISERROR Output 3

Wherever TRUE is there, that value is an error value.

Things to Remember

  • ISERROR returns the Boolean type result, i.e., either TRUE or FALSE.
  • It is available as a worksheet function as well as a VBA function.
  • It is useful as part of large VBA projects.
  • It recognizes only pre-determined error values (Read error type).

Recommended Articles

This has been a guide to VBA ISERROR. Here we discuss how the excel VBA ISERROR function identifies whether the value we have supplied is an error value or not with examples. You can learn more about VBA functions from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>