• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Arrays and memory usage - how big before it breaks

George

George

Member
Hi All,

Possibly a silly question (tried google and searching the site and didn't come up with anything): how much space in memory does an array take up (presumably it's a function of the number of dimensions and number of elements?) and is this the most resource efficient way to manipulate medium sized datasets (say 25,000 rows, 100 columns) using excel?

Thanks,

George.
 

Hi,

it depends on the type array variable (and content of cells),
just see in VBA inner help !

Also of which Excel version is running (32 bits or 64 bits) and
how much RAM in an ordinary modern computer …

You can compare with MemoryFree property before and after creating
an array with ReDim statement …

See also in VBA inner help Erase statement …
​
 
I ask this because at present I'm running 10 of these queries, and dumping them into a table and loading it back out of said table are causing some fairly significant slowdown (before you ask nothing loops through every cell - it just batch loads the table into an array).
 
Hi Marc - thanks for getting back to me - is there no hard and fast rule for calculating it then? (I'd probably assume 32bit Office just to be on the safe side - not necessarily being run by me so I can't list exact tech specs). To answer your other question: Right now they array types are just "variant".
 

So read Variant VBA help !
Read also Excel Memory Limits …

Edit : seems MemoryFree can't calculate the array memory, sorry …
But it's not difficult to calculate rows by columns by Variant used memory !​
 
Last edited:
Depending on your data structure it may be a lot more efficient to use a specific data type or even multiple arrays of the same data type instead of a single variant array
 
You must log in or register to reply here.
Back
Top