Excel VBA ISERROR Function

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

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.

Sub IsError_Example1()

Dim ExpValue As Variant

End Sub

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

Sub IsError_Example1()

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

End Sub

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

Sub IsError_Example1()

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

IsError (ExpValue)

End Sub

  • Enclose this result in a message box in VBA.

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 referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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.

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.

Things to Remember

  • 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 functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more.It is useful as part of large VBA projects.It recognizes only pre-determined error values (Read error type).

This article 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: –

  • VBA IsDate FunctionIFERROR in VBAVBA On Error GoToVBA 1004 Error ExampleVBA Double