Excel VBA IsError
There are many types of functions in VBA. One of them is Excel VBA IsError. This function can be termed as an informative function because it provides us information as it is a logical function. This function tells us whether a given value contains an error or not. As I said above that it is a logical function means the value or the output generated by this function is either true or false. So how does this function works? We supply the expression to this function and when the expression is turned to be a simple integer or a value in the calculation then the Boolean output by this function is False however if the function returns an error then the Boolean result for this function would be True. True meaning the value or expression does not represent or is not an error while False meaning the value or the expression is an error.
How to Use VBA IsError Function?
In these examples, we have seen that there are plenty of ways to use the IsError function in VBA. They are as follows,
- We can use the IsError function on a range of cells and check whether the cell has an error or not.
- We can also change a perfect value to an error and test it with the IsError function.
- On the other hand we can also use the expression directly in the code itself.
Let us begin with a basic example, in this example we will take a reference of a cell value from sheet of excel and test whether it contains an error or not. For this, follow the below steps:
Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.
Step 2: Once we have a module in our project explorer we can begin with our example, Write the subprocedure of VBA IsError Function.
Sub Example1() End Sub
Step 3: Now let us use A2 cell for reference and check whether the value in it is an error or not, currently the cell doesn’t have any value. We will use the Msgbox function to display.
Sub Example1() MsgBox IsError(Sheet1.Range("A1")), vbOKOnly, "Does cell A2 contain an error?" End Sub
Step 4: When we execute the above code by pressing function key F5 and to run the code, click on the Play button located below the menu bar.
Now let us use the concept where we can turn a variable with value to an error. In the following example, we will test two variables one we know for sure is not an error and another variable which we will convert into an error and check with the IsError function. For this, follow the below steps:
Step 1: Let us begin further in the same module from above and can declare another subprocedure.
Sub Example2() End Sub
Step 2: Now let us declare two different variables in the procedure.
Sub Example2() Dim var1, var2 Dim isErr1 As Boolean Dim isErr2 As Boolean End Sub
Step 3: Now let us assign the first variable with a general value and for the second variable we will change the value to an error and then test it with the function known as IsError.
Sub Example2() Dim var1, var2 Dim isErr1 As Boolean Dim isErr2 As Boolean var1 = 10 isErr1 = IsError(var1) var2 = CVErr(11) isErr2 = IsError(var2) MsgBox isErr2 End Sub
Step 4: When we execute the above code by pressing function key F5 and to run the code, click on the Play button.
We have true as a result because the expression in the function was the second variable and which is an error since this function is an informative function it gave us the result as yes the value in the function is an error.
In this example, we will notice how to use the IsError function while working with a bunch of code and how we can test it. So in this example, we will see that expression in the code is an error or not. For example, 10/0 is a dividend error but 0/10 is not an error. For this, follow the below steps:
Step 1: So Again we use the same module for our third example and start by declaring a third subprocedure.
Sub Example3() End Sub
Step 2: Now let us declare two variables, one as an expression while another as an output for the expression.
Sub Example3() Dim Expression1 Dim Output As Boolean End Sub
Step 3: Now let us put a value to the expression.
Sub Example3() Dim Expression1 Dim Output As Boolean Expression1 = 0 / 100 End Sub
Step 4: Now we will store the output of the expression in the output variable.
Sub Example3() Dim Expression1 Dim Output As Boolean Expression1 = 0 / 100 Output = IsError(Expression1) End Sub
Step 5: Now we can use the msgbox function to display the result of the output variable.
Sub Example3() Dim Expression1 Dim Output As Boolean Expression1 = 0 / 100 Output = IsError(Expression1) MsgBox "The expression(0/100) is an error or not : " & Output, vbInformation, "VBA IsError Function" End Sub
Step 6: When we run the above code we will get the following result.
Explanation of Excel VBA IsError Functions
As we have discussed above that IsError in VBA is an informative function that is used to identify whether a given expression has an error or not. The syntax for the function has one mandatory argument which is the expression that needs to be evaluated.
Also, we can use texts to display in the function to show the relevance as shown in example 3.
Things to Remember
These are the things which we should keep in mind IsError function in VBA and they are as follows,
- IsError is an Information function in VBA.
- The function has a Boolean value as an output.
- The result displayed by the function is either true or false.
This is a guide to the VBA IsError. Here we discuss how to Use IsError Function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –