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

.