VBA code causing memory leak. Crashing Excel 2013/64. Please help.

Hi all,

Using Excel 2013, 64bit here. Was having similar issues on excel 2010 32 bit (I think, can't properly test now).

If I have a pre-defined mean (e.g. 3.75) and standard deviation (e.g. 0.601) for a number of cells, and want to extend more numbers along that same mean and standard deviation, so I'm doing the following:

In some cells, I have fixed numbers that are part of a group that fit the pre-defined mean/sd. To generate more numbers along the same precise mean/sd I'm doing

Please Login or Register  to view this content.

in new cells.

Then I have additional cells which calculate the "new" mean and SD for the original numbers, plus those generated in cells with the Norm.INV formula above.

Then I use VBA to check if the new mean and SD match the old mean and SD. If they don't, it pushes an activesheet.calculate function to re-do the norm.inv, respinning the rand().
Like so:

Please Login or Register  to view this content.

With the known mean in A1, known SD in B1 and the mean generated from the new norm.inv cells in Q4 and the new sd in R4.

This seems to work away, initially it seems ok (on a xeon 12 core, it runs without issue for about 30 seconds), but then the memory usage in excel.exe starts to build.
I ran it overnight last night (it can take a long time to randomly generate a number that matches), and Excel.exe was using 63gb ram this morning (system capacity) and it hung excel.

What is it about this code that is doing this?
Is there a way in VBA to flush memory buffers?
Perhaps there's a more elegant (in fact, I'm probably sure there is) way to do this?
I could probably import the numbers as arrays and loop over them rather than use in-sheet cells, that would probably be a faster and more sensible way of doing it I guess.

thanks for any help,
K

Hello kabammi,

Grammer aside, please change your title to better describe your problem.

Hello kabammi,

Thanks for updating your title. Office 2013 has a lots of problems and I would not be surprised if this is a known issue. I have seen issues with Office programs in the past not releasing memory during looping operations.

Unfortunately, the only known remedy for releasing memory resources is to save the program, quit the application, and restart it, which is generally not a option. Even being cautious about releasing variables in your loops is not always a guaranteed fix.

On a personal note, I think Microsoft's memory management is **** poor at best. It is the only operating I have used that exhibits this behavior consistently, Even using third party programs to help manage the memory and registry are still not enough to stave off a crash from either too little memory or memory being corrupted. With all the coding I do, if the system can run a full 7 days without a crash, I throw a party! It is usually every 3 or 4 days that the system will crash.