Max size of array returned by function?

[N1GHTEYES]

I have an function which returns a array. If the size of the array is 5461 rows or less, it works fine. If it is more than that, it gives VALUE! error.

In the latter case it does not cause the execution to be halted if I have Break on all Errors set.

I have not been able to find a reference to a specific Excel limit to the size of arrays returned from functions, but it looks like that's what I'm getting. Can anyone confirm this is the problem? If not, is there a way to avoid it?

If you want to see what I mean, the code below is a simple example showing the problem. If you paste this function into 5461 cells as an array formula, they all get filled w/ random values. If you paste it into 5462 cells, they all get VALUE! error.

Public Function dummy() As Variant

Dim i As Long, max As Long
max = Application.Caller.Rows.count

ReDim ary(1 To max, 1 To 1) As Variant
For i = 1 To max
    ary(i, 1) = Rnd()
Next i
dummy = ary()

End Function

Any suggestions gratefully received.

Tony

 

[GlennUK]

I would have expected a different limit ... help says:
Maximum size of worksheet arrays 6,553 elements
which I would have assumed was due to a limit such as you are experiencing. Haven't heard of a 5,461 limit, as far as I can remember.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Using excel 2003 I do not get the error on 5,462 elements

I also no not get an error on 6,554 elements.

In fact, I do not get an error on 330,000 elements.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Geoff, how did you manage to have 330,000 rows in an excel 2003 sheet ?

 

[GlennUK]

Hey Geoff, you are the lucky one ... am still using Excel 97 here ( they are talking about having an upgrade soon, but who knows ).

PHV, 330000 elements can consist of multiple columns AND rows ... not necessarily just rows.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PHV]

I thought the OP talked about rows ...

 

[GlennUK]

He also talked about cells.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[N1GHTEYES]

Phew - you chaps have been busy while I slept.

Glen - where did you find that reference to 6,553? (I realise it would probably be tempting, but please don't say "in the help").

Actually, I was talking about rows, but I've since tried it using a multiple column return array and the limit does seem to be cells. The maximum rows I can have in a N rows by 2 columns range (without getting the VALUE! error) is 2730.

However, that limit only seems to apply to arrays returned from functions. If I create an array in a sub and assign it to a range on the sheet, it is happy to take at least 60,000 rows by 2 cols.

BTW, the app in which I got the problem is Office 97. Apart from moving to XL2003, does anyone have any suggestions?

Tony

 

[xlbo]

PHV ;-)
Tried with 5461 rows * 1 col
Tried with 5462 rows * 1 col
Tried with 6553 rows * 1 col
Tried with 6554 rows * 1 col
Tried with 11000 rows * 1 col
Tried with 11000 rows * 10 columns
Tried with 11000 rows * 30 columns = 330,000 elements

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[N1GHTEYES]

Geof,

Does "tried" mean writing an array of that size, or using the function I posted in a range of that size?

Tony

 

[GlennUK]

Hi NightEyes

yes, I found that ref to 6553 elements in the Excel Specifications part of the help. That's for Excel 97, which is what you've got too.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Tony - used the function you provided entered as an array formula into the number of cells as detailed in my last post. Probably not too helpful though as any number of specs have changed between '97 and 2003

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[N1GHTEYES]

Thanks chaps. I found the ref you mentioned Glenn. I also searched both XL97 and XL2000, in both the XL help and the VBA help, but could not find any referenec to "returning arrays from functions", though I'm sure I have read something similar in the help before. I was hoping that such an entry might have had something to say about this issue.

The Excel specifications help in XL2000 says:

Worksheet arrays:
Limited by available memory. Also, arrays cannot refer to entire columns. For example, an array cannot refer to the entire column C:C or to the range C1:C65536. However, an array can refer to the range C165535 because the range is one row short of the maximum worksheet size and does not include the entire C or D column.

However, I checked the size of the actual maximum array returnable in XL2000 and it is the same as XL97. So the limit quoted above (and in the 97 help) clearly does not apply to arrays returned from functions in either 97 or 2000.

I've tried googling this issue w/ various related terms, but have not so far found any useful info. Has anyone got any other references or info on what the actual limitation is, and if there might be a way round it?


Thanks,
Tony