Macro runs more slowly each time it is run

[Jehannum_2000]

I have a .xlsm spreadsheet with a button-activated macro that runs calculations on spreadsheet data.

Each time I call the macro it takes about 0.15 seconds longer to complete, even when using the same initial data.

The slowdown becomes noticeable and obvious after a while. A calculation that previously took 0.5 seconds now takes 6.5 seconds.

If I save and re-load the spreadsheet, it goes back to its fastest speed (but again slows down on each subsequent call).

Since the re-loaded spreadsheet has exactly the same data as the saved slow version, the problem surely cannot be some unseen change in the input/output data.

Could it be because the macro is being allocated more and more memory on each use? I used Task Manager to confirm that Excel's memory allocation increases each time the macro is called.

How do I fix this?

 

[jimrward]

Can you post your macro

 

[Jehannum_2000]

Sorry, I can't post the entire macro. It's 327 lines long, and is proprietary to my company.

 

[offthelip]

Those are the symptons of a memory leak, EXCEL VBA is notably bad at managing memory, so you have to help it along a bit. The way to do this is to set all the declared objects to Nothing when you have finished with them and before you exit the macro. In theory you shouldn't need to do this, so I have usually tried setting things to zero in the major suspect procedures and then see if that works.

 

[Jehannum_2000]

Those are the symptons of a memory leak, EXCEL VBA is notably bad at managing memory, so you have to help it along a bit. The way to do this is to set all the declared objects to Nothing when you have finished with them and before you exit the macro. In theory you shouldn't need to do this, so I have usually tried setting things to zero in the major suspect procedures and then see if that works.

Thank you, I will try this.

One other thing that might be relevant is that the lag increase is worse for larger datasets.

 

[offthelip]

That would figure: it is using more memory, thus a bigger leak!!

 

[Jehannum_2000]

The problem is now fixed. I am not quite certain why it went away.

The speedup appeared to occur when I removed a loop from the calculation macro. I don't understand why this had such a dramatic effect (there were only around 12 iterations of the loop). It's as though there is a certain memory usage threshold above which Excel gets bad but below which things are fine.