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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top