Array formula range size
 

I am currently fighting with array formulas in different versions of Excel.
It seems that up to Excel XP there is a restriction for the result
of UDFs which return arrays of about 5000 cells.

On the other hand, TRANSPOSE for an array of size 100x100 seems to work.
Is there an overview of what works and what does not work
for the sizes of array formula results?

Array formula range size
 

For details see:
Limitations of Passing Arrays to Excel Using Automation
http://support.microsoft.com/default...b;EN-US;177991


Thus.. if possible pass a range to the transpose instead of a variant..


Sub ArrayDemo()
Dim v, Res(1 To 8)
Dim rng As Range

Const c = 1, r = 5461

Set rng = Range(Cells(1, 1), Cells(r, c))

With Application
'Res(1 to 7) will be accepted in all xl versions
Res(1) = Range(Cells(1, 1), Cells(r, c))
Res(2) = Range(Cells(1, 1), Cells(r, c + 1))
Res(3) = Range(Cells(1, 1), Cells(r * 2, c * 3))

Res(4) = .Transpose(Range(Cells(1, 1), Cells(r * 1, c * 1)))
Res(5) = .Transpose(Range(Cells(1, 1), Cells(r + 1, c * 1)))
Res(6) = .Transpose(Range(Cells(1, 1), Cells(r * 2, c * 3)))

Res(7) = .Transpose(Res(1))

'Passing an array to a worksheetfunction iso a range
'is limited to 5461 elements if version < xl2002

'res(8) will not work <xlXP
Res(8) = .Transpose(Res(2))

End With

End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Erich Neuwirth wrote:

I am currently fighting with array formulas in different versions of
Excel. It seems that up to Excel XP there is a restriction for the
result of UDFs which return arrays of about 5000 cells.

On the other hand, TRANSPOSE for an array of size 100x100 seems to
work. Is there an overview of what works and what does not work
for the sizes of array formula results?