需要VB使Excel能够实时和在后台计算工作表或范围

How can I make excel continuously calculate a sheet/range in realtime (not 1 calc/sec) and do it in the background?

如何使excel连续实时计算工作表/范围(不是1 calc / sec)并在后台进行?

I want this metric clock to run like a stopwatch....

我希望这个公制时钟像秒表一样运行....

=IF(LEN(ROUND((HOUR(NOW())*(100/24)),0))=1,"0"&ROUND((HOUR(NOW())*(100/24)),0),ROUND((HOUR(NOW())*(100/24)),0))&":"&IF(LEN(ROUND((MINUTE(NOW())*(100/60)),0))=1,"0"&ROUND((MINUTE(NOW())*(100/60)),0),ROUND((MINUTE(NOW())*(100/60)),0))&":"&IF(LEN(ROUND((SECOND(NOW())*(100/60)),0))=1,"0"&ROUND((SECOND(NOW())*(100/60)),0),ROUND((SECOND(NOW())*(100/60)),0))

2 个解决方案

#1

I've used the following to produce the effect you are looking for:

我已经使用以下方法来产生您正在寻找的效果:

Option Explicit

Public TimerRunning As Boolean
Dim CalculationDelay As Integer

Public Sub StartStop_Click()
    If (TimerRunning) Then
        TimerRunning = False
    Else
        TimerRunning = True
        TimerLoop
    End If
End Sub

Private Sub TimerLoop()
    Do While TimerRunning
        '// tweak this value to change how often the calculation is performed '
        If (CalculationDelay > 500) Then
            CalculationDelay = 0
            Application.Calculate
        Else
            CalculationDelay = CalculationDelay + 1
        End If
        DoEvents
    Loop
End Sub

StartStop_Click is the macro that I tie to the Start/Stop button for the stopwatch. You can get fancy, and change its name to "Start" or "Stop" depending on the value of TimerRunning, but I kept things simple to illustrate the concept.

StartStop_Click是我绑定到秒表的开始/停止按钮的宏。您可以获得幻想,并根据TimerRunning的值将其名称更改为“Start”或“Stop”,但我保持简单以说明概念。

The two key things here are:

这里的两个关键事项是:

Application.Calculate

Which forces Excel to calculate the worksheet, and:

这迫使Excel计算工作表,并且:

DoEvents

Which allows VBA to run in the background (i.e. Excel does not stop responding to user input). This is what allows you to still press the "Stop" Button even though the timer is running.

这允许VBA在后台运行(即Excel不会停止响应用户输入)。即使计时器正在运行,这也可以让您按下“停止”按钮。

#2

I think this might fail your "(not 1 calc/sec)" criteria, but I achieved something similar as follows. Assumes your formula is in cell A1 of a worksheet named Sheet1.

我认为这可能会失败你的“(不是1计算/秒)”标准,但我实现了如下类似的东西。假设您的公式位于名为Sheet1的工作表的单元格A1中。

In the ThisWorkbook code module:

在ThisWorkbook代码模块中:

Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:01"), "RecalculateRange"
End Sub

... and in a regular code module:

...并在常规代码模块中:

Public Sub RecalculateRange()
    Sheet1.Range("A1").Calculate
    Application.OnTime Now + TimeValue("00:00:01"), "RecalculateRange"
End Sub

#1