You can call worksheet functions from VBA code (except for those
functions, such as Month, that have a native VBA equivalent). There
are two ways to do this. First is to go through
Application.WorksheetFunction. For example,
Dim D As Double
On Error Resume Next
Err.Clear
D = Application.WorksheetFunction.VLookup( _
"a", Range("A1:B5"), 2, False)
If Err.Number <> 0 Then
Debug.Print "error"
Else
Debug.Print D
End If
In this code, the Err.Number value will be a value other than 0 if the
VLookup function fails. You need to test Err.Number to see if the
function call succeeded.
The other way is to omit the WorksheetFunction qualifier and go
directly through Application.
Dim D As Variant
D = Application.VLookup( _
"a", Range("A1:B5"), 2, False)
If IsError(D) = True Then
Debug.Print "error"
Else
Debug.Print D
End If
Here, if VLookup fails, no Error is raised but the function returns an
Error typed Variant. The IsError functions tests the variable to
determine whether it contains an error. In this method, the result
variable (D in this example) must be declared as a Variant. If it is
not a Variant, you'll get an error 13, Type Mismatch, because VBA
attempts to assign an Error to a variable type other than Variant, and
such assignment is not allowed.
If you need to determine the exact type of error (e.g., DIV/0, NAME,
etc), you can use CVErr to create an Error type variant and test the
error value against the result of CVErr. E.g.,
Dim V As Variant
' code to set value of V goes here
If IsError(V) Then
If V = CVErr(xlErrValue) Then
Debug.Print "#value"
ElseIf V = CVErr(xlErrNA) Then
Debug.Print "#n/a"
ElseIf V = CVErr(xlErrName) Then
Debug.Print "#name"
' and so on
End If
End If
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
On Thu, 6 May 2010 19:31:50 +0200, "Nenad Tosic"
<(E-Mail Removed)> wrote:
>I am new to this group, so don't mind if I ask somthing that is too easy for
>you. My question is whether
>I may use all "standard" Excel 2007 functions. I would like to find interval
>in which some function returns
>"#NUM!" although it must not, because all parameters are inside "permited
>ranges". My idea is to start
>with some interval [A, B] for which A is "acceptable" argument and B is
>"unacceptable" argument, and,
>by halving it (C=A+(B-A)/2.0) and asking : IF C is "unacceptable" argument
>THEN B=C ELSE A=C
>WHILE (B-A>(A+B)/2^32) and my User Defined Function returns A (or B). But
>when I use somthing
>like (inside my User Defined Function):
>
>Dim X as Double ' and some more variables, not necessary for explanation
>
>and, after that declaration, some assignments ... and :
>
>X=Application.WorksheetFunction.IFERROR(Application.WorksheetFunction.<_someFunc>(<parameters>);
> -8.0)
>
>(because of my "Local settings" I use semicolumn as arguments' delimiter) in
>order to assign to X value of that function
>(it is between 0.0 and 1.0 for all values of parameters), or -8.0 if the
>function returns "#NUM!", after that I would "ask"
>whether X is less than 0.0 (this means that Excel function returns "#NUM!"),
>and so on,...
>
>But when I "walk through" my User Defined Function (F8, F8, ... F8) and see
>that values of some other variables are
>being changed as I expect, when the flow goes to the "sentence" a few rows
>above program "crashes" as if the it exited
>my UDF.
>
>Please help !!!
>Thanks !!!
Similar Threads
