hi all --

simple one for those of you who use formulas or arrays regularly.

In a module I have a public function -- Myfunct( inArray as variant,
inNum as Integer) params a an array and an int
It works fine.

Now I want to call it from a worksheet formula using a range as the
array parameter.
e.g.: =MyFunct(A1:D1,B2)

That formula syntax doesn't errors and Im stuck as to the proper way
to convert a range (A1:D1) to call my function. Ive checked through
the Excel array formula dox, but I don't see how to refer to a simple
range as an array.

this works --- = MyFunct({23,34,25,13},B2)
this doesn't -- = MyFunct({A1:D1},B2)

any hints? tips? thanks.
dave

david cassain wrote...
....
this works --- = MyFunct({23,34,25,13},B2)
this doesn't -- = MyFunct({A1:D1},B2)
....

The latter shouldn't work - it's a syntax error. You can't put braces
around range addresses. What do you get if you use

=MyFunct(A1:D1,B2)

?

On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote:

The latter shouldn't work - it's a syntax error. You can't put braces
around range addresses. What do you get if you use

=MyFunct(A1:D1,B2)


Thanks for the reply Harlan --

I get a #VALUE! error.
dave

david cassain wrote...
On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote:
The latter shouldn't work - it's a syntax error. You can't put braces
around range addresses. What do you get if you use

=MyFunct(A1:D1,B2)

I get a #VALUE! error.

Did you enter this as an array formula, using [Ctrl]+[Shift]+[Enter]
rathre than just [Enter]? For some reason, Excel's formula parser
doesn't require array entry for formulas containing array constants,
but it does require array entry for derived arrays, including derived
directly from ranges.

If your udf still returns #VALUE!, you're going to have to show us the
VBA code if you want further assistance.

On 26 May 2005 12:16:16 -0700, "Harlan Grove" wrote:

david cassain wrote...
On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote:
The latter shouldn't work - it's a syntax error. You can't put braces
around range addresses. What do you get if you use

=MyFunct(A1:D1,B2)

I get a #VALUE! error.

Did you enter this as an array formula, using [Ctrl]+[Shift]+[Enter]
rathre than just [Enter]? For some reason, Excel's formula parser
doesn't require array entry for formulas containing array constants,
but it does require array entry for derived arrays, including derived
directly from ranges.

If your udf still returns #VALUE!, you're going to have to show us the
VBA code if you want further assistance.

thanks again Harlan,

I get the error either way I enter it.


here's my forumla in cell A3:
= MyFunct(A1:D1,A2)


here's the sheet data -- 3 rows, 4 vals in 1st row
---------------------------------
....0.....1.......2......3
....2
....Formula
----------------------------------

here's the trivial test function in module1:

Public Function MyFunct(inArr, inInt)
'~~ test passing range from worksheet to UDF.
Dim i, tmp
For i = LBound(inArr) To UBound(inArr)
tmp = tmp + inArr(i)
Next
MyFunct = tmp / inInt
End Function


here's expected result:
A3 should equal 6 --- (0+1+2+3)/2


I still get the #VALUE! error in the worksheet when I enter my
formula with either [Ctrl]+[Shift]+[Enter] OR [Enter].

dave

On Thu, 26 May 2005 19:56:44 GMT, david cassain
wrote:

here's expected result:
A3 should equal 6 --- (0+1+2+3)/2

sorry the result should end up 3 not 6!

david cassain wrote...
....
here's the trivial test function in module1:

Public Function MyFunct(inArr, inInt)
'~~ test passing range from worksheet to UDF.
Dim i, tmp
For i = LBound(inArr) To UBound(inArr)
tmp = tmp + inArr(i)
Next
MyFunct = tmp / inInt
End Function
....
I still get the #VALUE! error in the worksheet when I enter my
formula with either [Ctrl]+[Shift]+[Enter] OR [Enter].

The udf fails on the LBOUND call because inArr is a Range object, so it
doesn't have dimensions directly. Even if you got clever and forced it
to be an array, e.g.,

=MyFunct(A1:D1+0,x)

you'd still fail on the inArr(i) expression since all ranges are 2D.

If your actual udf were as simple as your sample udf, you should
rewrite it as

Public Function MyFunct(a As Variant, n As Double) As Double
Dim x As Variant
For Each x In a
If IsNumeric(x) Then MyFunct = MyFunct + CDbl(x)
Next x
MyFunct = MyFunct / n
End Function

If your actual udf is more complicated, you really do need to show us
the code if you want help.

On Thu, 26 May 2005 19:56:44 GMT, david cassain

here's my forumla in cell A3:
= MyFunct(A1:D1,A2)


here's the sheet data -- 3 rows, 4 vals in 1st row
---------------------------------
...0.....1.......2......3
...2
...Formula
----------------------------------

here's the trivial test function in module1:

Public Function MyFunct(inArr, inInt)
'~~ test passing range from worksheet to UDF.
Dim i, tmp
For i = LBound(inArr) To UBound(inArr)
tmp = tmp + inArr(i)
Next
MyFunct = tmp / inInt
End Function


here's expected result:
A3 should equal 3 --- (0+1+2+3)/2


I still get the #VALUE! error in the worksheet when I enter my
formula with either [Ctrl]+[Shift]+[Enter] OR [Enter].

dave


working formula -- =MyFunct({0,1,2,3},B2)

non-working formula -- =MyFunct(A1:D1,B2)
It does *not* work when entered as an array formula , or a regular
formula. sigh.

the error excel is giving me is: " a value in the formula is of the
wrong data type" --- so probably "A1:D1" is the wrong formula syntax.

anyone know the proper syntax to convert an excel range --- a vba
array so I can call a UDF?

david cassain wrote:
On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote:


The latter shouldn't work - it's a syntax error. You can't put braces
around range addresses. What do you get if you use

=MyFunct(A1:D1,B2)



Thanks for the reply Harlan --

I get a #VALUE! error.
dave

Perhaps you could include the relevant portion of the function,
including the line that produces the error.

Alan Beban

On Thu, 26 May 2005 18:41:12 GMT, david cassain
wrote:

That formula syntax doesn't errors and Im stuck as to the proper way
to convert a range (A1:D1) to call my function.

sorry that typo should read :
That formula syntax errors and Im stuck as to the proper way to
convert a range (A1:D1) to call my function