passing named range to a UDF user defined function

  • Thread starter Thread starter Brian Murphy
  • Start date Start date
B

Brian Murphy

Hello Group,

I'm stuck on something I thought was going to be easy.

I have a named range called OD that refers to a column of values.

I have a user defined function called myFunc that wants to take a single value as an argument, and return a single value.

Function myFunc(od) As Double
myFunc = 3.14 / 4 * od ^ 2
End Function

I can call myFunc if the argument is a value or a reference to a single cell, but not with the named range OD as the argument. I get #VALUE!

Is it possible to call my function with OD as an argument? I can pass OD as an argument to excel's built in functions without any trouble.

If necessary, I think it might be okay if my function were changed to return an entire column of values. I tried the following, but it didn't work either.

Function myFunc(od) As Variant
Dim i%
ReDim arr(1 To od.count)
For i = 1 To od.count
arr(i) = 3.14 / 4 * od(i) ^ 2
Next
myFunc = arr
End Function

Can someone please set me straight.

Thanks,

Brian Murphy
Austin, Texas
 
Hello Brian

If I have understood you correctly, here's one way to do it:

Function myFunc(od As Range) As Double
Dim Cell As Range
For Each Cell In od.Cells
myFunc = myFunc + 3.14 / 4 * Cell.Value ^ 2
Next Cell
End Function


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Brian Murphy" <[email protected]> skrev i en meddelelse
Hello Group,

I'm stuck on something I thought was going to be easy.

I have a named range called OD that refers to a column of values.

I have a user defined function called myFunc that wants to take a single
value as an argument, and return a single value.

Function myFunc(od) As Double
myFunc = 3.14 / 4 * od ^ 2
End Function

I can call myFunc if the argument is a value or a reference to a single
cell, but not with the named range OD as the argument. I get #VALUE!

Is it possible to call my function with OD as an argument? I can pass OD as
an argument to excel's built in functions without any trouble.

If necessary, I think it might be okay if my function were changed to return
an entire column of values. I tried the following, but it didn't work
either.

Function myFunc(od) As Variant
Dim i%
ReDim arr(1 To od.count)
For i = 1 To od.count
arr(i) = 3.14 / 4 * od(i) ^ 2
Next
myFunc = arr
End Function

Can someone please set me straight.

Thanks,

Brian Murphy
Austin, Texas
 
Brian,

When you say "I can pass OD as an argument to excel's
built in functions without any trouble" what do you mean?
As far as I know, a worksheet function that is designed to
accept an array as an argument can accept a named range.
For example, Sum(OD) or STDEV(OD) will both return
results. However, a worksheet function designed to accept
a single value as an argument will return an error. For
example, Power(OD, 5) returns #Value!.

When I tested your second version of myFunction it worked
for me. I'm wondering if you used the proper syntax in
referencing the named range when calling the function
and/or if you are aware that arrays are horizontal by
default. You need to use the transpose worksheet function
to return a vertical array.

For my test, the named range "OD" was set to refer to
cells A1:A5. TestMyFunc returned the correct results to
the ranges C1:G1 and also C1:C5 when transposed.

Sub TestMyFunc()
Range("C1:G1") = MyFunc(Range("OD"))
Range("C1:C5") = Application.Transpose(MyFunc(Range("OD")))
End Sub

Function MyFunc(od) As Variant
Dim i%
ReDim arr(1 To od.Count)
For i = 1 To od.Count
arr(i) = 3.14 / 4 * od(i) ^ 2
Next
MyFunc = arr
End Function

I answered this post with a great deal of trepedation
because I'm aware that your abilities well exceed my own.
I was particularly impressed by your contribution re
Excel's smooth curve interpolation. I suspect I've missed
the point somehow.

Regards,
Greg

-----Original Message-----
Hello Group,

I'm stuck on something I thought was going to be easy.

I have a named range called OD that refers to a column of values.

I have a user defined function called myFunc that wants
Click to expand...
to take a single value as an argument, and return a single
value.
Function myFunc(od) As Double
myFunc = 3.14 / 4 * od ^ 2
End Function

I can call myFunc if the argument is a value or a
Click to expand...
reference to a single cell, but not with the named range
OD as the argument. I get #VALUE!
Is it possible to call my function with OD as an
Click to expand...
argument? I can pass OD as an argument to excel's built
in functions without any trouble.
If necessary, I think it might be okay if my function
Click to expand...
were changed to return an entire column of values. I
tried the following, but it didn't work either.
 
