Prev: Select first cell in active row?
Next: File Attributes Function
From: Nenad Tosic on 7 May 2010 05:26
Hello all,
this time I will be much shorter:

Is there any way that I can "handle" errors. When Excels' <function> returns
error ("#NUM!") (in a worksheet)
I may type in a cell "=IFERROR(<function>; -5.0) (my local settings needs
semicolumn as argument separator)
in order to display either value that <function> returns (if it is not
error) or -5.0 if it returns error of some types
("#N/A", "#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?" or "#NULL!")).

Is there any mechanism that I may use in order to "catch" error that returns
"WorksheetFunction.<function>"
inside VBA code (User Defined Function)? If I type something like:

X=WorksheetFunction.IsError(WorksheetFunction.<function>, -5.0)

and step through UDF ("F8") everything is OK, variables get their proper
values, ... until that assignment, if <function>
returns "#NUM!". If <function> returns some "proper" value everything is OK
until next assignment in thich <function>
returns "#NUM!". My UDF simply "exits", all variables lose their values, as
if function is exited (normaly or abnormaly).

Thanks in advance
Nenad

From: Ron Rosenfeld on 7 May 2010 06:14
On Fri, 7 May 2010 11:26:51 +0200, "Nenad Tosic" <tosicnenad(a)ikomline.net>
wrote:

>Hello all,
>this time I will be much shorter:
>
>Is there any way that I can "handle" errors. When Excels' <function> returns
>error ("#NUM!") (in a worksheet)
>I may type in a cell "=IFERROR(<function>; -5.0) (my local settings needs
>semicolumn as argument separator)
>in order to display either value that <function> returns (if it is not
>error) or -5.0 if it returns error of some types
>("#N/A", "#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?" or "#NULL!")).
>
>Is there any mechanism that I may use in order to "catch" error that returns
>"WorksheetFunction.<function>"
>inside VBA code (User Defined Function)? If I type something like:
>
>X=WorksheetFunction.IsError(WorksheetFunction.<function>, -5.0)
>
>and step through UDF ("F8") everything is OK, variables get their proper
>values, ... until that assignment, if <function>
>returns "#NUM!". If <function> returns some "proper" value everything is OK
>until next assignment in thich <function>
>returns "#NUM!". My UDF simply "exits", all variables lose their values, as
>if function is exited (normaly or abnormaly).
>
>Thanks in advance
>Nenad

You could try using the Evaluate method.

e.g.:

====================================
Option Explicit
Sub TestError()
Dim r As Variant

r = WorksheetFunction.IfError(Evaluate("IRR({10,20})"), -5)

Debug.Print r
End Sub
=======================================

will print -5 even though the formula gives a #NUM error.
--ron
From: Nenad Tosic on 7 May 2010 15:51
Thanks, Ron !! I was a little bit too impatient (I could not "see" reply
from Dave Peterson
and (especially) Chip Pearson, who proposed me to use Err.Number (I got
status 1004
when error occured and 0 when everything was OK) instead of Daves' proposal
to use
"if iserror(<function>) then ..."). I used both of them to find two UDFs (I
will test which
is faster, because the logics of both UDFs is the same). I will also try to
use "IfError()",
but later...

(I am not sure, but I think that I have already experimented with
"IfError()").

Nenad

"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message
news:0np7u5tq01eu49rh6i0f000gkencv3cl9o(a)4ax.com...
> You could try using the Evaluate method.
>
> e.g.:
>
> ====================================
> Option Explicit
> Sub TestError()
> Dim r As Variant
>
> r = WorksheetFunction.IfError(Evaluate("IRR({10,20})"), -5)
>
> Debug.Print r
> End Sub
> =======================================
>
> will print -5 even though the formula gives a #NUM error.
> --ron

 | 
Pages: 1
Prev: Select first cell in active row?
Next: File Attributes Function