blocks|key|2448017|text|首先，我一直在使用由Richie+(UK)+Post+#7+Here编写的脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2448018|它简单地迭代了一个循环，改变了一个单元格中i的值。我稍微改变了它，所以它循环了10,000次，为了样本大小，我执行了10次。|2448019|屏幕更新对我的代码执行速度有什么影响？|2448020|以下是禁用和启用Screen+Updating时的执行长度：|style|CODE|2448021|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|code-block|syntax|javascript|2448022|如您所见，在未禁用Screen+Updating的情况下，执行代码所需的时间几乎是原来的3.5倍。|2448023|这两个代码都是使用VB编辑器中的Run按钮执行的，而不是“查看”电子表格。|2448024|代码的开头和结尾处有两行简单的代码：|2448025|Application.ScreenUpdating+=+False
Application.ScreenUpdating+=+True|2448026|但是，它们会对你的执行效率有很大的影响！|2448027|注意：显然，Screen+Updating的优势在这里是众所周知的，但这对初学者可能是有好处的，我发现查看这些数字很有趣！|BOLD|2448028|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ozgrid.com/forum/showthread.php?t=56900&p=293030#post293030^0|U|4|0|0|0|0|8|F|0|0|9|F|0|0|0|0|0|0|3|6|F|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@$A|1C|B|1D|1|1E]]|C|$]]|$1|D|3|E|5|6|7|1F|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|1G|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|1H|8|@$A|1I|B|1J|J|K]]|9|@]|C|$]]|$1|L|3|M|5|N|7|1K|8|@]|9|@]|C|$O|P]]|$1|Q|3|R|5|6|7|1L|8|@$A|1M|B|1N|J|K]]|9|@]|C|$]]|$1|S|3|T|5|6|7|1O|8|@]|9|@]|C|$]]|$1|U|3|V|5|6|7|1P|8|@]|9|@]|C|$]]|$1|W|3|X|5|N|7|1Q|8|@]|9|@]|C|$O|P]]|$1|Y|3|Z|5|6|7|1R|8|@]|9|@]|C|$]]|$1|10|3|11|5|6|7|1S|8|@$A|1T|B|1U|J|12]|$A|1V|B|1W|J|K]]|9|@]|C|$]]|$1|13|3|-4|5|6|7|1X|8|@]|9|@]|C|$]]]|14|$15|$5|16|17|18|C|$19|1A]]]]

Firstly I've been using the script written by Richie (UK) Post #7 <a href="http://www.ozgrid.com/forum/showthread.php?t=56900&amp;p=293030#post293030" rel="nofollow">Here</a>

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.

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

These are the lengths of execution when <code>Screen Updating</code> was disabled and enabled:

<pre><code>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
</code></pre>

As you can see it takes almost 3.5 times as long to execute the code when <code>Screen Updating</code> is not disabled. 

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

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

<pre><code>Application.ScreenUpdating = False
Application.ScreenUpdating = True
</code></pre>

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

Note: Obviously the advantages of <code>Screen Updating</code> will be well known to many here but this may be of benefit to beginners and I find it interesting to view the numbers!

blocks|key|4421941|text|如果您想要查看一个相当激烈的示例，说明为什么ScreenUpdating很重要，请运行以下代码。在Excel2011中，我在不使用ScreenUpdating+=+false的情况下运行此交换所需的时间大约要长45倍！这是一个巨大的时差。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4421942|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+%2B+(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+%2B+(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|code-block|syntax|javascript|4421943|4421944|此外，虽然我们在讨论提高效率的方法，但另一个关键点是Select、Selection和Activate很少(如果有的话)是必要的。当您记录宏时，它将始终使用这些宏，但很少情况下您需要在代码中实际使用它们。同样，标题中包含Active的任何内容(如ActiveCell)通常都表示代码速度较慢，因为您可能正在选择单元格。|4421945|您几乎总是可以明确地引用单元格/工作表，并避免select。例如：|4421946|msgbox+(Worksheets(1).Range("A1").value)+|4421947|无论您当前是否在第一个工作表上，都将工作。一个常见的新VBA错误是做一些更像这样的事情：|4421948|Worksheets(1).Select
msgbox+(Range("A1").value)|4421949|这是一个不必要的步骤。|4421950|这大大增加了代码运行时的时间。|4421951|entityMap^0|M|E|1T|M|0|0|0|Q|6|X|9|17|8|32|6|3F|A|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|17|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|N|3|O|5|6|7|1J|8|@]|D|@]|E|$]]|$1|P|3|Q|5|H|7|1K|8|@]|D|@]|E|$I|J]]|$1|R|3|S|5|6|7|1L|8|@]|D|@]|E|$]]|$1|T|3|U|5|H|7|1M|8|@]|D|@]|E|$I|J]]|$1|V|3|W|5|6|7|1N|8|@]|D|@]|E|$]]|$1|X|3|Y|5|6|7|1O|8|@]|D|@]|E|$]]|$1|Z|3|-4|5|6|7|1P|8|@]|D|@]|E|$]]]|10|$]]

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

