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?

 

[EssKayKay]

I miss spoke a bit - there were more instances of SET than I thought but I believe I addressed them all. However, it appears this has not had any appreciable affect on performance.

Without hijacking this thread, as I noted above I have a routine that I run periodically that copies/pastes all data. The worksheet then works great for some time. What I was wondering if there is a way to determine how long a sub routine runs? I'd like a way to update a cell with the time it took the sub to run. Here is a link to my concern. Run code after set period of time (post #14)

I hope this redirection is not against forum rules. If so, simply disregard this.

Thanks again,
Steve K.

 

[EssKayKay]

I think I found what I was looking for. Here's the link:

VBA Code To Calculate How Long Your Macro Takes To Run

There are various scenarios where you may want to calculate the time it takes for your VBA code to run. I often do this when trying to test various ways to code a procedure. I like to see which method gives me a faster run time.

www.thespreadsheetguru.com

Sub CalculateRunTime()      '--- in seconds ---
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
   StartTime = Timer

'*****************************
     Insert Your Code Here...
'*****************************

'Determine how many seconds code took to run
   SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
   MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
   Range("P1") = SecondsElapsed
End Sub

Thank you all. Hopefully this does what I want. Any other problems, I'm sure I'll be back.
Steve K.

 

[offthelip]

Another possible cause of gobbling up memory is if your are doing a lot of copy and pasting. If you are try changing it to something like:

Range("your destination range").Value = Range("your source range").Value

This avoids using the clipboard

 

[EssKayKay]

Another possible cause of gobbling up memory is if your are doing a lot of copy and pasting. If you are try changing it to something like:

Range("your destination range").Value = Range("your source range").Value

This avoids using the clipboard

I do some of that. I will look into it. Thanks for the heads up.

 

[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 again offthelip,
I don't mean to hijack this thread but I have a follow up question on memory leaks if you please.

Are you were talking about RAM or disk storage. I have I checked the file size as the program begins to slow down (650± KB). The "xlsb" file size does not appreciably change. If I save the file; then reopen, even though the file size has not changed the worksheet still continues to run slow and slower with repeated use.

So, I assume you are talking RAM which makes more sense. Wouldn’t closing Excel and reopening it restore the memory loss? Or, wouldn’t a complete computer shut down and restart clear the memory loss? I did both but the worksheet continues to performs the same (i.e., slow/slower).

Currently, my only resolution is to run my “Renew” routine which basically copies/pastes all the data and resaves the worksheet. Once this is done, all runs much quicker but does again slow down over time.

Thanks,
Steve K.

 

[offthelip]

Memory leaks refers to the amount of memory that an application is using when it runs. You can check this by opening Taskmanager on windows and looking at how much memory EXCEL is using. Then run the macros and see whether the memory increases. When there is a memory leak the amount of memory increases every time you run the macro. If the memory stays the same then it is some other problem
If you completely shut down excel then that should start afresh, it should not be necessary to restart the computer. ( Once again you can check on task manager)

 

[EssKayKay]

Memory leaks refers to the amount of memory that an application is using when it runs. You can check this by opening Taskmanager on windows and looking at how much memory EXCEL is using. Then run the macros and see whether the memory increases. When there is a memory leak the amount of memory increases every time you run the macro. If the memory stays the same then it is some other problem
If you completely shut down excel then that should start afresh, it should not be necessary to restart the computer. ( Once again you can check on task manager)

I will take a look at that. But, like I said, even after a reboot it still ran slow. The only resolution so far is my "Renew" routine. I have a "time" on the primary routine that is run on most all visits. It the process takes more than 7 seconds, a message box pops up noting.

Thank you very much OffTheHip, you have been very helpful
Again, much appreciated,
Steve K.

 

[offthelip]

It looks to me like whoever wrote that Routine knew there were some problems with it. Without knowing more about the workbook and what that routine does, it is difficult to offer any further suggestions

 

[EssKayKay]

It looks to me like whoever wrote that Routine knew there were some problems with it. Without knowing more about the workbook and what that routine does, it is difficult to offer any further suggestions

I wrote much of the code via the "Record Macro" which I know is not the most efficient.

Again, doing more work on this I believe I found where the “memory leak” may be happening. However, as I noted, I do not believe this is a RAM leak but something else.

Below is my code that appears to be causing the problem. The remarked out portion is the original code. Based on suggestions here, I think I rewrote this a bit more clearly. If you please, does any of this look problematic? Does my revised code look to be correct? I ran it but since there are so many variables, I may have missed some conditions.

More testing appears to show that the second portion of the code (CopyLookupFormula) is what’s causing the problem. I have shortened that code to one line –
Range("LookupFormula").Copy Range("P33:P" & Range("LastDataRow").Value)


Here is the code –

'-------------- DeleteRows --------------
'   Range("P33:P" & Range("LastDataRow").Value).Select
'   Selection.ClearContents
'   Range("LookupFormula").Select
'   Selection.Copy
'   Range("P33").Select

 Range("P33:P" & Range("LastDataRow").Value).ClearContents
 Range("LookupFormula").Copy Range("P33")


'-------------- CopyLookupFormula --------------
'   Range("LookupFormula").Select
'   Selection.Copy
'   Range("P33:P" & Range("LastDataRow").Value).Select
'   ActiveSheet.Paste
'   Application.CutCopyMode = False

 Range("LookupFormula").Copy Range("P33:P" & Range("LastDataRow").Value)


'-------------- SetLookupDate --------------
  Range("P33").Select
  Range(Selection, Selection.End(xlDown)).Select
  Range("P33:P" & Range("LastPmtRow").Value).Select
  ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:R" & Range("LastPmtRow") & "C16"

Thank you once again for viewing,
Steve

 

[offthelip]

If you are copying lookup formula and pasting them somewhere else that means the number of lookup formulae is increasing all the time, this will make everytime the workbook recalculates much slower.
So this is probably where the problem is. Not a memory leak at all, you are increasing the size and complexity of the workbook everytime you run the macro.
I suggest you need to think carefully about your system: do you really need to copy the lookup formulae?? Is there another way of doing this? If you are copying lookup formula just to get some new values into cells then this can be done much much faster using VBA to do the lookup, rather than using a formula.