How much does setting these properties speed up your Excel macro: Application.ScreenUpdating, Application.DisplayAlerts

Question

what is the point of doing these:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

does it really save that much time?

Answer 1

It depends on how much you are actually updating on the screen as part of your code, (i.e. number of cells updated), and how many sheets are there, how many sheets/cells refer to sheet your code is updating and how many formulas are present in the whole workbook.

Each time you change some thing in the sheet, Excel re-calculates all formulas. So if your code is not updating too many sheets/cells but your workbook has many formulas then turning off the screen update might not help you at all.

One more thing to consider for perfomance is Calculation property, set this to xlCalculationManual to turnn off the auto recals and turn it back to xlCalculationAutomatic at the end.

Application.Calculation = xlCalculationManual

One more thing to conside is Events, if one or more of those sheets have Worksheet_Chnage or Worksheet_Calculate event handlers each change that your code is doing will trigger them and your code need to wait till they return. So turn it off during your code.

Application.EnableEvents = False

In most of my code, I usually use this

On Error GoTo lblError
Dim bEvents As Boolean, iCalc As Integer, bScrnUpd As Boolean
bEvents = Application.EnableEvents
iCalc = Application.Calculation
bScrnUpd = Application.ScreenUpdating
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'-----------------------My code

Application.EnableEvents = bEvents
Application.Calculation = iCalc
Application.ScreenUpdating = bScrnUpd
Exit Sub

'reset them even if you are exiting due to error
lblError:
Application.EnableEvents = bEvents
Application.Calculation = iCalc
Application.ScreenUpdating = bScrnUpd
Debug.print Err.Description