vba Excel - 屏幕更新时屏幕闪烁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15615978/
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
Excel - Screen flicker on screen update
提问by ZorleQ
I've got yet another question from the long serries of 'Stop excel screen from flickering', but I think this one is a bit more tricky.
我从“停止闪烁的 excel 屏幕”的长系列中还有另一个问题，但我认为这个问题有点棘手。
I've got a client who requires a 'progress' bar on his spreadsheet during macro execution.
What I've done, is I've a pie chart linked to 2 cells which I adjust based on the progress. For performance, I've disabled events, ScreenUpdatingand changed calculation to manual.
我有一个客户在宏执行期间需要在他的电子表格上有一个“进度”栏。我所做的是，我有一个饼图链接到我根据进度调整的 2 个单元格。为了性能，我禁用了events，ScreenUpdating并更改了calculation to manual.
Then, once in a while I quickly toggle ScreenUpdating on and offto actually update the progress chart. This works like a charm every time I use it (more than 30 projects now).
然后，偶尔我会快速切换ScreenUpdating on and off以实际更新进度表。每次我使用它（现在超过 30 个项目）时，这就像一个魅力。
Recently, the client came back to me with a 'big' problem. Whenever I do the quick toggle to refresh the progress chart, the entire excel screen (including all the items on it)flickers, which drives the guy crazy.
最近，客户带着“大”问题回来找我。每当我快速切换以刷新进度表时，整个 excel 屏幕（包括其上的所有项目）都会闪烁，这让这家伙发疯了。
Has anyone got any idea on what could be done with this to keep the chart updating while keeping the front page constantly displayed (the macro jumps between pages) without screen flickering? Something like double buffering or so...
有没有人知道可以用它做什么来保持图表更新，同时保持首页不断显示（宏在页面之间跳转）而不会出现屏幕闪烁？像双缓冲之类的东西......
SOLUTION
解决方案
Believe it or not, but I just found a solution. This was so so so easy. In VBA, it's possible to refresh just a specific object, while the remaining screen remains 'locked'. For example:
信不信由你，但我刚刚找到了一个解决方案。这太容易了。在 VBA 中，可以只刷新特定对象，而其余屏幕保持“锁定”状态。例如：
ThisWorkbook.Worksheets("Welcome").ChartObjects(1).Chart.Refresh
Thank you everyone for your input.
感谢大家的投入。
采纳答案by David Zemens
You may be able to rewrite the macro so that it doesn't "jump between pages" (since it's usually not necessary to activateworksheets in order to manipulate their data. Otherwise, either a user form or the application's StatusBarcould be used to display a progress bar:
您可以重写宏，使其不会“在页面之间跳转”（因为通常不需要activate工作表来操作其数据。否则，可以使用用户表单或应用程序StatusBar来显示进度酒吧：
Sub StatusBar()
Dim pct As Double
Dim msg as String
Application.ScreenUpdating = False
For i = 10000 To 1 Step -1
pct = (10000 - i) / 10000
msg = Format(pct, "0.0%") & " complete"
Application.StatusBar = msg
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Here is a revised example with UserForm. Note that you will need to add the UserForm module, and at least one Label to it in order to present the information:
这是一个带有 UserForm 的修订示例。请注意，您需要添加 UserForm 模块和至少一个 Label 以显示信息：
Sub StatusBar()
Dim pct As Variant
DoEvents
UserForm1.Show vbModeless
Application.ScreenUpdating = False
For i = 10000 To 1 Step -1
pct = (10000 - i) / 10000
If Int((i / 10000) * 100) = (i / 10000) * 100 Then
msg = Format(pct, "0%")
UserForm1.Label1 = msg & " complete"
End If
Next
UserForm1.Label1 = "Finished!"
Application.ScreenUpdating = True
End Sub
Problem might be that, depending on how quickly your macro(s) execute, the progress indicator may appear to jump from 0 to 100%. You can do graphical progress bars, too, but the basic idea behind it would be the same, just instead of updating a label value, you'd be changing the size of a textbox with a differing background color, etc.
问题可能在于，根据宏执行的速度，进度指示器可能会从 0 跳到 100%。您也可以制作图形进度条，但其背后的基本思想是相同的，只是不是更新标签值，而是更改具有不同背景颜色的文本框的大小等。
Revised per OP comments
根据 OP 评论修订
This is how you can "copy" a range from one sheet to another sheet without "copy/paste":
这是您无需“复制/粘贴”即可将范围从一张纸“复制”到另一张纸的方法：
Set rng1 = Sheets(1).Rows(12)
Set rng2 = Sheets(2).Rows(1)
rng2.Value = rng1.Value
So, you can just define your ranges, and assign the second range to = the first range.
因此，您可以定义您的范围，并将第二个范围分配给 = 第一个范围。
