vba 设置这些属性对 Excel 宏的速度有多大影响：Application.ScreenUpdating、Application.DisplayAlerts
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2922939/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How much does setting these properties speed up your Excel macro: Application.ScreenUpdating, Application.DisplayAlerts
提问by l--''''''---------''''''''''''
what is the point of doing these:
做这些有什么意义：
Application.ScreenUpdating = False
Application.DisplayAlerts = False
does it really save that much time?
真的能节省那么多时间吗？
采纳答案by Frank
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 ScreenUpdatingcalls 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:
我发现当你关闭ScreenUpdating, 时Calculation，最好考虑如何做尽可能多的工作（写、读、事件，...），以尽可能少的ScreenUpdating调用作为回报。这将加快操作速度，同时也为用户提供更好、更容易接受的体验。例如，您想尽快将一些数据写入工作表。你可以这样做：
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 Rangeas a 2D array[rows,cols]. I have found the following to be effective:
同样，在写入数据时，写入更大的块最快，次数更少。所以理想的写入次数（如果有的话）是一次。您可以通过将 aRange视为 2D来做到这一点array[rows,cols]。我发现以下方法是有效的：
' 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).
在这里，没有循环，这优化了每个单独任务在 CPU 中的时间，从而缩短了处理时间，反过来似乎使 excel 正常工作（而不是崩溃）。
HTH,
哈，
F
F
回答by Adarsha
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.
每次更改工作表中的某些内容时，Excel 都会重新计算所有公式。因此，如果您的代码没有更新太多的工作表/单元格，但您的工作簿有很多公式，那么关闭屏幕更新可能根本无法帮助您。
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.
要考虑性能的另一件事是计算属性，将其设置为 xlCalculationManual 以关闭自动重新计算并在最后将其转回 xlCalculationAutomatic。
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.
需要考虑的另一件事是事件，如果这些工作表中的一个或多个具有 Worksheet_Chnage 或 Worksheet_Calculate 事件处理程序，您的代码所做的每个更改都会触发它们，您的代码需要等到它们返回。所以在你写代码的时候把它关掉。
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
回答by Patrick Honorez
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".
screenUpdating 的改进很大程度上取决于您的宏是如何编写的。它将特别有用记录器制作的那些可怕的宏，充满了不必要的“选择”和“激活”。
回答by Michael Todd
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.
绝对地。几年前，我有一个长时间运行的宏，运行时间将近一分钟。我将 ScreenUpdating 设置为 false，它在不到 5 秒的时间内完成。
Just make sure you reset ScreenUpdating to true when you're finished running the macro.
只需确保在完成宏运行后将 ScreenUpdating 重置为 true。
回答by Chris Hayes
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.
尤其是后者，因为没有人想被询问您是否“真的”想要更新、追加或删除记录的消息敲死。我更喜欢让用户远离这些东西。
