title: How can I manage memory better in Excel VBA to prevent macro from slowing down?
body: <h2>Background</h2>
  
  <p>I inherited quite a bit of Excel VBA code that was wonky and ran slowly.  The code simulates user input into a report thousands of times and then generates an output file to be sent to some other users.  I made great strides in performance thus far by eliminating copy/paste, by working within arrays instead of accessing the sheets and cells directly, by removing unnecessary code, and by re-working many volatile formulas into something better.</p>
  
  <p>Prior to my changes, running this report to simulate user input would take around 65 seconds per user (and there are roughly 4,300 users).  That comes out to 279,500 seconds (roughly 77.6 hours).  That is assuming a constant speed, the same speed at which the macro begins.  This is not the case at all.  In reality, the macro slows considerably as it processes overnight and on, to the point where it is taking well over 120 seconds per user.</p>
  
  <h2>Problem</h2>
  
  <p>Following my code cleanup and performance improvements I got the macro to start at around 10 seconds per user, which is an excellent speed.  I let the macro run overnight last night and came in this morning to see it is now taking around 90 seconds per user, so I am experiencing a large degradation in performance overnight.</p>
  
  <p><strong>My question is:  How can I prevent this slowdown from occurring as the macro runs over time?</strong></p>
  
  <h2>Research</h2>
  
  <p>I am not without an idea.  Currently, my thoughts are that I would need to close and re-open the Excel workbooks because they are getting bogged down in garbage.  One idea I have is to perhaps use a native VB.NET application to open the Excel workbook and run the macros.  Every 500 or so users I could write the relevant variables somewhere in cells in the workbook, save and close the workbook, and then re-open it to pick up where I left off.  That doesn't seem very elegant to me.  Additionally, it may be difficult to convince others I need to introduce a "new" technology (VB.NET).  People are weird about that, and prefer a native VBA solution.</p>
  
  <p>Is there a better way to manage Excel so that I don't have to create a new VB.NET application and do the saving/closing/opening?</p>
  
  <p>I would like to add there is no specific offending code, it simply slows down (still works!) as time goes on.</p>