Any way to use "standard" Excel functions inside VBA functions

[Nenad Tosic]

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 !!!

 

[Dave Peterson]

Some worksheet functions can be called with application.worksheetfunction....
Some can't.

But you can check for errors with:

if iserror(...) then
directly in VBA. No need to use application.worksheetfunction.iserror().

You may want to share the code for the UDF to get more helpful answers.

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 !!!

 

[Chip Pearson]

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]

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 !!!

 

[Nenad Tosic]

Thanks, Mr Dave, and I am sorry for my impatience, because I did not see
today early in the moning
that you and Mr Chip Pearson had already answered me, so I asked once more.
I made two UDFs.
Logics is the same. They don't do anything too useful, just find the
interval of X for which Excel function
CHIDIST(X, N) returns "#NUM!", although I was convinced that it must not (N
is Degrees_Of_Freedom,
for N>775 there is some interval ...).

Nenad

 

[Nenad Tosic]

Thanks, Mr Chip, and I am sorry for my impatience, because I did not see
today early in the moning
that you and Mr Dave Peterson had already answered me, so I asked once more.
I made two UDFs.
Logics is the same. They don't do anything too useful, just find the
interval of X for which Excel function
CHIDIST(X, N) returns "#NUM!", although I was convinced that it must not (N
is Degrees_Of_Freedom,
for N>775 there is some interval ...). I just wanted to say THANKS, so I
erased yours answer ...

Nenad