User function argument question

[Richard Ahlvin]

I have a user function that works fine when I supply a cell reference as an
argument. It also works if I supply a named call as the argument. However,
when I supply the name of a range of cells, I get A #Value! error. Built-in
functions (such as SQRT) work O-K. What do I need to do to user functions
to make the arguments behave the same as for built-in functions.

R.Ahlvin

Example:
| A | B | C
--------------------------------
1 |NAME | |
2 | 7 | 7 | 2.645751
3 | 4 | 4 | 2
4 | 8 | #Value | 4

NAME Refers to: =Sheet1!$A$2:$A$4

Formulas:
| A B C
--------------------------------------
1 |NAME
2 | 7 =Fun(A2) =SQRT(A2)
3 | 4 =Fun(A3) =SQRT(A3)
4 | 8 =Fun(NAME) =SQRT(NAME)

Function:

Function FUN( ARG As Variant)
DIM X As Double
X = ARG.Value
FUN = X
End Function

 

[Jim Thomlinson]

You have lost me in terms of what you want to accomplish...

Your function takes a variant and (because nothing is specified) returns a
variant. The first 2 examples you pass in a single cell range object and the
function returns the value of the range object. In the final example you pass
in a range object consisting of a group of cells and then try to return the
value of the group. Is your intention to return the sum of the group? The
last item in the group? What do you intend the function to return? Finally
you are showing the sqrt = 4 meaning that it must be evaluating the number
16, but for the life of me I do not see where you would be getting 16 from...

 

[Richard Ahlvin]

Oops! I ment cell AA4 to be 16...
What I am trying to do, is to handle arguments in a user function the
same way the system supplied functions do. It must be super-simple, but
I haven't found the "secret" (I am rather a novice.)
I commonly make spread-sheets with a column of items having a name at
the top of the column, then use the name in a formula or function (that
requires a single value for the argument) in a nearby column. It
magically works using spreadsheet functions and built-in functions, but
fails in my user function as I attempted to indicate in my example. I
expect I am not declaring something about the user function argument in
a manner that this will work.

I also have had trouble using an array as an argument. It works fine
when I supply a range of cells on the spreadsheet (i.e. A3:A7 or C5:G5)
(it doesn't seem to matter whether it is a row or a column, it magically
works) as the argument but when I use an array constant (e.g.
{4,5,6,7,8} or {3;4;5;7;8} ) my user function fails. Again, the system
supplied functions all handle this just fine. I expect this is the same
problem if not declaring the arguments inside the user function
properly.

What I am after is simply the same bahavior that the system functions
all have.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

Sorry for the lateness of my reply. The issue you are having may have to do
with using a variant as the argument type. If you know that the function
should be accepting a range of cells then make the argument a range similar
to this...

Public Function MySum(ByVal Cell As Range) As Double
MySum = Application.Sum(Cell)
End Function

 

[Richard Ahlvin]

You have lost me in terms of what you want to accomplish...

Your function takes a variant and (because nothing is specified) returns a
variant. The first 2 examples you pass in a single cell range object and the
function returns the value of the range object. In the final example you
pass
in a range object consisting of a group of cells and then try to return the
value of the group. Is your intention to return the sum of the group? The
last item in the group? What do you intend the function to return? Finally
you are showing the sqrt = 4 meaning that it must be evaluating the number
16, but for the life of me I do not see where you would be getting 16
from...