在 Excel VBA 中强制屏幕更新 [英] Force a screen update in Excel VBA

问题描述

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

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.

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

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

我使用的是 Excel 2003.

I'm using Excel 2003.

推荐答案

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

Add a DoEvents function inside the loop, see below.

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

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

这篇关于在 Excel VBA 中强制屏幕更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！