Which one cost more memory to run?

cheoksoon

Board Regular
Joined
Aug 25, 2016
Messages
56
Dear forum users,

I'm sure there's answer to this on Google but I can't seem to find it.

I'm used to storing data in VBA arrays and/or collections.

But that can sometimes be hard to visualise when I have so many of them.

The other way is just to store it on the worksheet! It is easier to visualise and it doesn't actually get wiped when the code is refreshed (like VBA).

However, which option actually costs more memory to run?

1. Storing data in arrays?
2. Storing data in worksheets?

Thanks...

ps: Fun fact, I just found out that excel is at least 38 years old!! Older than me!
 
Last edited:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
The way you phrased your post, I am not 100% sure of what your asking. You say that you are used to storing data in arrays, but of course when you re-run the macro, the data is erased and re-written to the array. Then you mention that the advantages of storing the data on a worksheet, is you can better visualize it and it does not get wiped. Where does the data come from to populate the arrays each time the Macro is run.

At any rate, when you ask "which costs more memory to run" - Are you asking how much physical memory the macro will occupy, or are you asking which method will the macro run the fastest.

If you are referring to the speed of execution. Storing the data in an Array will be much faster than grabbing the data from a worksheet.
 

cheoksoon

Board Regular
Joined
Aug 25, 2016
Messages
56
The way you phrased your post, I am not 100% sure of what your asking. You say that you are used to storing data in arrays, but of course when you re-run the macro, the data is erased and re-written to the array. Then you mention that the advantages of storing the data on a worksheet, is you can better visualize it and it does not get wiped. Where does the data come from to populate the arrays each time the Macro is run.

At any rate, when you ask "which costs more memory to run" - Are you asking how much physical memory the macro will occupy, or are you asking which method will the macro run the fastest.

If you are referring to the speed of execution. Storing the data in an Array will be much faster than grabbing the data from a worksheet.

Thanks, I think that's what I wanted to know. Storing data in arrays is faster to run compared to grabbing data from a worksheet. :)
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
You're welcome, and yes the speed difference would be exponential. The reason being that with an array all work is being done in memory, while having your code going back and forth to the sheet to read, calculate and write is extremely inefficient.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,522
Office Version
2013, 2007
Platform
Windows
I don't know if I understand it right but a combination of the two, if you want to call it that, works.
The Data can be in a Sheet, maybe in A1 to B500, and you can pick this up into an array a work with it in memory.
It then can be put back into a sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,537
Members
406,485
Latest member
kaksolver

This Week's Hot Topics

Top