Here's how to require user to pass a particular param type to a cellfunction (intercepting #VALUE).

[baobob]

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.

***

 

[Dave Peterson]

This worked fine for me:

Option Explicit
Function Plural(ByVal S As String) As String
Plural = S & "s"
End Function

It worked with:
=plural(a1)
and
=plural("asdf")

Maybe it was something else that was causing the trouble. (or maybe you used
something materially wrong in the function declaration????

 

[baobob]

Dave:

Er...did I garble my msg.?

I know a string and range both work.

I need to *prevent* a string param from being passed. It must be a
range.

Thanks much for answering, as usual.

***

 

[keiji kounoike]

Then, Why did you declare param S as range?
When you give a string value, it will return #VALUE! though it couldn't
return a warning message.

Function Plural(ByVal S as Range) as String
Plural = S & "s"
End Function

As reference said, If an object has a default property, VarType (object)
returns the type of the object's default property. and I think range's
default property is the range's value, so if range's value is a string,
it return 8 and if range's value is a number, it will return 5.

keiji

 

[Charles Williams]

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

 

[baobob]

Keiji:

As indicated in the subject field of my msg., I don't *want* the user
to see #VALUE. That's crummy programming.

I want him not only to see a humanly understandable message, but tell
him what he must do to fix the prob.

Thanks as usual to you & all who answered.

***

 

[Dave Peterson]

Ah, Now I understand your question.

Dave:

Er...did I garble my msg.?

I know a string and range both work.

I need to *prevent* a string param from being passed. It must be a
range.

Thanks much for answering, as usual.

***