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”.
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”.
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.
We will test whether this value is error value or not.
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
Ok, let’s 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 the 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 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 called “Division by Zero”
- #N/A: When you try to fetch the data from different table 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.
Now, look at the below data set.
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.
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 below result in column 4.
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).
This has been a guide to VBA ISERROR. Here we discuss how 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 –
- Login Form in Excel VBA
- How to Create an Object in Excel VBA?
- VBA IsDate Function
- VBA Replace String
- IFERROR in VBA
- VBA On Error GoTo Example
- VBA Error Handling Examples
- VBA 1004 Error Example