passing named range to a UDF user defined function
 

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

passing named range to a UDF user defined function
 

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

passing named range to a UDF user defined function
 

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.

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

.

passing named range to a UDF user defined function
 

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






"Greg Wilson" wrote
in message ...
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.

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

.

.