Forum Discussion
Macros suddenly run much slower
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)
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
