Forum Discussion

Olivierb61's avatar
Olivierb61
Copper Contributor
Feb 08, 2022

Macros suddenly run much slower

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
Macros and VBA
timfrewin's avatar
timfrewin
Copper Contributor
Feb 14, 2022

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) 

  • ChrisWELLth's avatar
    ChrisWELLth
    Copper Contributor
    Aug 16, 2024

    timfrewin I have zero idea how whoever figured this out but I just got a brand new computer and my macros were running soooo slow.  Put in these two code snippets and boom, fixed!  Thank you so much!

  • DiggyWig's avatar
    DiggyWig
    Copper Contributor
    Mar 28, 2024
    timfrewin
    I 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.
  • Massimo069's avatar
    Massimo069
    Copper Contributor
    Mar 22, 2024

    timfrewin 

    i read this post and applied the solution to my macro, as result took less than 1 sec to execute despite 15-20 sec without the command described.

    this is the place vhere i applied it:

    at the beginning

    Public Sub name_of_my_macro()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ...all the code

     

    and at the end

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

     

    Thank you so much, you saved me

  • MaartenMeerman's avatar
    MaartenMeerman
    Copper Contributor
    Feb 03, 2023
    Don't forget to add calculate to the code as needed in places if you change cells and need results to be used elsewhere before the macro finishes
  • Steve_Pigsoft's avatar
    Steve_Pigsoft
    Copper Contributor
    Feb 14, 2022
    Olivierb61 Thanks for the suggestion which worked for you ( Application.Calculation = xlCalculationManual + At the end of each function: Application.Calculation = xlCalculationAutomatic). Unfortunately, this does not fix the problem for me. I tried it - There are problems elsewhere in MS's code for latest Excel 365 releases. Their code needs fixing. Still, glad to hear that your macros are now back to speed

Resources