vba 屏幕更新的效果

Effect of Screen Updating

提问by Alistair Weir

I have been playing around measuring code execution times to gauge differences between executing my scripts locally and on my server. At one point I forgot to disable screen updatingand was thankful I'm not sensitive to flashing lights before thinking about it in more detail:

我一直在尝试测量代码执行时间，以衡量在本地和服务器上执行脚本之间的差异。有一次我忘了禁用screen updating，很庆幸在更详细地考虑之前我对闪光灯不敏感：

When I first started using VBAI always assumed it was just used so that it didn't scare end users into thinking their PC was about to crash. When I started reading more into improving the efficiency of your code I understood what it was for but how much of an effect does screen updatingreally have on your codes execution time?

当我第一次开始使用时，VBA我总是认为它只是被使用过，所以它不会吓到最终用户认为他们的 PC 即将崩溃。当我开始阅读更多关于提高代码效率的内容时，我明白了它的用途，但screen updating对代码执行时间有多大影响？

回答by chris neilsen

Turning off screen updating will only make a difference to execution time if the code interacts with Excel in a way that causes changes to the screen content. The greater the amount of screen changes the bigger the impact will be. The other posted answers aptly demonstrate this.

如果代码以导致屏幕内容更改的方式与 Excel 交互，则关闭屏幕更新只会对执行时间产生影响。屏幕变化量越大，影响越大。其他发布的答案恰当地证明了这一点。

Other application settings that can make a difference to execution time are Calculation and Event handling. Use this code template as a starting point (the error handler ensures that these properties are turned back on at the end of the sub, even if it errors)

其他可以影响执行时间的应用程序设置是计算和事件处理。使用此代码模板作为起点（错误处理程序确保在 sub 结束时重新打开这些属性，即使它出错）

Sub YourSub()
    On Error GoTo EH

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' Code here

CleanUp:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
Exit Sub
EH:
    ' Do error handling
    Resume CleanUp
End Sub

Other techniques exist that can provide even greater improvement in execution speed.

存在其他技术，可以在执行速度方面提供更大的改进。

The most useful include

最有用的包括

1. Avoid Select, Activateand ActiveCell/Sheet/Workbookas much as possible. Instead declare and assign variables and reference those.
2. When referencing large ranges, copy the Range data to a variant array for processing and copy the result back to the range after.
3. Use Range.SpecialCells, Range.Findand Range.AutoFilterto limit the number of cells referenced.

1. 避免Select，Activate并ActiveCell/Sheet/Workbook尽可能。而是声明和分配变量并引用它们。
2. 引用大范围时，将范围数据复制到变体数组进行处理，然后将结果复制回范围。
3. 使用Range.SpecialCells,Range.Find和Range.AutoFilter来限制引用的单元格数量。

There are plenty of examples of these techniques on SO.

在 SO 上有很多关于这些技术的例子。

回答by enderland

If you want to see a fairly drastic example of why ScreenUpdatingis important, run the following code. It takes roughly 45 times longer in Excel 2011 for me to run this swap without ScreenUpdating = false! This is a hugedifference in time.

如果你想看到一个相当激烈的例子来说明为什么ScreenUpdating很重要，请运行以下代码。在 Excel 2011 中，我在没有ScreenUpdating = false! 这是一个巨大的时间差异。

Sub testScreenUpdating()

    Dim i As Integer
    Dim numbSwitches As Integer
    Dim results As String

    'swap between sheets this number of times
    numbSwitches = 1000

    'keep track of time
    Dim startTime As Double
    startTime = Time

    'swap between sheets 1/2 (need both sheets or this will crash)
    For i = 1 To numbSwitches
        Sheets(1 + (i Mod 2)).Select
    Next i

    'get results
    results = "Screen Updating not disabled: " & Format(Time - startTime, "hh:mm:ss") & " seconds"
    startTime = Time

    'scenario 2 - screenupdating disabled

    Application.ScreenUpdating = False

    'swap between sheets 1/2 (need both sheets or this will crash)
    For i = 1 To numbSwitches
        Sheets(1 + (i Mod 2)).Select
    Next i

    Application.ScreenUpdating = True

    'get results for part two
    results = results & vbCrLf & "Screen Updating IS disabled: " & Format(Time - startTime, "hh:mm:ss") & " seconds"

    'show results
    MsgBox results


End Sub

---

---

