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]

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.

I will look at that. I may need some assistance but we'll see what happens. Regarding size of the workbook, I have checked that after numerous runs and if for the most part it remains constant - around 650KB. So it looks like the file is not getting any larger.

 

[EssKayKay]

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.

I understand what you are saying. The formula is dependent on other cells in the row. By “copying”, the cells references remain relative. Therefore all references are in tack. I’m not sure how to update all the cells in the range via VBA.

The LookupFormula being copied for Row 33 is IF(B33="","----",IF(M33<=0,C33,"----")). This formula is then being copied throughout the range. In this case “P33:P365”. The range changes as other data may be entered.

I tried using the “Offset” command to test if the appropriate cell matches the condition. Simply as a test to see if I could get this to work, I only addressed the first part of the formula. In column “P” I placed the formula: =IF(ActiveCell.Offset(0, -14)=””,1,2). However, it returns #NAME? in the cell.

Apparently I’ve got something wrong. Any suggestions?

Thanks,
SKK

 

[offthelip]

You need to be careful about using the Offset function because this is a "Volatile" function. This means that excel recalculates it everytime there is a calculation reguardless of whether the input values have changed. It is probably worth your while have a read about the EXCEL Recalculation process because it might allow you to identify where it is going wrong for you:
Excel Recalculation

 

[EssKayKay]

You need to be careful about using the Offset function because this is a "Volatile" function. This means that excel recalculates it everytime there is a calculation reguardless of whether the input values have changed. It is probably worth your while have a read about the EXCEL Recalculation process because it might allow you to identify where it is going wrong for you:
Excel Recalculation

OffTheLip,

I’m back and thank you once again. I think I may beginning to become a bit of a pest here so maybe it’s time to put this thread to bed but I’ll ask one more question if you please.

I read Excel Recalculation and for the most part understand what’s happening. However, I have been unsuccessful at finding some way to update the formula(s) in the range via VBA but not using copy/paste.

As noted in example, the formula being copied in range “P33:P368” (which can vary) is

IF(B33="","----",IF(M33<=0,C33,"----"))
IF(B34="","----",IF(M34<=0,C34,"----"))
IF(B35="","----",IF(M35<=0,C35,"----"))
.
.
.
IF(B368="","----",IF(M368<=0,C368,"----"))

Without being too demanding here again, could you shed some light on how I would achieve something like this through VBA without using copy/paste?
Again, I understand if you feel I am becoming too demanding. If so, simply disregard this post.

Thanks,
Steve K.

 

[MARK858]

You can apply the formula in one go (Excel will adjust the rows) and convert it to values if need be

    With Range("P33:P368")
        .Formula = "=IF(B33="""",""----"",IF(M33<=0,C33,""----""))"
        .Value = .Value
    End With

@offthelip will probably give you some array code later

 

[EssKayKay]

Thank you both for your time and consideration. I have this working much better. I’m still seeing a slight slowdown as routines are run but nothing like previous. For now, I’m going to consider this portion of the project done.

I tested each of the three routines separately and as a group. As expected, the main culprit is #2 CopyLookupFormula. The other two had negligible effect on performance.

1) -------------- DeleteLookupDate (fka DeleteRows) --------------
   OLD/NEW CODE –
     Range("P33:P" & Range("T14").Value).ClearContents
     Range("LookupFormula").Copy Range("P33")


2) -------------- CopyLookupFormula --------------
  OLD CODE –
    Range("LookupFormula").Copy Range("P33:P" & Range("T14").Value)

  NEW CODE –
    With Range("P33:P" & Range("T14").Value)
       .Formula = "=IF(B33="""",""----"",IF(M33<=0,C33,""----""))"
      .Value = .Value
   End With

3) -------------- SetLookupDate --------------
  OLD/NEW CODE –
    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""

Here is a chart showing Before (Old code) and After (New code) results in seconds.

Again, must thank you as I am most appreciative of your input.
Steve K.

 

[EssKayKay]

You can apply the formula in one go (Excel will adjust the rows) and convert it to values if need be

    With Range("P33:P368")
        .Formula = "=IF(B33="""",""----"",IF(M33<=0,C33,""----""))"
        .Value = .Value
    End With

@offthelip will probably give you some array code later

Mark,
I tried to "mark" your suggestion as "answered" but could not find the check to do so.
Again, my thanks. . .
SKK

 

[MARK858]

You're welcome (you can only mark one reply as a solution )

 

[EssKayKay]

You're welcome (you can only mark one reply as a solution )

My BAD - I didn't realize I already marked one. I guess once marked you can't unmark. Oh well, thank you any way.
Your suggestions works great. I'm going to attempt to use that technique on #1 also. I suspect I won't succeed but I will try.

Much appreciated. . .
Steve

 

[MARK858]

You can unmark but I wouldn't bother, the advice from Offthelip is good advice