Yeah general purpose UDFs need to use Variants because the user can enter
the param as a string, array of literals, formula, range etc etc.

Vartype does an implicit dereferencing of the range (under-the-covers
Vartype(Var=Range.Value) ) so that it tells you what the Range contains.

But you also get the performance hit of dereferencing without the benefit of
having the data available, so if you are going to use Vartype its better to
assign the param to a variant first and then use Vartype on the assigned
variant.

If you don't want to dereference the range object because your VBA is going
to manipulate it you can use IsObject() or TypeName().


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

wrote in message
...
I want to share something that took me some hrs. to learn, to possibly
spare others my pain.

I wrote a function the user can use in a cell, e.g.:

public function Plural(ByVal S as <immaterial here) as string
Plural = S & "s"
end function

But I can only handle an address or range name, not a literal string,
i.e.:

=Plural($A$1) not =Plural("apple")

I think I've found the solution by making the param a Variant and
using TypeName:

public function Plural(ByVal S as Variant) as string
if TypeName(S) = "Range" then
Plural = W & "s"
else
Plural = "Paisan, you sick in da head? Use a range, not a literal
string."
endif
end function

***

Pa(ren)thetically, what the hell is with VarType?

When TypeName is "Range", it's 8. But when it's "String", it's ... 8.

I mean, Douglas Adams's 42 would be more informative.

***