Also, while we're on the topic of ways to increase efficiency, another key point is that Select, Selection, and Activateare rarely (if ever) necessary. When you record macros it will always use these but there are very few situations when you need to actually use them in code. Likewise, anything with Activein title (such as ActiveCell) normally is an indication you will have slower code because you presumably are selecting cells.

此外，虽然我们对方式，提高办事效率的话题，另一个关键的一点是Select，Selection和Activate很少（如果有的话）必要的。当您录制宏时，它总是会使用这些宏，但很少有需要在代码中实际使用它们的情况。同样，Active标题中的任何内容（例如ActiveCell）通常都表明您的代码会较慢，因为您可能正在选择单元格。

You can almost always refer to cells/worksheets specifically and avoid select. For example:

您几乎总是可以专门引用单元格/工作表并避免选择。例如：

msgbox (Worksheets(1).Range("A1").value) 

will work regardless of whether you are currently on the first worksheet. A common new VBA mistake is to do something more like:

无论您当前是否在第一个工作表上，都将起作用。一个常见的新 VBA 错误是执行以下操作：

Worksheets(1).Select
msgbox (Range("A1").value)

which is an unneeded step.

这是一个不必要的步骤。

This adds significant time to code runtimes.

这为代码运行时增加了大量时间。

回答by Alistair Weir

Firstly I've been using the script written by Richie (UK) Post #7 Here

首先，我一直在使用 Richie (UK) Post #7 Here写的脚本

It simply iterates through a loop changing the value of i in one cell. I have changed it slightly so it loops 10,000 times and I execute it 10 times for sample size.

它只是在一个循环中迭代更改 i 在一个单元格中的值。我对其进行了轻微更改，因此它循环了 10,000 次，并针对样本大小执行了 10 次。

What is the effect of screen updating on the speed of my codes execution?

屏幕更新对我的代码执行速度有何影响？

These are the lengths of execution when Screen Updatingwas disabled and enabled:

这些Screen Updating是禁用和启用时的执行长度：

Disabled    Enabled
0.61909653  2.105066913
0.619555829 2.106865363
0.620805767 2.106866315
0.625528325 2.102403315
0.625319976 2.0991179
0.621287448 2.105103142
0.621540236 2.101392665
0.624537531 2.106866716
0.620401789 2.109004449

As you can see it takes almost 3.5 times as long to execute the code when Screen Updatingis not disabled.

如您所见，在Screen Updating未禁用时执行代码所需的时间几乎是其 3.5 倍。

Both of these codes were exceuted using the Run button in the VB editor, as opposed to 'watching' the spreadsheet.

这两个代码都是使用 VB 编辑器中的“运行”按钮执行的，而不是“观看”电子表格。

2 simple lines at the start and end of your code:

代码开头和结尾的 2 行简单代码：

Application.ScreenUpdating = False
Application.ScreenUpdating = True

But, they can have a large effect on the efficiency of your execution!

但是，它们会对您的执行效率产生很大影响！

Note:Obviously the advantages of Screen Updatingwill be well known to many here but this may be of benefit to beginners and I find it interesting to view the numbers!

注意：显然，Screen Updating这里的许多人都知道的优点，但这可能对初学者有益，而且我发现查看数字很有趣！

回答by Sekory

There is one important thing to know about screen updating which I didn't see in any previous answer. From my own test I find out that turning screen updating off and on takes about 15ms (tested in C# via Excel Interop). Keep that on mind if you will execute anything which would take less time. And after all don't turn screen updating on/off many times in some loop. That would be real performance killer.

关于屏幕更新，有一件重要的事情需要了解，这是我在之前的任何答案中都没有看到的。从我自己的测试中，我发现关闭和打开屏幕更新大约需要 15 毫秒（通过 Excel 互操作在 C# 中测试）。如果您将执行任何需要更少时间的操作，请记住这一点。毕竟不要在某个循环中多次打开/关闭屏幕更新。那将是真正的性能杀手。

And one more note (which you probably don't want to hear) if you want it quick use C++. It is typically 5 to 10 times quicker (don't catch me here it depends on what you really do) than VBA.

如果您想快速使用 C++，还有一个注意事项（您可能不想听到）。它通常比 VBA 快 5 到 10 倍（不要在这里抓住我，这取决于你真正做什么）。

回答by Frank

I know it's an old thread, but:

我知道这是一个旧线程，但是：

1. Set ScreenUpdating = true, but remember to set it back to its old value.
2. Changing Workbooks will also reset ScreenUpdating.

1. Set ScreenUpdating = true，但请记住将其设置回其旧值。
2. 更改工作簿也将重置ScreenUpdating。