屏幕更新的效果 [英] Effect of Screen Updating

问题描述

我一直在测量代码执行时间，以衡量本地和我的服务器上执行我的脚本之间的差异。有一次，我忘了禁用屏幕更新，感谢我对闪光灯不敏感，然后再考虑更多细节：

当我第一次开始使用 VBA 时，我一直认为它只是被使用，所以它并没有吓倒最终用户，认为他们的电脑即将崩溃。当我开始阅读更多的内容来提高代码的效率时，我了解了什么，但是您的代码执行时间真的在屏幕更新中有多少效果？ p>

解决方案

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

可以对执行时间产生影响的其他应用程序设置是计算和事件处理。使用此代码模板作为起点（错误处理程序确保这些属性在子结尾处被重新启动，即使出现错误）

  Sub YourSub（）
错误GoTo EH 
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 Application.EnableEvents = False 
 
'代码在这里
 
清理：
错误恢复下一步
 Application.ScreenUpdating = True 
 Application.Calculation = xlCalculationAutomatic 
 Application.EnableEvents = True 
退出子
 EH：
'执行错误处理
 GoTo CleanUp 
 End Sub 
  pre> 
 
 
存在可以提高执行速度的其他技术。
 
 
 
最有用的包括
 
 
 
 
 
避免选择，激活和 ActiveCell / Sheet / Workbook 。 
 
 
引用大范围时，将范围数据复制到变量数组进行处理，然后将结果复制回范围。
 
 
使用 Range.SpecialCells ， Range.Find 和 Range.AutoFilter 限制引用的单元格数量。
 
 
这些技术在SO上有很多例子。 / p> 
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 updating and was thankful I'm not sensitive to flashing lights before thinking about it in more detail:

When I first started using VBA I 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 updating really have on your codes execution time?
解决方案
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 grater the amount of screen changes the bigger the impact will be.  The other posted answers aptly demonstrate this. 

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 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
    GoTo CleanUp
End Sub
Other techniques exist that can provide even greater improvement in execution speed.

The most useful include

Avoid Select, Activate and ActiveCell/Sheet/Workbook as much as possible.  Instead declare and assign variables and reference those.
When referencing large ranges, copy the Range data to a variant array for processing and copy the result back to the range after.
Use Range.SpecialCells, Range.Find and Range.AutoFilter to limit the number of cells referenced.
There are plenty of examples of these techniques on SO.

                        
这篇关于屏幕更新的效果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！