IFERROR for Excel 2003

I read that 2007 had an IFERROR function but I needed it now for 2003 so I did some searching around a found a solution!

I’m working with a complicated Excel workbook with multiple queries covering many worksheets. I’m currently using many VLookup functions to pull the data together on one main worksheet. The problem is that when a VLookup doesn’t return true data I end up with a ‘ #N/A ‘ entry. I needed to have a null value or a ‘ 0 ‘ when a false statement is produced. The first solution I found was to use a IF statement with an ISERROR funtion but that was a lot to type and caused the VLookup function to happen twice in many cases which seemed to increase the calculation time. I needed something better and that is when I read about the IFERROR function available in Excel 2007.

While I will have Excel 2007 in a week or two (I’m also looking forward to faster calculation because it will use both CPUs) I couldn’t delay this project. I searched around a little more and found a helpful Microsoft support article. It said that I could create my own user-defined IFERROR function for Excel 2003! I did and it seems to work well.

Here is the VB code from Microsfot’s site and a link to their page.

Basically you go to Tools -> Macro -> -> Visual Basic Editor (or hit ALT-F11)
Select Insert -> Module
Paste in this:

Function IfError(formula As Variant, show As String)

On Error GoTo ErrorHandler

If IsError(formula) Then
IfError = show
Else
IfError = formula
End If

Exit Function

ErrorHandler:
Resume Next

End Function

Then File -> Close and return to Excel.

IFERROR is basically used like this:
=IFERROR(Calulation or Function, alternate result)

If the Calculation or Function doesn’t return an error then the results are used. If it does return an error then the alternate result is used in the cell.

Support Article - Should work for older versions of Excel also!

In the end I can not guarantee that this is quicker to run but it is a lot quicker to type and cleaner to read.