Just like we use IFERROR in excel to know what to do when an error is encountered before every function we have an inbuilt IFERROR function in VBA which is used in the same fashion since it is a worksheet function we use this function with the worksheet.function method in VBA and then we provide the arguments for the function.
IFERROR Function in VBA
It is a crime to expect the code to function without throwing any error. To handle errors in VBA, we have several ways using statements like On Error Resume Next VBAOn Error Resume Next VBAVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error., On Error Resume Goto 0, On Error GoTo Label. VBA error handlers can only proceed further to the next line of code. But in case if the calculation doesn’t happen, we need to replace the error with some other identity word. In this article, we will see how to achieve this by using VBA IFERROR Function in excelIFERROR Function In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula..
How to use IFERROR in VBA?
The thing to remember here is it is not a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions. rather just as a worksheet function.
For example, take the above data only for a demonstration.
Step 1: Define the variable as an integer.
Sub Iferror_Example1() Dim i As Integer End Sub
Step 2: To perform calculation, open For Next Loop.
Sub Iferror_Example1() Dim i As Integer For i = 2 To 6 Next i End Sub
Step 3: Inside the write the code as Cells(I,3).Value =
Sub Iferror_Example1() Dim i As Integer For i = 2 To 6 Cells(i,3).Value = Next i End Sub
Step 4: To access the IFERROR function, we cannot simply type the formula; rather, we need to use the “WorksheetFunction” class.
Sub Iferror_Example1() Dim i As Integer For i = 2 To 6 Cells(i, 3).Value = WorksheetFunction.If Next i End Sub
Step 5: As you can see in the above image, after inserting the command “WorksheetFunction” class, we get the IFERROR formula. Select the formula.
Sub Iferror_Example1() Dim i As Integer For i = 2 To 6 Cells(i, 3).Value = WorksheetFunction.IfError( Next i End Sub
Step 6: One of the problems in VBA while accessing the worksheet functions we don’t get to see the arguments like how we have seen in the worksheet. You need to be sure about the arguments we are using.
This is the reason before I show you the IFERROR in VBA, I have shown you the syntax of the worksheet function.
The first argument here is “Value,” i.e., what is the cell you want to check? Before this, apply the calculation in Cell.
Now in the VBA, apply the codes below.
Sub Iferror_Example1() Dim i As Integer For i = 2 To 6 Cells(i, 4).Value = WorksheetFunction.IfError(Cells(i, 3).Value, "Not Found") Next i End Sub
Now IFERROR function checks for any error in column C. If any error finds it will show the result as “Not Found” in column D.
Like this using the IFERROR function, we can alter the results as per our wish. In this case, I have altered the result as “Not Found.” You can change this to your requirement.
Types of Errors, VBA IFERROR, Can Find
It is important to know the types of excel errorsExcel ErrorsErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference. the IFERROR function can handle. Below are the kind of errors IFERROR can handle.
#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
This has been a guide to VBA IFERROR Function. Here we learned how to use the VBA IFERROR function in Excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –