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 Varian
Dim i, j As Lon
Dim varA As Variant

ReDim varA(10, 10)

For i = 1 To 1
For j = 1 To 1
varA(i, j) =
Nex
Nex

Func2 = var
End Functio

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

ReDim varA(40, 250)

For i = 1 To 4
For j = 1 To 25
varA(i, j) =
Nex
Nex

Func3 = var
End Functio

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 suggestion
Andersson

I'm pretty sure it must be entered as an array formula. Ctrl+Shift+Enter


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"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

Hi Andersson,

I don't have a clue why Func3() doesn't work for you. Func3() works the same as
Func2() and Func4() (below) for me. I have Excel 2002 on a low end machine but
that shouldn't make a difference.

You write to enter with CTRL+ENTER but I suppose you mean CTRL+SHIFT+ENTER.
Entering with CTRL+ENTER takes forever so I haven't bothered to wait for the
outcome.

'*****
Public Function Func4() As Variant
Dim i, j As Long
Dim varA As Variant

ReDim varA(100, 250)

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

Func4 = varA
End Function
'*****

Not much help, but maybe you can make something out of this.

Regards
Anders Silven

"Andersson" skrev i meddelandet
...
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

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

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.

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.

Tom Ogilvy wrote:

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.

I was just making the point that the limitation is not a feature of the
TRANSPOSE function or of transferring data from an array to a worksheet
range, which will work on a large array if done by a Sub procedure; I
believe this is unlike versions earlier than xl2000, but I can no longer
check that.

Alan Beban