Macros suddenly run much slower

Olivierb61 

I had this same problem. Something has clearly changed on MS's side. Some macros which used to take around 30 secs were taking anywhere from 20 mins to 2.5 hours. This was a sudden change early last week. However, we've found a fix. Once we disabled automatic formula recalculation during execution of the macro, everything went back to normal. If anything, faster than before.

At the beginning of each function:

Application.Calculation = xlCalculationManual

At the end of each function:

Application.Calculation = xlCalculationAutomatic

Or, a better way, if you want to preserve the user settings of automatic vs manual calculation following completion of the macro:

Sub x()
 lCalc = Application.Calculation
 
 With Application
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
 .DisplayAlerts = False
 
 ''///your code here
 
 .Calculation = lCalc
 .ScreenUpdating = True
 .DisplayAlerts = True
 End With
End Sub

(Credit to user royUK on https://www.ozgrid.com/ forum for that addition and code snippet) 

Olivierb61 

It's only taken 2 years but I finally found the issue for us. We have a very large workbook for production planning that we've been using for 15 years, and around early 2022 it slowed way down, and got worse and worse. It turns out in one of the worksheets, the conditional formatting rules had been duplicated 17,000+ times.

What's strange is that no macros run to change anything on that worksheet, although they do reference data from it. I looked at a Dec 2021 copy of the workbook and the worksheet only had the original dozen or so conditional formatting rules. I found a reference on another forum that the duplication of conditional formatting rules is a recent bug in Excel - it happens when deleting/copying worksheets within a workbook (even though for us the worksheet with the problem is never deleted or copied, although others are every week).

I was unable to delete the conditional formats from the worksheet using the GUI...Excel would just crash. I was able to do it with the following code. I adjusted the loop parameters to delete 5000 conditional formats each time I ran it, took about 30 minutes to do each 5000. But workbook macros are back to normal speed and don't seem to be duplicating now. 

Sub Remove_New_Formatting()
    Dim i As Long

    Sheets("<worksheet name>").Activate

    With ActiveSheet.Cells.FormatConditions
        For i = .Count To 3 Step -1
            .Item(i).Delete
        Next i
    End With

End Sub

Olivierb61 

We are having the same problem in my company.

If someone knows how to fix it please let us know.

I have the same problem.

I have a large spreadsheet with one macro that takes about 10 minutes to run. When I open the spreadsheet, Excel is using about 400MB of RAM. As the macro runs, the amount of RAM increases. When it's finished, Excel is using about 1,600MB of RAM. After that, every action is Excel spikes the CPU up to 100%.

Looks to me like a recent update introduced some sort of memory release issue.

I've run into the same issue. Macros that used to take ~5 minutes to complete now take hours. I've instrumented my code and it appears that the slowdown is primarily related to copying data between cells. Turning off autocalculation has not yielded improvement. 

I found that disabling the Hardware Graphic Acceleration solved this problem for me. Olivierb61 

Olivierb61 I have experienced in similar situation. Prior to running Macro in Excel (2021 & Office 365), my 16G DDR4 RAM would more than suffice. After I have started running Macro (simple ones too), RAM usage suddenly spiked to 100%, and that caused my PC to run extremely slowly, which I was not expecting with a Ryzen 9 CPU & RTX306 GPU. So, I simply upgraded to 32G DDR4 RAM, and it did not help much, which is ridiculous. My MB can support up to 128G DDR4 RAM, but I am not going to spend a few hundred dollars to upgrade to 128G RAM, just so that I can run Excel Macro. I doubt the 128G RAM would help the situation anyways. MS should make it clear the size of RAM is needed to run Macro in Excel without slowing down PC speed. 

Olivierb61 

I had this very problem, where my 4-year-old Windows 10 laptop would process a macro I wrote in 2.5 minutes, vs. my new Windows 11 laptop taking 40+ minutes. The specs on my new laptop are much better, which left me baffled. 

 After not finding a solution online, I started to compare my Windows 10 Laptop settings with the Windows 11 and what I found was the Enable VBA macros (Not recommended; potentially dangerous code can run) were selected for my Windows 10 system but not 11, so after making my change, I went from a 40+ minute run time down to 5 or so minutes. Still not as good as my old Windows 10 laptop, but a lot better than 40+ minutes. 

For those that are unsure where to find the setting navigate to the following: 

File >> Options >> Trust Center Settings... >> Macro Settings >> Select the Enable VBA Macros (not recommended; potentially dangerous code can run) radio button >> OK >> OK

All Done!