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.

 

[High Plains Grifter]

Sorry - should have said - I am using XL2010, and not in compatibility mode

 

[High Plains Grifter]

Ignore me (Except when I say ignore me) I am an idiot and have realised.

 

[Andrew Poulsom]

What are those curly braces {} in your formula? The first one is in the wrong place for an array formula. Type the formula without the braces, press Ctrl+Shift+Enter and Excel will add them for you. The formula will look like this in the formula bar:

{=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))}

 

[High Plains Grifter]

sorry - I was writing the formula out from memory, as I had closed the workbook in frustration at my lack of progress. Then, having made the post I realised that I had made an error in the formula (I might try writing out non working formulae from memory again - it worked wonders!) and that was the reason for the problem. I was referencing a few rows above A1 in the original without noticing. I wrote the brackets in the wrong place at the end of a calamitous day of brain numbing awfulness where the massed armies of my own incompetence, recalcitrant computers and plain back luck were assailing me on all sides, and beset as I was with these terrors I was sinking into the cotton wool of stupidity. You are right about the brackets; I wrote them in myself in a distracted and neurotic state while pouring coffee and fury onto an already bespoiled keyboard. The machine was right to vomit my excremental offerings in my face, and I apologise for having brought a small part of that horrific day into your life; you have been tainted and I can only hope you were able to wash the stench of stupidity off your hands before it spread and took over the world. After I walked home (don't ask) I had a beer. Best ever :¬)