How to Use VBA IsError Function (6 Examples)

There are so many inbuilt functions available in Excel VBA. The IsError function is one of them. This function justifies the given argument and returns whether this is an error or not. If there are errors then it returns TRUE, otherwise FALSE. In this article, we will discuss this VBA IsError function and its usage. Excel worksheets also have the ISERROR function, and both perform the same operation.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to IsError Function

Function Objective:

The IsError function checks whether the input or the given value returns an error or not.

Syntax:

IsError(expression)

Argument:

Argument Required/Optional Explanation
expression Required This argument will be used for error checking.

Returns:
This operation produces
TRUE if any error is found, otherwise FALSE.

Available In:

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.


6 Examples of Using VBA IsError Function

We will demonstrate some examples to show the usages of the VBA IsError function. Some common error values are #DIV/0!, #N/A, #NAME/?, #NULL etc. This function checks the given value and if found any of the error values they provide True otherwise produces False.


1. Direct Input Value in the Formula with VBA IsError

We will input the value directly on the code and run the VBA IsError function here. We cannot directly insert an error value in the code. The VBA CvErr function will apply to generate this error value.

Step 1:

  • First, go to the Developer tab.
  • Click on the Record Macro option.
  • Put Test_IsError_1 in the Macro name box.
  • Then, press OK.

Direct Input Value in the Formula with VBA IsError

Step 2:

  • Then click on Macros.
  • Select Test_IsError_1 from multiple macros.
  • Click on the Step Into option.

Direct Input Value in the Formula with VBA IsError

Step 3:

  • Choose Module from the Insert tab.

Direct Input Value in the Formula with VBA IsError

Step 4:

  • Now, write the following code on the command window.
Sub Test_IsError_1()
   MsgBox "Is this value an Error?       " & IsError(1 / 2)
End Sub

Direct Input Value in the Formula with VBA IsErrorStep 5:

  • Now, click on the marked button of the main tab to run the code.

Here is the final result. This input value is not an error, that’s why showing FALSE.

We will insert an error value using the CvErr function and check what shows after operating.

Step 6:

  • Insert the below code on the command window.
Sub Test_IsError_1()
   MsgBox "Is this value an Error?       " & IsError(CVErr(0))
End Sub

Direct Input Value in the Formula with VBA IsError

Step 7:

  • Now, run the code using the previous method or simply press the F5 button.

Direct Input Value in the Formula with VBA IsError

Read More: How to Use VBA Val Function in Excel (7 Examples)


2. Insert Cell Reference and Apply the IsError Function

In this section, we will insert the cell references as the input. We will consider the below data set, where some error values exist.

Insert Cell Reference and Apply the IsError Function

Step 1:

  • Form a new macro name Test_IsError_2 following the steps in the previous method.
  • Then, press OK.

Step 2:

  • Step into the Test_IsError_2 macro.

Step 3:

  • Write the following code corresponding to Cell B5.
Sub Test_IsError_2()
 MsgBox "Is this value an Error?       " & IsError(Range("B5"))
End Sub

Insert Cell Reference and Apply the IsError Function

Step 4:

  • Now, run the code by pressing F5.

Insert Cell Reference and Apply the IsError Function

From Cell B5 we can see that this is a valid value, so the return is False.

Step 5:

  • Now, modify the cell reference of the code to Cell B6. So, the code becomes:
Sub Test_IsError_2()
 MsgBox "Is this value an Error?       " & IsError(Range("B6"))
End Sub

Insert Cell Reference and Apply the IsError Function

Step 6:

  • Again, press F5 to run the code.

Insert Cell Reference and Apply the IsError Function

Now, the return is True as Cell B6 contains an error value.

Related Content: How to Use VBA IsEmpty Function (5 Relevant Examples)


3. Apply User Input with VBA IsError Function

We will insert the input through the used in this section. We will also use variables here.

Step 1:

  • Initiate a new macro named Test_IsError_3.
  • Then press OK.

Step 2:

  • Step into the macro Test_IsError_3 by pressing Alt+F8.

Step 3:

  • Enter the following code on the command window.
Sub Test_IsError_3()
Dim variable_1 As Variant
Dim output_1 As String
variable_1 = InputBox("Enter a Number")
output_1 = IsError(variable_1)
MsgBox "Is this value an Error?       " & output_1
End Sub

