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.
Step 2:
- Then click on Macros.
- Select Test_IsError_1 from multiple macros.
- Click on the Step Into option.
Step 3:
- Choose Module from the Insert tab.
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
Step 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
Step 7:
- Now, run the code using the previous method or simply press the F5 button.
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.
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
Step 4:
- Now, run the code by pressing F5.
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
Step 6:
- Again, press F5 to run the code.
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
Step 4:
- Now, press F5 to run the code.
- Put “1” on the input box.
Step 5:
- Finally, press OK.
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
- How to Use VBA TimeValue Function (6 Relevant Examples)
- VBA EXP Function in Excel (5 Examples)
- How to Use the VBA Weekday Function (2 Suitable Examples)
- Use VBA Int Function in Excel ( 3 Examples)
- How to Use Log Function in Excel VBA (5 Suitable Examples)
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
Step 4:
- Run the code by pressing the F5 button.
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
Step 6:
- Again, press F5 to run the code.
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.
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
Step 4:
- Press F5 to run the code.
Step 5:
- Now, modify the code using the Cells function.
Sub Test_IsError_5()
Cells(6, 3).Value = IsError(Cells(6, 2))
End Sub
Step 6:
- Press F5 to run the code.
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
Step 8:
- Finally, press F5 and run the code.
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
Step 4:
- Finally, run the code by pressing F5.
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.