Hello Greg,

Thank you for the reply, and the kind words. I learned some useful things about Beziers in solving that one.

Let me explain a bit more about the UDF thing.

1. In a new file on Sheet1 put 1,2,3,4,5 in A1:A5 and name this OD.
2. In C1 put =Power(od,2) and press Enter, and you'll see 1
3. Copy C1 down to C2:C5 and you'll see 1,4,9,16,25.

So far, so good. This is the behavior I would want. It actually seems to be behaving like an Array formula, even though it wasn't entered as one.

4. Select C1:C5 and drag it to D3:D7 and you'll see 9
16
25
#VALUE!
#VALUE!


5. Drag this to B6:B10 and you'll see #VALUE! in each cell.
6. While B6:B10 are still selected, press F2 followed by Shift+Control+Enter to make this an array formula, and now you'll see 1,4,9,16,25.

Certainly the Power function can take a single cell reference as its first argument. We now see it can take a named cell range if the formulas are on the same rows as the named cells. The above shows that it can also take a named cell range from anywhere (even on another worksheet) if it's entered as an array formula.

This is how I want my own function to behave.

After further fiddling, I've think I may now have what I want.

Function MyFunc(od) As Variant
Dim i%
ReDim arr(1 To od.Count)
For i = 1 To od.Count
arr(i) = 3.14 / 4 * od(i) ^ 2
Next
MyFunc = Application.Transpose(arr)
End Function

You were right. The problem cleared up by using Transpose. Yesterday I thought I had tried it this way, but maybe not (I know I tried it like arr(n,1), but no go). With this function defined, step 6 above gives the right answer. It doesn't match the behavior of Power exhibited in steps 2 and 3 above, but I can live with that. A page on Chip Pearson's site helped on this (http://www.cpearson.com/excel/returnin.htm). I suppose that loading the function up with more logic, it could be made to behave like Power.

Cheers,

Brian
 
I knew my interpretation was too simplistic. I doubt if my
post was both worth the read and reply. Glad to here you
got it resolved.

Take care,

Greg
-----Original Message-----
Hello Greg,

Thank you for the reply, and the kind words. I learned
Click to expand...
some useful things about Beziers in solving that one.
Let me explain a bit more about the UDF thing.

1. In a new file on Sheet1 put 1,2,3,4,5 in A1:A5 and name this OD.
2. In C1 put =Power(od,2) and press Enter, and you'll see 1
3. Copy C1 down to C2:C5 and you'll see 1,4,9,16,25.

So far, so good. This is the behavior I would want. It
Click to expand...
actually seems to be behaving like an Array formula, even
though it wasn't entered as one.
4. Select C1:C5 and drag it to D3:D7 and you'll see 9
16
25
#VALUE!
#VALUE!


5. Drag this to B6:B10 and you'll see #VALUE! in each cell.
6. While B6:B10 are still selected, press F2 followed by
Click to expand...
Shift+Control+Enter to make this an array formula, and now
you'll see 1,4,9,16,25.
Certainly the Power function can take a single cell
Click to expand...
reference as its first argument. We now see it can take a
named cell range if the formulas are on the same rows as
the named cells. The above shows that it can also take a
named cell range from anywhere (even on another worksheet)
if it's entered as an array formula.
This is how I want my own function to behave.

After further fiddling, I've think I may now have what I want.

Function MyFunc(od) As Variant
Dim i%
ReDim arr(1 To od.Count)
For i = 1 To od.Count
arr(i) = 3.14 / 4 * od(i) ^ 2
Next
MyFunc = Application.Transpose(arr)
End Function

You were right. The problem cleared up by using
Click to expand...
Transpose. Yesterday I thought I had tried it this way,
but maybe not (I know I tried it like arr(n,1), but no
go). With this function defined, step 6 above gives the
right answer. It doesn't match the behavior of Power
exhibited in steps 2 and 3 above, but I can live with
that. A page on Chip Pearson's site helped on this
(http://www.cpearson.com/excel/returnin.htm). I suppose
that loading the function up with more logic, it could be
made to behave like Power.
Cheers,

Brian






"Greg Wilson" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
 
Back
Top