Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Named Range within an Array Formula | Excel Discussion (Misc queries) | |||
different formula for ROW, array / range? | Excel Discussion (Misc queries) | |||
array formula with a dynamic range. | Excel Worksheet Functions | |||
Array Formula, noncontigous range | Excel Worksheet Functions | |||
Create and Array formula using range names | Excel Programming |