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.

--
Regards,
Tom Ogilvy




"Andersson" wrote in message
...
I need to build a function that returns a array of 100X250 elements. I do
a function like in a module that:

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

ReDim varA(10, 10)

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

Func2 = varA
End Function

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

ReDim varA(40, 250)

For i = 1 To 40
For j = 1 To 250
varA(i, j) = 1
Next
Next

Func3 = varA
End Function

when I put the formula =Func2() in a cell and use CTRL+ENTER, its works
fine, but when I put the formula =Func3() I receive a #Value!. I think that
it is because the number of elements of array. How bypass it? It´s possible
to construct a function puts a array of 100 row, 250 cols in a Excel
SpreadSheet?

I use Excel 2000.

Thanks for the suggestions
Andersson