Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help about handling "#NUM!" error !
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.<fun ction, -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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help about handling "#NUM!" error !
On Fri, 7 May 2010 11:26:51 +0200, "Nenad Tosic"
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.<fu nction, -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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help about handling "#NUM!" error !
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Coult not find specified object" and "Path-File access error" messages | Excel Programming | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch" | Excel Programming | |||
Is there a "generic" Error Handling method | Excel Programming | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming |