Array of 100X250 as return of a user defined function?

[Andersson]

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

 

[Rob van Gelder]

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


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

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?

 

[Anders S]

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

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?

 

[Tom Ogilvy]

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

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?

 

[Alan Beban]

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]

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.

 

[Alan Beban]

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