在 Excel VBA 中强制屏幕更新

Force a screen update in Excel VBA

提问by Jean-Fran?ois Corbett

My Excel tool performs a long task, and I'm trying to be kind to the user by providing a progress report in the status bar, or in some cell in the sheet, as shown below. But the screen doesn't refresh, or stops refreshing at some point (e.g. 33%). The task eventually completes but the progress bar is useless.

我的 Excel 工具执行一项长期任务，我试图通过在状态栏或工作表中的某些单元格中提供进度报告来对用户友好，如下所示。但屏幕不刷新，或在某个时间点停止刷新（例如 33%）。任务最终完成，但进度条没用。

What can I do to force a screen update?

我该怎么做才能强制更新屏幕？

For i=1 to imax ' imax is usually 30 or so
    fractionDone=cdbl(i)/cdbl(imax)
    Application.StatusBar = Format(fractionDone, "0%") & "done..."
    ' or, alternatively:
    ' statusRange.value = Format(fractionDone, "0%") & "done..."

    ' Some code.......

Next i

I'm using Excel 2003.

我正在使用 Excel 2003。

回答by Robert Mearns

Add a DoEventsfunction inside the loop, see below.

在循环内添加一个DoEvents函数，见下文。

You may also want to ensure that the Status bar is visible to the user and reset it when your code completes.

您可能还想确保状态栏对用户可见，并在您的代码完成时重置它。

Sub ProgressMeter()

Dim booStatusBarState As Boolean
Dim iMax As Integer
Dim i As Integer

iMax = 10000

    Application.ScreenUpdating = False
''//Turn off screen updating

    booStatusBarState = Application.DisplayStatusBar
''//Get the statusbar display setting

    Application.DisplayStatusBar = True
''//Make sure that the statusbar is visible

    For i = 1 To iMax ''// imax is usually 30 or so
        fractionDone = CDbl(i) / CDbl(iMax)
        Application.StatusBar = Format(fractionDone, "0%") & " done..."
        ''// or, alternatively:
        ''// statusRange.value = Format(fractionDone, "0%") & " done..."
        ''// Some code.......

        DoEvents
        ''//Yield Control

    Next i

    Application.DisplayStatusBar = booStatusBarState
''//Reset Status bar display setting

    Application.StatusBar = False
''//Return control of the Status bar to Excel

    Application.ScreenUpdating = True
''//Turn on screen updating

End Sub

回答by Jan Petersen

Text boxes in worksheets are sometimes not updated when their text or formatting is changed, and even the DoEvent command does not help.

工作表中的文本框在其文本或格式更改时有时不会更新，甚至 DoEvent 命令也无济于事。

As there is no command in Excel to refresh a worksheet in the way a user form can be refreshed, it is necessary to use a trick to force Excel to update the screen.

由于 Excel 中没有以刷新用户表单的方式刷新工作表的命令，因此有必要使用一种技巧来强制 Excel 更新屏幕。

The following commands seem to do the trick:

以下命令似乎可以解决问题：

- ActiveSheet.Calculate    
- ActiveWindow.SmallScroll    
- Application.WindowState = Application.WindowState

回答by GSerg

Put a call to DoEventsin the loop.

将调用放入DoEvents循环中。

This will affect performance, so you might want to only call it on each, say, 10th iteration.
However, if you only have 30, that's hardly an issue.

这会影响性能，因此您可能只想在每次迭代时调用它，比如第 10 次迭代。
但是，如果您只有 30 个，这几乎不是问题。

回答by user2180598

Specifically, if you are dealing with a UserForm, then you might try the Repaintmethod. You might encounter an issue with DoEventsif you are using event triggers in your form. For instance, any keys pressed while a function is running will be sent by DoEventsThe keyboard input will be processed before the screen is updated, so if you are changing cells on a spreadsheet by holding down one of the arrow keys on the keyboard, then the cell change event will keep firing before the main function finishes.

具体来说，如果您正在处理UserForm，那么您可以尝试Repaint方法。如果您在表单中使用事件触发器，则可能会遇到DoEvents问题。例如，在函数运行时按下的任何键都将由DoEvents发送。键盘输入将在屏幕更新之前处理，因此如果您通过按住键盘上的一个箭头键来更改电子表格上的单元格，则在主函数完成之前，单元格更改事件将继续触发。

A UserForm will not be refreshed in some cases, because DoEventswill fire the events; however, Repaintwill update the UserForm and the user will see the changes on the screen even when another event immediately follows the previous event.

在某些情况下不会刷新用户窗体，因为DoEvents会触发事件；然而，Repaint将更新用户窗体，即使另一个事件紧跟在前一个事件之后，用户也会在屏幕上看到更改。

In the UserForm code it is as simple as:

在 UserForm 代码中，它很简单：

Me.Repaint

回答by FreeSoftwareServers

@Hubisans comment worked best for me.

@Hubians 评论对我来说效果最好。

ActiveWindow.SmallScroll down:=1
ActiveWindow.SmallScroll up:=1

回答by PKanold

This worked for me:

这对我有用：

ActiveWindow.SmallScroll down:=0

or more simply:

或更简单地说：

ActiveWindow.SmallScroll 0

回答by CH Oldie

I couln't gain yet the survey of an inherited extensive code. And exact this problem bugged me for months. Many approches with DoEnvents were not helpful. Above answer helped. Placeing this Sub in meaningful positions in the code worked even in combination with progress bar

我还没有获得对继承的广泛代码的调查。确切地说，这个问题困扰了我几个月。DoEnvents 的许多方法都没有帮助。以上答案有帮助。将此 Sub 放置在代码中有意义的位置，甚至与进度条结合使用

Sub ForceScreenUpdate()
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Wait Now + #12:00:01 AM#
    Application.ScreenUpdating = False
    Application.EnableEvents = False
End Sub

回答by Michael

This is not directly answering your question at all, but simply providing an alternative. I've found in the many long Excel calculations most of the time waiting is having Excel update values on the screen. If this is the case, you could insert the following code at the front of your sub:

这根本不是直接回答您的问题，而只是提供了一个替代方案。我发现在许多冗长的 Excel 计算中，大部分时间都在等待屏幕上显示 Excel 更新值。如果是这种情况，您可以在 sub 前面插入以下代码：

Application.ScreenUpdating = False
Application.EnableEvents = False

and put this as the end

并将其作为结尾

Application.ScreenUpdating = True
Application.EnableEvents = True

I've found that this often speeds up whatever code I'm working with so much that having to alert the user to the progress is unnecessary. It's just an idea for you to try, and its effectiveness is pretty dependent on your sheet and calculations.

我发现这通常会加快我正在使用的任何代码的速度，以至于不必提醒用户注意进度。这只是您尝试的一个想法，其有效性很大程度上取决于您的工作表和计算。