<pre><code>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: " &amp; Format(Time - startTime, "hh:mm:ss") &amp; " 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 &amp; vbCrLf &amp; "Screen Updating IS disabled: " &amp; Format(Time - startTime, "hh:mm:ss") &amp; " seconds"

 'show results
 MsgBox results


End Sub
</code></pre>

<hr>

Also, while we're on the topic of ways to increase efficiency, another key point is that <code>Select</code>, <code>Selection</code>, and <code>Activate</code> are 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 <code>Active</code> in title (such as <code>ActiveCell</code>) normally is an indication you will have slower code because you presumably are selecting cells.

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

<pre><code>msgbox (Worksheets(1).Range("A1").value) 
</code></pre>

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

<pre><code>Worksheets(1).Select
msgbox (Range("A1").value)
</code></pre>

which is an unneeded step.

This adds significant time to code runtimes.

blocks|key|4422048|text|如果代码以导致屏幕内容更改的方式与Excel交互，则关闭屏幕更新只会对执行时间产生影响。屏幕更改量越大，影响就越大。其他张贴的答案恰如其分地证明了这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4422049|其他可以影响执行时间的应用程序设置是计算和事件处理。使用此代码模板作为起点(错误处理程序确保这些属性在sub结束时重新打开，即使它出错)|4422050|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|code-block|syntax|javascript|4422051|还有其他技术可以在执行速度方面提供更大的改进。|4422052|最有用的包括|4422053|4422054|尽可能避免使用Select、Activate和ActiveCell/Sheet/Workbook。而是声明和赋值变量并引用这些变量。|ordered-list-item|offset|length|style|CODE|4422055|当引用大范围时，将范围数据复制到变量数组中进行处理，并将结果复制回后面的范围中。|4422056|使用Range.SpecialCells、Range.Find和Range.AutoFilter来限制引用的单元格数量。|4422057|4422058|在SO上有很多这些技术的例子。|4422059|entityMap^0|0|0|0|0|0|0|7|6|E|8|N|P|0|0|2|I|L|A|W|G|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|15|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|16|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|17|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|19|8|@$Q|1A|R|1B|S|T]|$Q|1C|R|1D|S|T]|$Q|1E|R|1F|S|T]]|9|@]|A|$]]|$1|U|3|V|5|P|7|1G|8|@]|9|@]|A|$]]|$1|W|3|X|5|P|7|1H|8|@$Q|1I|R|1J|S|T]|$Q|1K|R|1L|S|T]|$Q|1M|R|1N|S|T]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1P|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|12|$]]

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. 

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)

<pre><code>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
</code></pre>

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

The most useful include

<ol>
<li>Avoid <code>Select</code>, <code>Activate</code> and <code>ActiveCell/Sheet/Workbook</code> as much as possible. Instead declare and assign variables and reference those.</li>
<li>When referencing large ranges, copy the Range data to a variant array for processing and copy the result back to the range after.</li>
<li>Use <code>Range.SpecialCells</code>, <code>Range.Find</code> and <code>Range.AutoFilter</code> to limit the number of cells referenced.</li>
</ol>

There are plenty of examples of these techniques on SO.

blocks|key|4422076|text|关于屏幕更新，有一件很重要的事情需要知道，这是我在之前的回答中没有看到的。从我自己的测试中，我发现关闭和打开屏幕更新大约需要15ms+(通过Excel+Interop在C#中测试)。如果你要执行任何需要更少时间的事情，请牢记这一点。毕竟，不要在某个循环中多次打开/关闭屏幕更新。这将是真正的性能杀手。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4422077|还有一个注意事项(你可能不想听到)，如果你想要它快速使用C%2B%2B。它通常比VBA快5到10倍(不要在这里抓住我，这取决于你真正做了什么)。|4422078|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

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.

blocks|key|4510845|text|我知道这是个陈词滥调，但是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4510846|但请记住将其设置回其旧的value.|4510847|Changing工作簿也会重置ScreenUpdating.|unordered-list-item|offset|length|style|CODE|4510848|
+|4510849|设置ScreenUpdating+=+true|ordered-list-item|4510850|4510851|entityMap^0|0|0|F|E|0|0|2|L|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@$G|V|H|W|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|X|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|Y|8|@$G|Z|H|10|I|J]]|9|@]|A|$]]|$1|P|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|R|$]]

I know it's an old thread, but:

<ol>
<li>Set <code>ScreenUpdating = true</code>, but remember to set it back to its old value.</li>
<li>Changing Workbooks will also reset <code>ScreenUpdating</code>.</li>
</ol>

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 <code>screen updating</code> and was thankful I'm not sensitive to flashing lights before thinking about it in more detail:

When I first started using <code>VBA</code> 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 <code>screen updating</code> really have on your codes execution time?

Effect of Screen Updating

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我一直在测量代码执行时间，以测量在本地执行脚本和在服务器上执行脚本的差异。有一次，我忘了禁用screen updating，幸好在考虑更多细节之前，我对闪光灯不敏感：当我第一次开始使用VBA时，我总是假设它只是被使用过，这样它就不会吓到最终用户，让他们认为他们的PC即将崩溃。当我开始阅读更多关于提高代码效率的文章时，我...

问屏幕更新的效果
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问屏幕更新的效果EN