User function argument question

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

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...
--
HTH...

Jim Thomlinson


"Richard Ahlvin" wrote:

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

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

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



--
HTH...

Jim Thomlinson


"Richard Ahlvin" wrote:

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

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...
--
HTH...

Jim Thomlinson


"Richard Ahlvin" wrote:

> 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