Excel memory cache?

[AlanMiller]

I've been looking a spreadsheet with lots of calculated cell values in some 30,000 rows (pushing towards the limit of XL AFAIK). The details are here http://www.drroyspencer.com/2010/04/sim ... rsion-1-0/ and the 4MB XLS is available for download. The reported concern is that the author suspects

After running the model many times, eventually the memory cache used by Excel gets filled up (I think), and garbage numbers start to appear. Just close out Excel and re-open it to fix this.

I haven't run up against this in my trials, but I would like to find out more about this memory cache issue.

Some calculated numbers involve generated random numbers e.g.

Code: Select all

=SQRT(-2*LN(RAND()))*SIN(2*PI()*RAND())*$E$4

I remember some issue associated with having to "reseed" random number generating routines to get/keep them working properly. I also seem to recall this being a reported problem with this program. Is "reseeding" still an issue with RAND()?

thanks for any info
Alan

[HansV]

The reseeding "problem" applies to the VBA Rnd function, but it is in fact by design, to make it possible to test using the same random number sequence if desired. See Rnd function (VBA).
Excel's RAND worksheet function doesn't need to be reseeded as far as I know.

I don't know how Excel's memory cache works.

[AlanMiller]

Thanks Hans. That at least sorts out the reseed issue.
How's the hand now BTW? Back to the full 64 digit typing capability?

Alan

[HansV]

My han(d)s are doing well, thank you!

[AlanMiller]

Good to hear. Can't keep a good man down for long, eh?

Alan