# Thread: ISERROR Function (Excel 97/2000)

1. ## ISERROR Function (Excel 97/2000)

Hello,

I've been looking at all the functions and wanted to find out if someone can give me a reason/example why I would use the ISERROR function.

Thank you very much,
Tira

2. ## Re: ISERROR Function (Excel 97/2000)

Tira, it is used to check for errors. It allows you to do alternative processing when there is an error condition. For examples, check out that, and <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=68627&page=&v iew=&sb=&o=&vc=1#Post68627>the other</A> threads. Hope this helps! --Sam

3. ## Re: ISERROR Function (Excel 97/2000)

The ISERROR() function can be used to see if a particular expression returns an Error, such as #DIV/0!. Suppose you have 2 values in A1 and B1 and in C1 you want to the result of A1/B1, but if B1 = 0 that would return an error. So in C1 you could use <pre> =IF(ISERROR(A1/B1),"",A1/B1)</pre>

What that does is return a blank if the division produces an eorror, otherwise the actual division.

Andrew C

4. ## A new function: IFERROR()

Frequently when using ISERROR, my formula reads

=IF(ISERROR(GreatBigLongFormula,"",GreatBigLongFor mula)

It always seemed to me that a new function, say IFERROR (patent pending) would be a good idea. The above formula would then become:

=IFERROR(GreatBigLongFormula,"")

- meaning, of course, that you wouldn't have to repeat the GreatBigLongFormula.
I once briefly tried to create a custom function to do same, but gave up.
I wonder if Excel 2002 has such a function?

P.S. How do you make your posted formulas appear in Courier font?

5. ## Re: A new function: IFERROR()

<< How do you make your posted formulas appear in Courier font? >>

When you use the PRE tags (see Tag Panel) special formatting is applied which uses the Courier font. This formatting is intended for code listings, and as it does not word wrap is not really suitable for general use.

Good idea about the IFERROR function, but a UDF might be too cumbersome and difficult to implement, especially if you want to include all possible built in functions.

As far as your GreatBigLongFormula is concerned, when you enter the first occurrence highlight it and press Ctrl-C (or Ctrl-Insert) and then place the cursor where the second occurrence is required and press Ctrl-V. It might save some typing on very long formulae.

Andrew C

6. ## Re: A new function: IFERROR()

Thanks. I do use the Ctrl-C and Ctrl-V to save typing - it was more a question of readability.

<pre>This is a test...</pre>

7. ## Re: A new function: IFERROR()

Rory,

Tried you code with this simple situation : Cells A1 to A1 contain numbers except for one which contains 1/0, (#DIV0!).

=ErrHandle(SUM(A1:A10)) returns #VALUE!, whereas the idea is that it should return a null or blank.

I think there are some functions that do not work with the evaluate method, but am not 100% sure, but SUM() is not one of them.

Andrew C

8. ## Re: A new function: IFERROR()

Hi Andrew,
I've edited my function as I decided it was terrible too - you had to pass the formula to it as a string (which I think is why you were getting the #VALUE error) and this meant that you couldn't copy it down or across cells effectively (all the references remained the same.) The revised version I think is much better but please feel free to break it! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

9. ## Re: A new function: IFERROR()

Rory,

Your version 2 passes my previous test, and also a VLOOKUP. Nice one. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

How about a second optional argument that is returned if an error does result. Omitting the argument returns a blank cell ! <pre>Function ErrHandle(strFormula, Optional strRet As String) As Variant
Application.Volatile
If IsError(strFormula) Then
ErrHandle = strRet
Else
ErrHandle = strFormula
End If
End Function</pre>

Andrew

10. ## Re: A new function: IFERROR()

I like it - you get to choose your error message... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

11. ## Re: A new function: IFERROR()

Now I just need a way to pass the <img src=/S/confused.gif border=0 alt=confused width=15 height=20> smilie as an argument to return on error..... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> (probably better received than some of the semi-swearwords I use currently)

12. ## Re: A new function: IFERROR()

Thanks! Definitely swiped, this will go in my bag with the following tweak; the ability to set what you want to return if the formula in fact evaluates to an error:

Function IFERROR(strFormula, Optional SetTo As Variant) As Variant
Application.Volatile
If IsError(strFormula) Then
IFERROR = SetTo
Else
IFERROR = strFormula
End If
End Function

So you can use a string or NA() as "SetTo" arguments, with default return as zero. (As you can see, I preferred the "IFERROR" name. I was noodling around with this with CVErr and getting nowhere.)

13. ## Re: A new function: IFERROR()

This any good?
<pre>Function IfError(varFormula As Variant, _
Optional varErrMsg As Variant = vbNullString) As Variant
Application.Volatile
If IsError(varFormula) Then
IfError = varErrMsg
Else
IfError = varFormula
End If
End Function
</pre>

Edited by me as the first version was very cumbersome. Later edited again to add optional error message after collaboration with Andrew, Geoff and John - Rory

14. ## Re: ISERROR Function (Excel 97/2000)

Thanks for your info Andrew. Tira

15. ## Re: ISERROR Function (Excel 97/2000)

Thanks for your info, Sam. Yet, why use the formula because one will see that there is an error anyway, so why would one want it to say "True" or "False"? <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Tira

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•