Which one cost more memory to run?

[cheoksoon]

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!

 

[igold]

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]

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]

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]

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.