Just for clarification, xl2000 is one of the versions limited as I stated.
(statement was about using the function in a worksheet as a UDF)

Perhaps Alan missed that you were using it as a UDF in the worksheet or was
suggesting a non worksheet workaround.

--
Regards,
Tom Ogilvy

"Alan Beban" wrote in message
...
But the following works in xl2000

Sub Func3()
Dim i, j As Long
Dim varA As Variant

ReDim varA(1 To 10000, 1 To 1)

For i = 1 To 10000
For j = 1 To 1
varA(i, j) = 1
Next
Next

Range("A1:A10000").Value = varA
End Sub

Alan Beban

Tom Ogilvy wrote:

for most versions of excel the limit is 5461 elements. This may be
improved
in Excel 2002.

Public Function Func3() As Variant
Dim i, j As Long
Dim varA As Variant

ReDim varA(1 To 5461, 1 To 1)

For i = 1 To 5461
For j = 1 To 1
varA(i, j) = 1
Next
Next

Func3 = varA
End Function

will work

Public Function Func3() As Variant
Dim i, j As Long
Dim varA As Variant

ReDim varA(1 To 5462, 1 To 1)

For i = 1 To 5462
For j = 1 To 1
varA(i, j) = 1
Next
Next

Func3 = varA
End Function

will return #Value as an example.