Store data in array or on a sheet?

[XLbyname]

Hi All,

During a loop I will be picking up five pieces of information relating to about 16,000 people who have ID numbers that could be anything from 1 to 700,000. I need to store this info and refer to it during the loop.

My question is, if I turn off screenupdating, will Excel be able to store and retrieve the information more quickly and stably (ie without risks of overwhelming memory leaks) if I use 5 one-dimension arrays, 1 two-dimensional array or simply store the information in a worksheet?

There will only be about 16,000 people stored in total, but I feel I would need to store them in a 700,000-sized array so I can quickly refer to DOB(i) rather than constantly searching for a matching ID in a smaller array of only 16,000.

Should I store DOB for person 999 as?

1. DOB(999)=DOB
2. Person(999,2)=DOB
3. .cells(999,2).value=DOB

Thanks for your help!

 

[offthelip]

It will be much much faster to store the data in either 5 one dimensional arrays or 1 two dimensional arrays. Which is faster or better out of those two, I don't know . I would use which ever is easiest to code because I suspect it doesn't make much difference. Unlike storing on the worksheet which is going to be very very slow!!

 

[XLbyname]

Thank you! Just what I wanted to hear.