How many settings for these properties are accelerated by the Excel macro: Application.ScreenUpdating, Application.DisplayAlerts

Question

What's the point:

Application.ScreenUpdating = False Application.DisplayAlerts = False 

Does it really save a lot of time?

Answer 1

I agree.

I found that when you turn off ScreenUpdating , Calculation , it’s better to think about how to do as much work (recording, reading, events, ...) as possible in a few calls of ScreenUpdating in return. This will speed up operations and also provide the user with a more convenient and more tolerant experience. Say, for example, that you want to write some data on a sheet as quickly as possible. You can do it:

 For Each row In rowDic.Keys() ' turn off updating for item in rowDic.Key(row) ... do some writes Next ' turn on updating Next 

or faster, you can do this:

 ' turn off updating For Each row In rowDic.Keys() for item in rowDic.Key(row) ... do some writes Next Next ' turn on updating 

Similarly, when recording data, it is faster to write large chunks, less time. therefore, the ideal number of records, if any, is one. You can do this by treating Range as a 2D array[rows,cols] . I found the following to be effective:

 ' turn off updates ' Organise data in ram so that it fits the range for which it is meant Dim two_d_arr (rows,cols) loadDataFromSource two_d_arr Dim destinationRange as Range destinationRange = Sheets(someSheet).Range(someRange).Value = two_d_arr Redim two_d_arr(0,0) ' !!! RELEASE MEMORY ' turn on updates 

There are no cycles, this optimizes each individual task time in the CPU, which leads to faster processing time and, in turn, it seems that excel is working fine (not a failure).

NTN

F

Answer 2

It depends on how much you actually update on the screen as part of your code (i.e. the number of updated cells) and how many sheets are there, how many sheets / cells refers to the sheet whose code is updated, and how many formulas are present throughout the book.

Every time you change something in a worksheet, Excel recalculates all formulas. Therefore, if your code does not update too many sheets / cells, but your book has many formulas, then turning off the screen update may not help you at all.

Another thing to consider for execution is the Calculation property; set this to xlCalculationManual to turn off automatic repeats and put it back into xlCalculationAutomatic at the end.

 Application.Calculation = xlCalculationManual 

Another thing to consider is events, if one or more of these sheets have Worksheet_Chnage or Worksheet_Calculate event handlers, every change that your code makes calls them and your code has to wait until they return. Therefore, disable it during your code.

 Application.EnableEvents = False 

In most of my codes, I apparently 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 

Answer 3

Improving screenUpdating depends a lot on how your macro is written. This will be especially useful with those terrible macros that make the recorder full of unnecessary “select” and “activate”.

Answer 4

That's right. A few years ago I had a multi-year macro that took almost a minute. I set ScreenUpdating to false and completed it in less than 5 seconds.

Just make sure you reset ScreenUpdating is correct when you finish working with the macro.

Answer 5

I would not use them for "speed", but for "function"

If you do not want the user to see the screen update:

 Application.ScreenUpdating = False 

If you do not want the user to see every extra message from the application:

 Application.DisplayAlerts = False 

Especially the latter, because no one wants to be killed by messaging, asking if you really want to update, add or delete records. I prefer to protect the user from such things.