ReDim error out of memory

[YasserKhalil]

Hello everyone

I am trying to use this line

Code: Select all

ReDim v(1 To 1000000, 1 To 150)

But I got an error "Out of memory" ..!!

What's wrong with it ..? I used that before and it was working well...

[YasserKhalil]

I have discovered the reason .. The reason is that I used this line in XLS file which is limited in the number of rows
But what's the relation between the declared array and the number of rows..? Is not supposed this array is declared in memory or it is also limited but the number of rows in the worksheet?

[HansV]

I get the error message in a .xlsm workbook too...

[YasserKhalil]

So what's the reason. Is the reason related to the word ReDim itself?

[HansV]

I assume it depends on the amount of free memory that is available. If the only running application is Excel, and the workbook with the code is the only open workbook, you might be able to create larger arrays than if you have lots of workbooks open and lots of other applications running.

[YasserKhalil]

I tested with one workbook open and I got the same. Generally, I don't think this is a logical way of thinking of using such a large array
Thank you very much.

[HansV]

A variant array of size 1000000 x 150 takes up a minimum of 1000000*150*16 bytes, corresponding to almost 2.25 GB. It looks like the maximum size is a bit below 2 GB.

[YasserKhalil]

Thanks a lot for this explanantion