ADD ERROR TRAP: Wrap your Formulas with IFERROR or ISERROR

'To Err is Human' might not be the sole reason behind IFERROR and ISERROR formulas but enough to justify them. So, we all does make errors and so does our logics and programs. Sometimes, we intentionally do it and sometimes, we're left to do it with no other option. So what?

So, if you're human and an Excel user, you'll need these functions almost regularly depending on your excel versions. IFERROR is introduced with MS Excel 2007 and ISERROR was used in Excel 2003 and prior versions.

In this tutorial, you'll find some basic information on IFERROR and ISERROR and some quick VBA macros at the end to wrap all your excel formulas with IFERROR or ISERROR at one go.

IFERROR of Excel 2007 is a drastic user-friendly and performance improvement over ISERROR of Excel 2003 though both have some difference between them.
IFERROR is replacement of common use of ISERROR function in conjunction with IF function.

IFERROR
Syntax: IFERROR(arg1, arg2)
Meaning 01: arg1 is a formula which may return an error or valid value and arg2 is the value the IFERROR function must return if arg1 returns an error.
Meaning 02: If arg1 is error then return arg2 else arg1. See IF(ISERROR(arg1),arg2,arg1) below.

ISERROR
Syntax: ISERROR(arg1)
Meaning: If arg1 is an error then return TRUE else FALSE.

IF(ISERROR(arg1),arg2,arg1)
Meaning: If arg1 is error then return arg2 else arg1.

However, IFERROR(arg1, arg2) is replacement of IF(ISERROR(arg1),arg2,arg1) but ISERROR is inevitable due to:
1. making MS Excel models compatible to Excel 2003 and Excel 2007.
2. the requirement of IF(ISERROR(arg1),arg2,arg3) instead of IF(ISERROR(arg1),arg2,arg1)
Please notice arg3 in the second reason.

Now, how you'll wrap all the existing formulas with IFERROR or IF(ISERROR) formulas at one go? Simple, read the following VBA macros, understand them and then use them as per your requirements. Don't miss the caution at the end.


Excel 2007

Wrap Selected Formulas
Sub Add_IFERROR_Selection()
Dim myCell As Range
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
        End If
    Next
End Sub


Wrap all formulas in Activesheet
Sub Add_IFERROR_Activesheet()
Dim myCell As Range
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
        End If
    Next
End Sub

Excel 2003

Wrap Selected Formulas
Sub Add_IFISERROR_Selection()
Dim myCell As Range
Dim cFrm As String
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            cFrm = Right(myCell.Formula, Len(myCell.Formula) - 1)
            myCell.Formula = "=IF(ISERROR(" & cFrm & "),0," & cFrm & ")"
        End If
    Next
End Sub

Wrap all formulas in Activesheet
Sub Add_IFISERROR_Sheet()
Dim myCell As Range
Dim cFrm As String
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            cFrm = Right(myCell.Formula, Len(myCell.Formula) - 1)
            myCell.Formula = "=IF(ISERROR(" & cFrm & "),0," & cFrm & ")"
        End If
    Next
End Sub

Caution:
1. Change the red highlighted 0 with the replacement you would like to have instead of 0.
2. These macros do not check if existing formulas are wrapped with iferror or iserror.
3. Make your selection wisely, you may not need to wrap all formulas with Error trap.






Comments

Ayush Jain said…
Here is the modified version of first macro mentioned above, hope you'll like it:

Sub Add_IFERROR_Selection()
Dim myCell As Range
Dim strNew as String
strNew = "0"
For Each myCell In Selection.Cells
If myCell.HasFormula And Not myCell.HasArray Then
myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & "," & strNew & ")"
End If
Next
End Sub


This will ease the process of providing replacement argument to the user.

PS: It's a nice and recommendable article.
March 5, 2011 at 3:29 PM
Ashish Jain said…
Thanks Ayush for your valuable comments. It's a pleasure that you visited.

I recommend readers to go through your comment and understand the difference in your macro, it's worth to write the VBA programs like this.
March 5, 2011 at 3:31 PM
Felix said…
What do I need to do to get "" instead of 0 as the value if there's an error?

Simply replacing the 0 with "" does not work because the concatenation thinks it's closing and opening.

And defining a string variable with """" in it produces an error.

Any help is much appreciated. Thanks.
May 12, 2011 at 2:53 AM
Ashish Jain said…
Hi Felix

Try this:

myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ","""")"
June 24, 2011 at 3:42 PM
Ulrik said…
Nice article!
In case you have array formulas, how do you alter the code to wrap these?
January 5, 2012 at 2:05 PM
Ashok said…
Hi Asish,
Need help to modify an IFERROR condition in 2007 to 2003 compliant format. Looking to avoid writing a macro any help on this will be highly appreciated. The condition is
=IFERROR(FIND("STUDENT",UPPER(O2),1)<>0,IFERROR(FIND("GONE",UPPER(O2),1)<>0,IFERROR(FIND("LEFT",UPPER(O2),1)<>0,IFERROR(FIND("RETIRE",UPPER(O2),1)<>0,IFERROR(FIND("DEAD",UPPER(O2),1)<>0,IFERROR(FIND("WRONG",UPPER(O2),1)<>0,"FALSE")))))
February 1, 2012 at 4:18 PM
Anonymous said…
Thank you for the macro.

Is there a way with VBA to remove different formulas from the function if(iserror(someformula)) in a selection of a bunch of different cells?

April 9, 2013 at 9:30 PM
@tbmarchant said…
I know this is an older post, but I found it extremely useful and wanted to leave some of the modified subs that I made based on the authors work for those who stumble on this through google.

The first is a sub that removes the IfError wrapping from selected cells. I've annotated the code to easier interpretation. Please note that instead of using 0 for the value_if_error section I use "".

Sub UndoIfErrorWrap()

Dim form As String
Dim mycell As Range

For Each mycell In Selection.Cells
If mycell.HasFormula And Not mycell.HasArray Then
' If statement to check cell for IfError function
If InStr(mycell.Formula, "IFERROR") > 0 Then
'Convert cells formula to string variable
form = mycell.Formula
'Removes IfError text
form = Replace(form, "=IFERROR(", "=")
'Removes value if error section of function
form = Replace(form, ","""")", "")
'Places modified string in cell
mycell.Formula = form
End If
End If
Next

End Sub

I also added a line to the authors code to check if an IfError function is already present so that there is no issue with the cell ending up with multiple nested IfError functions.

Sub ErrorWrapSelection()

Dim mycell As Range

For Each mycell In Selection.Cells
If mycell.HasFormula And Not mycell.HasArray Then
If InStr(mycell.Formula, "IFERROR") = 0 Then
mycell.Formula = "=IFERROR(" & Right(mycell.Formula, Len(mycell.Formula) - 1) & ","""")"
End If
End If
Next

End Sub
July 23, 2015 at 8:20 PM
Post a Comment