vba 处理某些内容时是否可以冻结 Excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8908930/
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
Is it possible to freeze Excel while I process something?
提问by BuZz
Is it possible to freeze Excel while I process something ? I am using 2007. I know already that freezing the update of the sheets from VBA is possible, but I'm looking for a little freeze of the whole thing (with a wait cursor and maybe something nice, like darkening the window or something) .
处理某些内容时是否可以冻结 Excel？我正在使用 2007。我已经知道可以从 VBA 冻结工作表的更新，但我正在寻找整个事情的一点冻结（使用等待光标，也许还有一些不错的东西，比如使窗口变暗或其他什么） .
Has anyone ever tried such thing from VBA ?
有没有人从 VBA 中尝试过这样的事情？
回答by Siddharth Rout
Has anyone ever tried such thing from VBA ?
有没有人从 VBA 中尝试过这样的事情？
Yes. Depending on the kind of processing that I am doing either I hide the entire Excel Application or show a userform with a progress update to avoid any user interference
是的。根据我正在执行的处理类型，我隐藏整个 Excel 应用程序或显示带有进度更新的用户表单以避免任何用户干扰
Example 1(Hiding The Application)
示例 1（隐藏应用程序）
Option Explicit
Sub Sample()
Application.Visible = False
'~~> Rest of the code
Application.Visible = True
End Sub
Example 2(Using a Userform which has the 'X' disabled)
示例 2（使用禁用了“X”的用户表单）
Create a Userform and place this code there
创建一个用户表单并将此代码放在那里
Option Explicit
Private Declare Function RemoveMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Const MF_BYPOSITION = &H400&
Private Sub UserForm_Initialize()
Dim lHwnd As Long
lHwnd = FindWindow("ThunderDFrame", "Please Wait...")
Do While lHwnd = 0
lHwnd = FindWindow("ThunderDFrame", "Please Wait...")
DoEvents
Loop
RemoveMenu GetSystemMenu(lHwnd, 0), 6, MF_BYPOSITION
End Sub
'~~> The below code will not be there
'~~> This is just there so that if you try the above code then you can exit the form
Private Sub UserForm_Click()
Unload Me
End Sub
And the usage will be like this
用法是这样的
Option Explicit
Sub Sample()
UserForm1.Show vbModeless
'~~> Rest of the code
Unload UserForm1
End Sub
回答by Jon Crowell
To freeze Excel during processing, set screenupdating to false. Make sure to set it back to true when you're finished or the user won't be able to interact with Excel. In addition to not scaring your users with a bunch of movement and flashing on the screen, this dramatically speeds up processing.
要在处理过程中冻结 Excel，请将 screenupdating 设置为 false。确保在完成后将其设置回 true，否则用户将无法与 Excel 交互。除了不会用一堆动作和屏幕上的闪烁吓到你的用户之外，这还大大加快了处理速度。
Sub Sample()
Application.ScreenUpdating = False
'~~> Rest of the code
Application.ScreenUpdating= True
End Sub
I agree with JMax that a progress bar is a great option. You can find several different Excel VBA progress bars here: http://spreadsheetpage.com/index.php/blog/progress_bars_the_movie/
我同意 JMax 的观点，即进度条是一个不错的选择。您可以在这里找到几个不同的 Excel VBA 进度条：http: //spreadsheetpage.com/index.php/blog/progress_bars_the_movie/
BTW, you can skip the movie and go straight to the links below. You may find the movie funny, but all it says is "progress bars = good, spinning stuff = bad".
顺便说一句，您可以跳过电影并直接转到下面的链接。你可能会觉得这部电影很有趣，但它说的是“进度条=好，旋转的东西=坏”。
