I noticed that excel 365 macros suddenly take much longer to run. Seems to be correlated with the latest update of excel done on february 3rd on my machine

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)

We have the same issue in my company, Waiting for a solution from MS Excel team

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

timfrewinI had this same problem. Suddenly, six days ago, a macro that I’ve been using for over ten years started taking 15 minutes to run instead of the normal 10 seconds.Introducing those few lines of code has fixed it 🙂Many, many thanks for sharing this … you’re a life saver.

Olivierb61 We are having the same problem in my company.If someone knows how to fix it please let us know.

Macros suddenly run much slower | Microsoft Community Hub

34 Replies

MichaelN1776
Copper Contributor
Feb 10, 2022
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.
- MichaelN1776
  Copper Contributor
  Feb 11, 2022
  Solved the problem by reverting back to an earlier version of Excel.
  https://support.microsoft.com/en-us/topic/how-to-revert-to-an-earlier-version-of-office-2bd5c457-a917-d57e-35a1-f709e3dda841
  - Steve_Pigsoft
    Copper Contributor
    Feb 14, 2022
    MichaelN1776
    
    Very many thanks for this recommendation (to work round the current version of Excel for Microsoft 365 (Win 10) that is currently very slow at running macros AND is also very slow to close after running macros, compared with previous Excel 365 versions):
    
    ie. To roll back to an earlier version of Excel 365 – I am pretty sure a roll back to an earlier version would fix the problem for me too. However:
    
    My current installed Microsoft Excel for Microsoft 365 is shown as
    MSO Version 2201 Build 16.014827.20180 (32 bit).
    
    As this is neither a version from Microsoft 365 Apps for enterprise nor Microsoft 365 Apps for business, I don’t think I am able to follow the path described via https://support.microsoft.com/en-us/topic/how-to-revert-to-an-earlier-version-of-office-2bd5c457-a917-d57e-35a1-f709e3dda841because - “This article doesn’t apply to MSI versions of Office.”
    
    Is there an equivalent rollback procedure for MSO versions of Microsoft 365? I couldn’t find one.
    
    Ideally, we all need a fix to the underlying problem with Excel 365's current macro-run-speeds as, even for those able to roll-back to earlier versions and with auto-update deselected, the roll-back fix is only a work-round and prevents ANY future updates to Office components? How do we ensure that this fault in the current releases of Excel is flagged to Microsoft for a fix in a later update release?
- Steve_Pigsoft
  Copper Contributor
  Feb 11, 2022
  I have the same problem: Current Excel for Microsoft 365 (Win 10) is now slow running macros AND is also very slow to close after running macros. These same files and macros still run fine on Excel 2007 and Excel 2010 on alternative Win 10 PCs, and the same macros ran fine on the Excel 365 PC UNTIL recent updates of Microsoft 365 / Excel 365 on that PC.
felixvs
Copper Contributor
Feb 08, 2022
Olivierb61
We are having the same problem in my company.
If someone knows how to fix it please let us know.

Forum Discussion

Macros suddenly run much slower

34 Replies

Resources