passing named range to a UDF user defined function

[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

 

[Leo Heuser]

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

 

[Greg Wilson]

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

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.

 

[Brian Murphy]

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

 

[Greg Wilson]

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

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






"Greg Wilson" <[email protected]> wrote

in message news:[email protected]...