Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User function argument question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User function argument question
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User function argument question
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User function argument question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Argument Question | Excel Discussion (Misc queries) | |||
Function (array argument, range argument, string argument) vba | Excel Programming | |||
Argument limit on user Function? | Excel Programming | |||
Array as Argument in User-Defined Functions | Excel Programming | |||
Passing an Array of User-Defined Type to an Argument of a Function | Excel Programming |