How much does setting these properties speed up your Excel macro: Application.ScreenUpdating, Application.DisplayAlerts
what is the point of doing these:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
does it really save tha开发者_运维知识库t much time?
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
Agree.
I have found that when you turn off ScreenUpdating
, Calculation
, it's best to think about how to do as much work (writes,reads,events,...) as possible for as few ScreenUpdating
calls in return. This will speed up operations while also providing the user with a better and more tolerable experience. Say, for example that you want to write some data to a sheet as fast as possible. You could do this:
For Each row In rowDic.Keys()
' turn off updating
for item in rowDic.Key(row)
... do some writes
Next
' turn on updating
Next
or to go faster you could 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 writing data, it's quickest to write larger chunks, fewer times. so the ideal number of writes, if any, is one. You can do this by treating a Range
as a 2D array[rows,cols]
. I have 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
Here, there are no loops, this optimises each individual task's time in the CPU which results in quicker processing times and in turn seems to make excel work normally (not crash).
HTH,
F
The improvement of screenUpdating depends largely on how your macro is written. It will be specially usefull with those horrible macros made by the recorder, full of unnecessary "select" and "activate".
Absolutely. I had a long-running macro several years ago that took almost a minute to run. I set ScreenUpdating to false and it finished in less than 5 seconds.
Just make sure you reset ScreenUpdating to true when you're finished running the macro.
I wouldn't use those for 'speed' but for 'function'
When you don't want the user to see the screen updating:
Application.ScreenUpdating = False
When you don't want the user to see every superfluous message from the app:
Application.DisplayAlerts = False
Especially the latter because no one wants to be bludgeoned to death with messages asking if you 'really' want to update, append or delete records. I prefer to shield the user from such stuff.
精彩评论