The IsError function is a built-in function in Microsoft Visual Basic for Applications (VBA) that is used to determine whether a value or expression is an error or not. This function helps in handling errors in VBA code by allowing the programmer to check for errors before they occur, thereby avoiding any potential disruptions to the program execution.
VBA IsError Function – Purpose, Syntax and Arguments
Purpose:
The main purpose of the IsError function is to validate the output of a formula or expression in VBA. It allows the programmer to handle unexpected errors or invalid data by providing a way to detect and respond to them.
Syntax:
The syntax for the IsError function is:
IsError(Expression)
- Expression: This is the value or expression that is being evaluated for an error.
Example:
Sub IsErrorExample()
Dim myVar As Variant
myVar = VBA.Division(10, 0)
If IsError(myVar) Then
MsgBox "Division by 0 is not possible."
End If
End Sub
In the above example, the IsError function is used to check if the value of the variable ‘myVar’ is an error. Since the value of ‘myVar’ is an error as it is attempting to divide by 0, the message box will be displayed.
Remarks:
- The IsError function can be used with both built-in VBA functions as well as user-defined functions.
- If the IsError function returns ‘True’, it means that the value or expression passed to it is an error.
- If the IsError function returns ‘False’, it means that the value or expression passed to it is not an error.
- The IsError function only evaluates whether a value or expression is an error or not. It does not return the type of error.
Important Notes:
- The IsError function will only work with the most recent error message. This means that if multiple errors occur, only the last one will be evaluated.
- This function should be used cautiously as it may not always give accurate results, especially when working with complex formulas or expressions.
- The IsError function cannot handle runtime errors, such as ‘Overflow’ or ‘Type Mismatch’ errors. To handle these types of errors, the ‘On Error’ statement should be used instead.
The IsError function is a useful tool in VBA for handling errors and validating data. It allows for better error handling and helps to ensure smooth execution of VBA code. However, it should be used carefully, and in conjunction with other error-handling methods, to avoid any potential issues.
Understanding VBA IsError Function with Examples
Example 1: Basic Syntax
Description:
VBA or Visual Basic for Applications is an object-based programming language used for developing applications and automating tasks in Microsoft Office. It has various built-in functions that can be used to enhance the functionality of a VBA program. One such useful function is the IsError function.
Code:
IsError(var)
Let’s understand the function by breaking down each element in the code above.
- IsError is the function name.
- var is the argument or input value that we want to check for an error.
- The function returns a Boolean value, either True or False.
Explanation:
The IsError function is used to check if the value or expression passed as an argument is an error or not. It takes one argument, which can be a variable, a cell reference, or any expression.
Example 2: Checking if a cell contains an error
Description:
In VBA, it is common to use cells to store values or formulas. However, there might be instances when these cells may contain an error, such as #N/A or #DIV/0!. In such cases, the IsError function can come in handy to check if the cell contains an error or not.
Code:
Dim cell As Range
Set cell = Range("A1")
If IsError(cell.Value) Then
MsgBox "The cell contains an error"
Else
MsgBox "The cell does not contain an error"
End If
Explanation:
In the code above, we first declare a variable cell as a range and assign it to cell A1. Then, we use an If-Else statement to check if the cell.Value contains an error or not. If the cell contains an error, the message box will display the first message, and if it does not contain an error, the second message will be displayed.
Example 3: Checking for errors in a formula
Description:
In VBA, we can also use the IsError function to check if a formula in a cell returns an error or not.
Code:
Dim result As Variant
result = Application.WorksheetFunction.VLookup("Product A", Range("A1:B10"), 2, False)
If IsError(result) Then
MsgBox "The formula returned an error"
Else
MsgBox "The formula returned a value"
End If
Explanation:
In the code above, we use the VBA Application.WorksheetFunction to perform a VLOOKUP function on cell A1 to B10. If the formula returns an error, the message box will display the first message, and if it returns a value, the second message will be displayed.
Example 4: Nesting IsError function with ‘IF’ function
Description:
In some cases, we might want to perform a specific action based on whether a cell contains an error or not. In such cases, we can use the IF function along with the IsError function to achieve the desired outcome.
Code:
Dim cell As Range
Set cell = Range("A1")
If IsError(cell.Value) Then
cell.Value = "Error"
Else
cell.Value = "No Error"
End If
Explanation:
In this code, we check if the cell contains an error using the IsError function. If it does, then we assign the cell a value of “Error”. If it does not contain an error, then we assign a value of “No Error”. This way, we can use the IsError function to perform some specific action based on an error.
Example 5: Using the ‘IfError’ function
Description:
The VBA ‘IfError’ function is similar to the IsError function, but it has an additional feature that allows us to specify a value or action to be performed if an error is encountered.
Code:
Dim result As Variant
result = Application.WorksheetFunction.Divide(10, 0)
If IsError(result) Then
result = Application.WorksheetFunction.IfError(result, "Error Encountered")
End If
MsgBox result
Explanation:
In the above code, we use the Application.WorksheetFunction.Divide to divide the number 10 by 0, which will return an error. We then use the IfError function to check if the result variable contains an error. If it does, then we use the IfError function to replace the error with a custom message, “Error Encountered”. This way, we can handle errors by specifying a custom message or value, making our code more user-friendly.
Conclusion
The IsError function is a powerful tool in the VBA programmer’s toolbox. It allows us to easily check for errors in cells or formulas and perform specific actions based on them. By learning how to use this function, we can handle errors more efficiently and improve the functionality of our VBA programs.