Apply User Input with VBA IsError Function

Step 4:

  • Now, press F5 to run the code.
  • Put “1” on the input box.

Apply User Input with VBA IsError Function

Step 5:

  • Finally, press OK.

Apply User Input with VBA IsError Function

As the input value is correct, the return is False. Also, we cannot input any error value from the user input option.

Read More: How to Use VBA IsNumeric Function (9 Examples)


Similar Readings:


4. Insert Cell Reference Through Variable in VBA IsError

We will insert the cell reference through a variable. The below data set is used in this example.

Step 1:

  • Initiate a new macro named Test_IsError_4.
  • Then press OK.

Step 2:

  • Enter the Test_IsError_4 by pressing Alt+F8.

Step 3:

  • Write the below code on the command window.
Sub Test_IsError_4()
Dim n1 As Variant
n1 = Range("B5").Value
     MsgBox "Is this value an Error?       " & IsError(n1)
End Sub

Insert Cell Reference Through Variable in VBA IsError

Step 4:

  • Run the code by pressing the F5 button.

Insert Cell Reference Through Variable in VBA IsError

As the value is valid the function returns False. Now, change the cell reference.

Step 5:

  • Modify the cell reference of the previous code, then it becomes:
Sub Test_IsError_4()
Dim n1 As Variant
n1 = Range("B7").Value
     MsgBox "Is this value an Error?       " & IsError(n1)
End Sub

Insert Cell Reference Through Variable in VBA IsError

Step 6:

  • Again, press F5 to run the code.

Insert Cell Reference Through Variable in VBA IsError

Cell B7 contains an invalid value so, the return is True.

Related Content: VBA If – Then – Else Statement in Excel (4 Examples)


5. Show the Status of Object within Worksheet through IsError Function

In this example, we will show the status of each object within the worksheet.

Step 1:

  • Create a macro named Test_IsError_5.
  • Then press OK.

Show the Status of Object within Worksheet Through IsError Function

Step2: 

  • Enter the Test_IsError_5 by pressing Alt+F8.

Step 3:

  • Put the below code using the Range function.
Sub Test_IsError_5()
Range("C5").Value = IsError(Range("C5"))
End Sub

Show the Status of Object within Worksheet Through IsError Function

Step 4:

  • Press F5 to run the code.

Show the Status of Object within Worksheet Through IsError Function

Step 5:

  • Now, modify the code using the Cells function.
Sub Test_IsError_5()
Cells(6, 3).Value = IsError(Cells(6, 2))
End Sub

Show the Status of Object within Worksheet Through IsError Function

Step 6:

  • Press F5 to run the code.

Show the Status of Object within Worksheet Through IsError Function

But, we want to get the status of all cells of Column B.

Step 7:

  • We insert a for loop in the code. So, the code becomes:
Sub Test_IsError_5()
Dim x As Integer
For x = 5 To 9
Cells(x, 3).Value = IsError(Cells(x, 2))
Next x
End Sub

Show the Status of Object within Worksheet Through IsError Function

Step 8:

  • Finally, press F5 and run the code.

Show the Status of Object within Worksheet Through IsError Function

Read More: How to Use VBA Case Statement (13 Examples)


6. Use Offset Function with the IsError Function

In this example, we use the offset function with the IsError function.

Step 1:

  • Create a new macro named Test_IsError_6.
  • Then, press OK.

Step 2:

  • Enter the Test_IsError_6.

Step 3:

  • Write the following code on the command window.
Sub Test_IsError_6(strRange As String)
    Dim cell As Range
    For Each cell In Range(strRange)
        cell.Offset(0, 1) = IsError(cell)
    Next
End Sub

Sub IsError_Example1()
    Test_IsError_6 "B5:B9"
End Sub

 

Use Offset Function with the IsError Function

Step 4:

  • Finally, run the code by pressing F5.

Use Offset Function with the IsError Function

Here, we get the status of all the cells of Column B.

Related Content: VBA Date Function (12 Uses of Macros with Examples)


Things to Remember

  • The return of the IsError code must be True or False.
  • This Iserror function is also available in the worksheet.

Conclusion

In this article, we described the usages of the VBA IsError function with a detailed explanation. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo