VBA ISERROR

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

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.”

Example #1

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

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

• Start the macro code.

Code:

Sub IsError_Example1()

End Sub

• Declare a variable to store the cell A1 value.

Code:

Sub IsError_Example1()

    Dim ExpValue As Variant

End Sub

• 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

• 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

• 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

Let us run the code and see the result of the ISERROR function.

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 cell A1 to “Hello.”

Now run the code and see the result.

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

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

• #DIV/0: This error occurs 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 it finds no value, then we will get this error, which is called “Not Available.”
• #NAME?: If Excel cannot recognize the formula or name, we will get this error.
• #NULL!: When you specify space 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. If that referenced cell deletes, then we will get this reference error.

Example #2

Now, look at the below data set.

We need to identify 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

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

Wherever TRUE is, that value is an error value.

• The ISERROR function returns the Boolean type result, i.e., 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).