How to Use VBA IsError Function (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


IsError Function Overview

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.

We will demonstrate some examples to show the usage 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 any of the error values provide True otherwise produces False.


1. Inputting Value Directly in the Formula with VBA IsError Function

We will input the value directly into 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. Inserting Cell Reference and Applying 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. Applying 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. Inserting Cell Reference Through Variable in VBA IsError Function

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. Showing the Status of the Object within the Worksheet through the 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. Using the 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.

Download Practice Workbook

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


Conclusion

In this article, we described the usage 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo