For those who’ve now experienced Excel 2007 or 2010, you may well be aware that there is a lovely replacement for the good old scenario of:
This is, of course, the IFERROR function. But for those still using 2003 you’re stuck with the above. Now we know that this works, but it can very quickly get big, bulky and nasty. And with multiple nesting formulae it’s hard to spot when or where it’s gone wrong.
So, how to deal with this?
Well why not create a User Defined Function (UDF) that will replicate the new IFERROR function into your Excel 2003. There are, of course, variants on this theme so let’s have a look at a couple…
For our first option there is the simple:
Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant 'Duplicates Excel 2007 functionality (UDF) If IsArray(ToEvaluate) Then IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate) Else IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate) End If End Function
Now this does the job, is very fast and is, frankly, pretty neat. But, that said there is a funky variant on this which I also like and there are some really good reasons for using this instead. First the code, then the explanation and finally some timings:
Function IFERROR(ParamArray ToEvaluate() As Variant) As Variant 'Handles multiple parameter error scenarios Dim vEval As Variant For Each vEval In ToEvaluate IFERROR = vEval If Not IsError(vEval) Then Exit Function Next vEval End Function
To explain a little, using nested formulae like
means that parentheses can get out of hand quickly e.g.
So, using IFERROR makes this much less of a paren nightmare, as shown below:
But our second version compacts this even more, since it simply keeps checking the parameters to be evaluated until it gets a valid result:
Now that’s about as tidy as it gets and is an improvement even on the MS 2007 official version of IFERROR in terms of writing the nested formulae.
Oh, and timings? I set this up to do a single vlookup and default value across 10,000 cells and the timings, assuming that screen updating and calculation are turned off and on again, average out as below for a sample of 10 runs:
IFERROR type 1: 1328 milliseconds
IFERROR type 2: 1219 milliseconds
IFERROR Std 2007: 2175milliseconds
So, not a lot in it but what the second really has going for it is that it can do multiple parameters so much more simply, even though the difference in speed is not particularly striking for the UDFs:
IFERROR type 1: 10047milliseconds
IFERROR type 2: 9735milliseconds
IFERROR Std 2007: 3821milliseconds
And the oddest thing about these functions, to my mind? That for a single IFERROR parameter the custom UDFs are faster than the Excel 2007 built-in function, yet for multiple parameters the built-in function wins handsomely. Although if you’re still using Excel 2003 you shouldn’t care, since you won’t have to worry about this oddity in any case.
P.S. I originally found the second IFERROR function here, and this may well be the original since a search doesn’t provide any earlier posts, but I can’t guarantee it. So if this is not the case, and you happen to be the original author, feel free to let me know and I’ll link to the original posting of this code.