What is Excel VBA ISERROR Function?
The ISERROR function in Excel VBA checks whether a value returns an error. It can be used to detect errors in the code and prescribe a workaround for it. If there is a VBA IsError match, it will return a Boolean expression that is either True or False.
Consider the following example:
In this example, a subroutine is created to check if dividing these numbers will result in an error. The MsgBox will return “False” when this code is run.
Consequently, if you mimic an error using the VBA CVErr function, this can be used to test the validity. Here is a simulation of Error 13 – which is a VBA IsError type mismatch to check for errors.
A simulation of the type mismatch error is made, and the result is printed in a message box.
Table of contents
Key Takeaways
- IsError is a VBA function used to check if a value or expression results in an error.
- It returns a Boolean value (True or False) indicating whether the specified expression contains an error such as VBA IsError type mismatch.
- Often used in conjunction with worksheet functions or operations that may produce errors, such as division by zero.
- IsError provides an alternative approach to handling errors compared to the On Error statement.
- IsError is often used in looping constructs to identify and handle errors for each iteration.
Syntax
The VBA IsError function can be used as shown below. The formula is
IsError(condition)
where
condition: It can be a variable or an expression to check if it is an error or not.
This function returns a boolean value of either True or False.
How to use Excel VBA ISERROR Function?
Following are the steps to create an example where the ISERROR Function is implemented.
Step 1: Once you open the Excel Workbook, in the title bar, choose the “Developer tab” icon and click on it.
After you click the Developer icon, select “Visual Basic”. It opens the VBA Editor, where you can follow the steps to use VBA IsError VLookup and other functions.
In the Editor’s toolbar, in the title bar, click the “Insert” button and select the “Module” option.
Step 2: Name a subroutine to check variables for errors.
Step 3: Initialize a double variable.
Step 4: To store the values in case of a VBA IsError match, declare 2 boolean variables. It means these variables will return only True or False values.
Step 5: Declare the values for the Double VBA data type and check if it is an error.
Step 6: Now, simulate the error by calling in the CVErr function, which will simulate that specific type of error.
Here, the value from the first variable is used to simulate a specific type of error.
Note: Please use whole numbers if you are trying to use the same variables. After deliberately setting up an error, check if it returns true using the IsError function.
Step 7: Define a string and type in the way you want the result to be shown. It is one of the ways to store it in a variable and then call a Message Box function on the String variable.
It helps if you want to include multiple lines in your message box function.
The vbCrLf constant in VBA is used to represent a newline character sequence. You use it to insert a line break or carriage return in strings.
The control characters “CrLf” stand for Carriage Return and Line Feed, which indicate the end of a line and move the cursor to the beginning of the next line.
Step 8: Using the MsgBox function, print the string variable.
Code:
Sub Example()
Dim n1 As Double
Dim e1, e2 As Boolean
n1 = 5
e1 = IsError(n1)
n2 = CVErr(n1)
e2 = IsError(n2)
Dim result As String
result = “Is n1 an error? ” & e1 & vbCrLf & _
“Is n2 an error? ” & e2
MsgBox result
End Sub
Step 9: Press the “run” button in the VBA Editor’s toolbar, where you can select the macros to run.
After running this subroutine, you’ll get a message box as shown below.
Now, you can use VBA IsError in different ways. You can learn more by looking at the examples below.
Examples
Let us go through different examples where the VBA IsError function can be used for error handling.
Example #1
Suppose you want to view values from a specific range in an Excel Worksheet. If there’s an error value, it will throw in an error which can be handled by using VBA IsError. Suppose cell “A1” has an error value.
Step 1: Initialize a subroutine to handle the VBA IsError cell value.
Step 2: Define variables to store the value of the cell in a variant variable. The cell is selected and stored in a range variable.
Step 3: Provide the range that you want to see the value of.
Step 4: Get the value of the cell selected in the previous step using the VBA Value function.
Step 5: Define an If-Else conditional statement to check for errors in the variable using the VBA IsError function; if there is a VBA IsError match, it will print the error in a Message Box.
Step 6: In case there are no errors in the result, write the Else statement and print the result in a message box.
Code:
Sub ExampleUsingIsErrorCell()
Dim result As Variant
Dim targetCell As Range
Set targetCell = Worksheets(“Sheet4”).Range(“A1”)
result = targetCell.Value
If IsError(result) Then
MsgBox “Error: Unable to retrieve the cell value”
Else
MsgBox “Cell Value: ” & result
End If
End Sub
Example #2
Consider an example where you want to find the square root of certain values. It can be done using the SQRT function.
Step 1: Find the square root by calling the function in Excel.
Select the adjacent cell to it and find its square root. Here, it is cell “A2.”
Step 2: Similarly, drag down the cursor till the end of the table to fill in all the values.
Now, you have all the square root values.
Step 3: You want to check which values have errors in them and print them in the 5th column. To do so, start with creating a sub-procedure.
Step 4: Find the size of the table using the xlUp function.
This function searches for the last non-empty cell value in Excel with the up-arrow key simulated.
Step 5: Start a FOR-loop to run through the whole table.
Step 6: With the VBA Cells function, check the square root values in the second column using the IfError value and paste the result in the 4th column.
Step 7: Continue the FOR-loop.
Code:
Sub FindSquareRootError()
Dim lastRow As Long
lastRow = Worksheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
Cells(i, 4) = IsError(Cells(i, 2))
Next i
End Sub
Step 8: Click the green arrow button when you want to run the sub-procedure. Then, go back to the worksheet to see the results. The errors come since you can’t find the square root of negative numbers.
Example #3
Suppose you have a sales tab with the total cost of the product along with the number of products. You must check the invalid entries. To do so, you can print the invalid values in another column while also providing the location of them in a message box so that they can be dealt with as soon as possible.
It can be done with the VBA IsError function.
Step 1: Create a subroutine to check for zero division error.
Step 2: Find the size of the table by counting the number of rows in VBA till the last non-empty value. It makes it more dynamic and will work even if the size of the table is changed.
Step 3: Start a FOR-loop running through the table.
Step 4: Perform error handling to continue the FOR-loop in case of errors.
Step 5: Print the quotient value in the third value by dividing the values in the first and second columns in iterations.
Step 6: Using the VBA GoTo function, make the subroutine execute a specific part of the code in case there’s an error.
Step 7: Find if the quotient of the values gives an error using the VBA IsError cell value.
Step 8: Under the If statement, print the cell value adjacent to the values being divided into the 5th column. Then, clear the error.
Step 9: In case there is no error, declare an Else statement to print the corresponding cell position in the 5th column as False.
Step 10: After the FOR-loop is done, exit the subroutine to prevent the errorhandler from running unnecessarily.
Step 11: Write the code for Errorhandler in case of an error. A message box is printed showing the row where the problem is, along with the type of error.
Code:
Sub FindZeroDivisionError()
Dim lastRow As Long
lastRow = Worksheets(“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
On Error Resume Next
Cells(i, 3).Value = Cells(i, 1).Value / Cells(i, 2).Value
On Error GoTo Errorhandler
If IsError(Cells(i, 1).Value / Cells(i, 2).Value) Then
Cells(i, 5).Value = True
Err.Clear
Else
Cells(i, 5).Value = False
End If
Next i
Exit Sub
Errorhandler:
MsgBox “Error occurred at row ” & i & “: ” & Err.Description
Resume Next
End Sub
Step 12: Run the subroutine and view the output below.
The final output is:
Important Things To Note
- Combine IsError with proper error handling techniques, such as On Error Resume Next and On Error GoTo, for comprehensive error management.
- When using IsError, consider providing alternative actions or values to handle errors gracefully.
- While IsError helps identify errors, it shouldn’t be used to simply ignore or suppress errors without addressing the underlying issue.
- Use a combination of error-checking techniques. IsError is a useful tool but may not cover all scenarios, especially in complex applications.
Frequently Asked Questions (FAQs)
No, VBA’s IsError function is designed to check if a value or expression results in any error, but it doesn’t differentiate between specific types of errors. Error handling techniques like On Error Resume Next and On Error GoTo are more suitable for handling specific types of errors in VBA.
Yes, you can nest Excel VBA IsError functions within each other to check for errors at different levels of a formula or expression. Each nested IsError checks the result of the enclosed expression for errors.
No, the IsError function in Excel VBA itself does not directly support custom error messages. It is primarily used to check if a value or expression results in an error.
For custom error messages, you may need to incorporate additional logic, such as using On Error Resume Next along with Err.Description to handle errors and display custom messages in your VBA code.
Recommended Articles
This has been a guide to VBA ISERROR FUNCTION. Here we explain the how to use ISERROR in Excel VBA and syntax along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply