vba Excel ScreenUpdating False 并且屏幕仍然闪烁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21721513/
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 ScreenUpdating False and still flickering screen
提问by Matth
I have the following simple code to close a range of open workbooks. I have just switched to Excel 2013 and in this new version my screen keeps flashing a white window in Excel for each workbook that is unhidden.
我有以下简单的代码来关闭一系列打开的工作簿。我刚刚切换到 Excel 2013，在这个新版本中，对于每个未隐藏的工作簿，我的屏幕在 Excel 中不断闪烁一个白色窗口。
How can I get that annoying screen flicker to shut off?
我怎样才能让烦人的屏幕闪烁关闭？
Sub CloseFiles()
On Error Resume Next
Application.ScreenUpdating = False
Application.StatusBar = "Please wait while files are closed."
Application.DisplayAlerts = False
Dim rCell As Range
For Each rCell In Range("Files")
Application.StatusBar = "Closing file " & rCell.Value
If rCell.Value <> "" Then
Windows(rCell.Value).Visible = True
Workbooks(rCell.Value).Close SaveChanges:=True
End If
Next rCell
Application.WindowState = xlMaximized
Windows("Filename.xlsm").Activate
Application.DisplayAlerts = True
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
回答by Prof. Juan Manuel Alonso D.
Having read most of the answers with potential solutions, I'm sorry to tell you none of them worked for me when trying to stop the flickering at the moment of making a worksheet visible/invisible.
阅读了大部分带有潜在解决方案的答案后，很遗憾地告诉您，在使工作表可见/不可见时尝试停止闪烁时，没有一个对我有用。
Then, it came up to me that the flickering could be caused by the automatic recalculation of the workbook, so I gave it a try, AND IT WORKED!
然后，我想到闪烁可能是由工作簿的自动重新计算引起的，所以我试了一下，它奏效了！
This is what I'm using:
这是我正在使用的：
Private Sub StopFlickering (ws As Worksheet)
Application.Calculation = xlManual
Application.ScreenUpdating = False
'Make the worksheet visible/invisible according to previous condition
'Instead, use "= True" or "= False", if it is the specific case
ThisWorkbook.Worksheets(ws.Name).Visible = _
Not ThisWorkbook.Worksheets(ws.Name).Visible
'(any other code in here)
'Don't forget to restore previous settings
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
Example to use it from anywhere in the workbook:
从工作簿中的任何位置使用它的示例：
StopFlickering ThisWorkbook.Worksheets("Worksheet Name")
I hope it works not just for me, but for anyone who gives it a try. Good luck, and let me know.
我希望它不仅适用于我，也适用于任何尝试过它的人。祝你好运，让我知道。
回答by jblood94
Use WindowStatein combination with DisplayAlerts. The user won't see the window minimize, but it will also keep Excel from flickering during a SaveAs, changing window visibility, or changing workbook/worksheet protection.
使用WindowState与组合DisplayAlerts。用户不会看到窗口最小化，但它也可以防止 Excel 在SaveAs更改窗口可见性或更改工作簿/工作表保护期间闪烁。
Dim iWindowState as Integer
With Application
.ScreenUpdating = False
.DisplayAlerts = False
iWindowState = .WindowState
.WindowState = xlMinimized
End With
'Flickery code
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.WindowState = iWindowState
End With
回答by Excelerator_Marc
I have determined the easiest way to solve this is to call the code from a separate subroutine.
我已确定解决此问题的最简单方法是从单独的子例程中调用代码。
Sub startcode()
Application.ScreenUpdating = False
Call myrunningsub()
Application.ScreenUpdating = True
End Sub
Sub myrunningsub()
'your code here
End Sub
This seems to work in binding the Application.ScreenUpdatingparameter. Note I use Excel 2013 in Windows 7 64 bit.
这似乎适用于绑定Application.ScreenUpdating参数。注意我在 Windows 7 64 位中使用 Excel 2013。
回答by Dean Meyer
Protecting/unprotecting a sheet activates the sheet, despite the setting for Application.ScreenUpdating. This was the cause for my flicker.
尽管设置了 Application.ScreenUpdating，但保护/取消保护工作表会激活工作表。这就是我闪烁的原因。
回答by whytheq
As Siddharth mentioned (I was just a second behind) ....why bother making the books visible - just close each and save changes.
正如 Siddharth 提到的（我只落后一秒钟）......为什么要让这些书可见 - 只需关闭每本书并保存更改。
Couple of other points:
1. I used to play around with the applications StatusBarbut don't bother anymore - too many times where the application crashes mid-procedure and the bar is left with an unwanted message on it!
2. Is On Error Resume Nextrequired in the flow of the program e.g. are you using it to deliberately hit an error and then move on to the next line of code? If not then just hiding an error can be dangerous....sometimes best to let programs error and then you know where you stand and can then fix the problem.
其他几点：
1. 我曾经玩过应用程序，StatusBar但不再打扰 - 太多次应用程序在过程中崩溃并且栏上留下不需要的消息！
2.On Error Resume Next程序流程中是否需要，例如您是否使用它来故意遇到错误，然后转到下一行代码？如果不是，那么仅仅隐藏错误可能很危险......有时最好让程序出错，然后你知道你的立场，然后可以解决问题。
Sub CloseFiles()
On Error Resume Next
Application.ScreenUpdating = False
Application.StatusBar = "Please wait while files are closed."
Application.DisplayAlerts = False
Dim rCell As Range
For Each rCell In Range("Files")
Application.StatusBar = "Closing file " & rCell.Value
If rCell.Value <> "" Then
'Windows(rCell.Value).Visible = True '::::::::why bother with this?
Workbooks(rCell.Value).Close SaveChanges:=True
End If
Next rCell
Application.WindowState = xlMaximized
Windows("Filename.xlsm").Activate
Application.DisplayAlerts = True
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
回答by Patrick Lepelletier
i Had the same "problem", and i use this kind of code (i add a doevents, and a .displaystatusbar=true)
我有同样的“问题”，我使用这种代码（我添加了一个 doevents 和一个 .displaystatusbar=true）
Sub CloseFiles()
err.clear
On Error Resume Next
with Application
.ScreenUpdating = False
.displaystatusbar = true 'kinda need this line
.StatusBar = "Please wait while files are closed."
doevents 'magic trick
.DisplayAlerts = False
.calculation= xlManual 'sometimes excel calculates values before saving files
.enableevents=false 'to avoid opened workbooks section open/save... to trigger
end with
'code
with application
.StatusBar = False
.displaystatusbar = false
.DisplayAlerts = True
.ScreenUpdating = True
.enableevents=true
.calculation= xlAutomatic
end with
End Sub
