VBA IFError

VBA IFERROR

A written code many times gives the error and chances of getting an error in complex error are quite high. Like excel has IFERROR function which is used where there are chances of getting an error. IFERROR changes the error message into other text statements as required and defined by the user. Similarly, VBA IFERROR functions same as the IFERROR function of Excel. It changes the error message into the defined text by the user.

This error mostly occurs when we perform any mathematical function or if we are mapping any data which is in a different format. IFERROR handles many errors, some of them are:

#VALUE!, #N/A, #DIV/0!, #REF!, #NUM!, #NULL! And #NAME?

Example #1

Now this IFERROR function can also be implemented in VBA. Now to compare the results from Excel with VBA IFERROR we will insert a column where we will apply VBA IFERROR statement as shown below.

For this, we consider the same data and insert another column where we will perform VBA IFERROR. Go to VBA window and insert a new Module from Insert menu as shown below.

Now in a newly opened module, write Sub-category with any name. Here we have given the name of the operating function as shown below.

Code:

Sub VBA_IfError()
End Sub

Now with the help of ActiveCell, we will select the first reference cell and then directly use IFERROR Formula with reference cell (RC) -2 divided by -1 cell numbers. Which means we are dividing cell A by cell B in the first argument. And in the second argument write any statement which we want in place of error. Here we will use the same state which we have seen above i.e. “No Product Class”.

Code:

Sub VBA_IfError()

    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"

End Sub

Now select the range cell which would be our denominator. Here we have selected cell C2.

Code:

Sub VBA_IfError()

    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"
    Range("C2").Select

End Sub

Now to drag the formula to below cells where we need to apply IFERROR till the table has the values.

Code:

Sub VBA_IfError()

    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"
    Range("C2").Select
    Selection.End(xlDown).Select

End Sub

Now to come to the last cell of the column with the help of command Range where we need to drag the IFERROR formula. Here our limit ends at cell D6.

Code:

Sub VBA_IfError()

    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"
    Range("C2").Select
    Selection.End(xlDown).Select
    Range("D6").Select

End Sub

Now to drag the applied IFERROR down to all the applicable cells select the Range from End (or Last) cell to Up to the applied formula cell by End(xlUp) command under Range.

Code:

Sub VBA_IfError()

    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"
    Range("C2").Select
    Selection.End(xlDown).Select
    Range("D6").Select
    Range(Selection, Selection.End(xlUp)).Select

End Sub

As we do Ctrl + D to drag the up cell values to all selected cells in excel, here in VBA, Selection.FillDown is used to fill the same up cells values in all selected cells.

Code:

Sub VBA_IfError()

    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"
    Range("C2").Select
    Selection.End(xlDown).Select
    Range("D6").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown

End Sub

This completes the coding of VBA IFERROR. Now run the complete code by clicking on the play button as shown in below screenshot.

As we can see above, in column D starting from Cell 2 to 6 we got our results.

There is another format and way to apply IFERROR in VBA. For this, we will consider another set of data as shown below. Below we have sales data of some products in column A and quality sold in column B. And we need map this data at cell F2 with reference to product type Laptop. Now data in the first table has #N/A in cell B3 with for Laptop quantity sold out which means that source data itself has an error. And if we look up the data from the first table to Cell F2 we will get same #N/A here.

For this open a new module in VBA and write Subcategory with the name of a performed function.

Code:

Sub VBA_IfError2()

End Sub

Select range where we need to see the output or directly activate that cell by ActiveCell followed by dot (.) command line as shown below.

Code:

Now select FormulaR1C1 from the list which is used for inserting any excel function.

Now use the same formula as used in excel function in VBA as well.

Code:

Sub VBA_IfError2()

    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],R[-1]C[-5]:R[3]C[-4],2,0),""Error In Data"")"

End Sub

Now select the cell where we need to see the output in Range. Here we have selected cell F3.

Code:

Sub VBA_IfError2()

    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],R[-1]C[-5]:R[3]C[-4],2,0),""Error In Data"")"

    Range("B8").Select

End Sub

Now compile and run complete code using F5 key or manually and see the result as shown below.

As we can see in the above screenshot, the output of product type Laptop, from first table #N/A is with error text “Error In Data” as defined in VBA code.

Pros of VBA IFERROR Function

• It takes little time to apply the IFERROR function through VBA.
• Result from Excel insert function and VBA IFERROR coding, both are the same.
• We can format or paste special the applied formula as well to avoid any further problem.

Cons of VBA IFERROR Function

• IFERROR with this method, referencing the range of selected cell may get disturbed as the table has limited cells.

Things To Remember

• We can record the macro and modify the recorded coding as per our need.
• Always remember to save the file as Macro-Enabled Excel, so that we can use the applied or created macro multiple times without any issue.
• Make sure you remember to compile the complete code before ending the command function. By this, we can avoid or correct any error incurred.
• You can apply the created code into a button or tab and use it by a single click. This is the quickest way to run the code.
• Best way to avoid any changes after running the code is to paste special the complete cells so that there will not be any formula into the cell.

Recommended Articles

This has been a guide to Excel VBA IFERROR Function. Here we discussed how to use IFERROR Function in VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

1. VBA Arrays
2. VBA Number Format
3. VBA Find
4. VBA Do While Loop