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.