Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's how to require user to pass a particular param type to a cellfunction (intercepting #VALUE).
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. *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's how to require user to pass a particular param type to acell function (intercepting #VALUE).
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. *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's how to require user to pass a particular param type toa cell function (intercepting #VALUE).
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 wrote: 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. *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's how to require user to pass a particular param type to a cell function (intercepting #VALUE).
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. *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's how to require user to pass a particular param type to acell function (intercepting #VALUE).
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. *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's how to require user to pass a particular param type to a cellfunction (intercepting #VALUE).
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If < equal to named list require user to fill out desired columns? | Excel Programming | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
How do i require a user to enter a data in a field in Excel | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
How to Pass Control from VBA to user and return | Excel Programming |