Errors Happen

We may come across several functions in programming languages. In VBA, we are prone to encounter errors like: 

1. Syntax errors
2. Compile time errors
3. Runtime errors

These are errors in code which can be handled using error handling methods. For example, you can see some best practices in articles like this one.

In this article, we will discuss some logical errors which will give unexpected output.

Example Scenario

Let us try to copy the contents of a cell to another while the data in the cell has invalid or improper data like a “DIV/0” error which we are not aware of. 

In this case, the contents would simply get pasted as-is. But we do not want to use this erroneous data in another sheet or any cell in the same sheet.

In that case, we need an option wherein the copy paste happens only if the source data is error-free. In case of error, we need some specific statement to be pasted there.

The IfError function of VBA can help us achieve this easily.

IfError Function

The IfError function can help point out/display obvious errors in other functions that may not catch our eye easily. 

This function is used to prevent mathematical errors or any other errors that occur from a copy paste action when the source value or the resulting value has an error. This function displays resulting values only if they are error-free. If there are errors in the output values, an alternate statement text is displayed.

Syntax: 

IfError( < source value> , <alternate text> )

Where: 

1. <source value> is the cell from which text is copied
2. <alternate text> is the statement to be used in case the value of source text is erroneous.

Paste One Column’s Cell Values to Another With and Without the IfError Function

Sub ifferror_demo()
'just a for loop to iterate through each row starting from 1 to 20
For i = 1 To 20
' simply paste the cell values of col 4 to col 6
Cells(i, 6).Value = Cells(i, 4).Value
&<pre&>&<code&>' paste the values of col 4 to col 6 only if the values have no errors. If not "invalid numbers" will be pasted.
Cells(i, 5).Value = WorksheetFunction.IfError(Cells(i, 4).Value, "Invalid numbers")&>/code&>&</pre&>;
Next
End Sub

The data in column E pasted using the IfError function looks reasonable and clear.

But the data in col F that has been pasted without using the “IfError” function has not corrected/checked anything for us.

Program That Uses IfError With Vlookup

In this program, we will try to see the price/piece of any item that is selected from a list in a cell.

Price/piece is in col D and the item is in col A. So, we will use “vlookup” here.

I’m setting data validation to list the items in cell H4.

For this, we can go to Data tab-> Data tools-> Data validation. 

Choose a list and select the range as the source.

The items are now listed here:

Now I apply the vlookup formula to get the price/piece of the selected item.

Now I select some item from the list in cell H4 that has a “Division by 0 error” in col D, eg: Cake.

To avoid this kind of output, we have to use the “IfError” formula, which can filter out these errors and display a value that the viewers can understand.

Here, the existing vlookup formula is wrapped in an “IfError” formula with the vlookup formula as the first argument. The second argument is an alternate text to be displayed in case the result is erroneous.

The proper value would be displayed in case the output is error-free.

Do the same using a macro code (VBA):

Sub Excel_IFERROR_demo()

' declare a variable of type worksheet
Dim ws As Worksheet
' assign the worksheet
Set ws = Worksheets("Snackbar")

' apply the Excel IFERROR function with the vlookup function
ws.Range("I4") = Application.WorksheetFunction.IfError(Application.VLookup(ws.Range("H4").Value, ws.Range("A1:D13"), 4, False), "CHECK THE VALUE")

End Sub

Now, we will delete the formula in cell “I4” and use this macro to fill the data in that cell.

What does this code do?

This declares a worksheet object, assigns the worksheet to it. Then, we assign the vlookup formula to the cell I4 . The formula depends on the value in cell H4. 

Whenever we change the value in cell H4, this code should be run to fill the corresponding value in cell I4.

A couple of output results to demonstrate how this code works:

A Program for Mathematical Calculation

This program is for direct division or calculation of price/piece for the item “Roti.”

Sub Excel_IFERROR_demo_2()

' declare a variable of type worksheet
Dim ws As Worksheet
' assign the worksheet
Set ws = Worksheets("Snackbar")

' apply the Excel IFERROR function with the vlookup function
ws.Range("D7") = Application.WorksheetFunction.IfError(ws.Range("B7").Value / ws.Range("C7").Value, "CHECK THE VALUE")

End Sub

Here the formula =B7/C6 in the cell D7 is replaced by the output of this code.

A couple of output values for corresponding input values.

Note: Change the values of B7 or C7 or both and run this procedure (code) to see the output in D7.

Summary

“IfError” is available both as a formula and VBA function in Microsoft Excel. This helps us catch the logical faults (e.g.: Math calculations) and not the syntax or compile errors. They are mostly used in Excel formulae directly instead of macros. In the case of automated macro utilities that are large in size, they can be used in the respective VBA code too.