'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?

3 things to remember before Excel VBA Interview

50 Excel VBA Interview questions

Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs

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.

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.

Syntax: ISERROR(arg1)

Meaning: If arg1 is an error then return TRUE else FALSE.

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.

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

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

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

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

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.

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.

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

**Are you going for an interview ?**

3 things to remember before Excel VBA Interview

50 Excel VBA Interview questions

**or looking for a job ?**

Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs

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.

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.

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.

Hi Felix

Try this:

myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ","""")"

Nice article!

In case you have array formulas, how do you alter the code to wrap these?

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")))))

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?

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

Post a Comment