Maximum Array Size - VBA

[starl]

I've been doing some reading and, if I understand correctly, when it comes to VBA arrays in Excel 2k and up, the only size limitation is the available memory.

In previous versions there were limitations of ~4000 and ~5000 elements... and now, there are none, for all practical purposes.

Am I understanding this correctly?

I am NOT talking about worksheet arrays - I only refer to the variable Array used in VB.

 

[NateO]

Hello Tracy,

Nope, no limits, in '97+ that I'm aware of in terms of # of elements in an array, aside from memory. But, there's a limit on which native Excel functions, e.g., counta(), transpose(), etc... Will work on in Excel versions less than XP, being 5461 elements. Now, this is true with a data-type array, but these same functions seem to work fine on objects, e.g., you can use transpose on a range object greater than 5461 cells (which feels like an array). See the following:

http://support.microsoft.com/?id=177991

Help?

 

[starl]

you're up late!
yeah, I think I understand.
You gonna be up much later? I've got another issue I'm trying to understand with Arrays...

 

[starl]

*lol*
that article was the one I read and was getting clarifcation on!

 

[Jay Petrulis]

you're up late!
yeah, I think I understand.
You gonna be up much later? I've got another issue I'm trying to understand with Arrays...

I think Bob Umlas wrote a nice paper about arrays, but I don't know where it is housed. Search for Bob Umlas White Paper Array in google, and you should pull a link.

If you really want to know about arrays in Excel/VBA, search the newsgroups for Alan Beban, (get his downloadable workbook, too). Pay particular attention to threads where Harlan Grove chimes in (in typically Harlan fashion). Those two fight at every instance, but they really, really, really know their stuff.

Tushar has posted some nice stuff in threads in this forum, too. He has some nice explanations to go with some clever code.

 

[starl]

Thanks, Jay, I'll keep them in mind.

 

[starl]

*toot* *toot*

Nate - how can you stay up so late and get to work tomorrow???

 

[NateO]

Nate - how can you stay up so late and get to work tomorrow???
{snip}

My ex-girlfriend describes me, in this sense, as a Camel Sleeper. I go without during the week, and you'll notice the opposite during the opposite.

 

[starl]

*geesh*
well, I'm gonna struggle to put in another hour.

 

[High Plains Grifter]

Is this the reason that this does not appear to work?

={INDEX('KISS Future'!$A$2:$J$23057,SMALL(IF($G$3='KISS Future'!$A$2:$A$23057,ROW('KISS Future'!$A$2:$A$23057),""),ROW(1:1)),COLUMN(A:A))}

The evaluate formula tool just collapses straght away to the apparently random answer the formaula gives, when it encounters the large array.