vba 如何防止 Excel 中的窗口闪烁/切换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24043988/
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
How to prevent window flickering/switching in Excel?
提问by FlouksBerlin
I am copying from one file to an other file, and I can see Excel switching between the source and the target file (like flickering). I want the macro to copy from the source and paste to target WITHOUT switching between the files (I DON'T want flickering).
我正在从一个文件复制到另一个文件，我可以看到 Excel 在源文件和目标文件之间切换（如闪烁）。我希望宏从源复制并粘贴到目标而不在文件之间切换（我不想闪烁）。
So here I got my Excel VBA code. I set up a Button to run 2 macros. The first macro is an openfiledialog where I can choose a file to be opened. That is required because the needed file always got a different name and is in a different path.
所以在这里我得到了我的 Excel VBA 代码。我设置了一个按钮来运行 2 个宏。第一个宏是一个 openfiledialog，我可以在其中选择要打开的文件。这是必需的，因为所需的文件总是有不同的名称并且位于不同的路径中。
After opening the file my second macro starts (CopyPasteValues), the one I posted below. At first I set my source and target Workbooks/Worksheets and create an array with 16 account numbers.
打开文件后，我的第二个宏开始 (CopyPasteValues)，我在下面发布的那个。首先，我设置了源和目标工作簿/工作表，并创建了一个包含 16 个帐号的数组。
Then I use the find method to search for the account number in each file (source and target). The result of the find method is used to create an offset in the src file and copy it to an offset in the target file.
然后我使用 find 方法在每个文件（源和目标）中搜索帐号。find 方法的结果用于在 src 文件中创建一个偏移量，并将其复制到目标文件中的一个偏移量。
Sub CopyPasteValues()
Dim srcWb As Workbook 'source Wb
Dim srcWs As Worksheet 'source Ws
Dim trgWb As Workbook 'target Wb
Dim trgWs As Worksheet 'target Ws
Set trgWb = ActiveWorkbook
Set trgWs = trgWb.Sheets("Entry Sheet 20004100")
Set srcWb = Workbooks.Open(Filename:=openedFile, UpdateLinks:=False, ReadOnly:=True, Editable:=False)
Set srcWs = srcWb.Sheets("20004100")
Dim GLAccountField
'Array of 16 Account numbers
GLAccountField = Array(430000, 446030, 477030, 474210, 446075, 472700, 472710, 476000, 476100, 476610, 452200, 454700, 471300, 473110, 490000, 490710)
Dim srcFinder As Range, trgFinder As Range
Dim searchGL As Long
Dim srcRng As Range, trgRng As Range
Dim i As Integer
For i = LBound(GLAccountField) To UBound(GLAccountField)
'The range where GL Accounts will be searched
Set srcRng = srcWs.Range("A1:A100") 'source file
Set trgRng = trgWs.Range("B10:B900") 'target file
'search for the account number(i) in source and target sheets
searchGL = GLAccountField(i)
Set srcFinder = srcRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
Set trgFinder = trgRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
'If finder value equals searched Account Number, then paste to target
If srcFinder Is Nothing Then
MsgBox "GL Account: " & searchGL & " NOT found in 'Accounting Input' file"
Else
'copy from source
srcFinder.Offset(0, 15).Resize(1, 12).Copy
'paste to target from source
trgFinder.Offset(1, 4).Resize(1, 12).PasteSpecial xlPasteValues
End If
Next i
srcWb.Close
End Sub
回答by aucuparia
(Answered in comments by Andy G):
（在安迪 G 的评论中回答）：
Use Application.ScreenUpdating = Falseat the start of your sub. Remember to set it back to Trueat the end (also good practice to do this in an error handler so it gets reset even in the event of an error):
Application.ScreenUpdating = False在您的子程序开始时使用。请记住将其设置回True最后（在错误处理程序中执行此操作也是一种很好的做法，以便即使在发生错误时也会重置）：
Sub foo()
On Error Goto errHandler
Application.ScreenUpdating = False
'Your code here
Application.ScreenUpdating = True
errHandler:
Application.ScreenUpdating = True
End Sub
