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.

 

[EssKayKay]

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.

Hello, OffTheLip and others,

I am not a programmer so please bear with me.

I am having a very similar problem. As some routines are run the entire worksheet slows down. In a simplified resolution, what I’ve done is create a separate routine that basically copies all the data from the “slow” version, to a new worksheet, then saves it as the original name. I assume this is not common practice and probably poor coding but it works.

After reading your suggestion on to set all declared objects to Nothing I believe this may be quite helpful. However, I’m not really sure what you mean by “declared objects”. Does this mean every instance of a DIM xxx As xxx? There are about 50 of these in about 80 different sub routines. If so, how do I set a declared object to Nothing?

As example, does this mean:

Public Sub TryMe()
   Dim Test as String
   Test = “DOG”
    < Run code here >
   Test = “”
End Sub

Sorry for being so uninformed,
Steve K.

 

[offthelip]

Usually it is only worthwhile trying to "free up" memory with code for objects where you use a "Set" line of code to define them. The most obvious one being Ranges, but others could be File System objects, Workbooks, collections, etc. For standard variables and arrays it is not possible to set them to nothing, you would have to "Erase" them, which is not totally staightforward, I have never found it necessary to free up their memory manually.
So in your code look at every line that starts with "Set". then at the exit from the code put another line setting the same object to Nothing, e.g.:

Dim myrange As Range
Set myrange = Range("E3:G12")

' Your Code

Set myrange = Nothing

 

[EssKayKay]

Excellent. There are about 25 instances of "set" but only about 8 addressing a range. I will look into this and see what happens. I'll keep you posted.

Thank you,
SKK