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