vba 需要VB让Excel在后台实时计算工作表或范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/870727/
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
Need VB to make Excel calculate a sheet or range in realtime and in the background
提问by CheeseConQueso
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))
=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))
回答by Graham
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
回答by e.James
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_Clickis 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，但我让事情变得简单以说明这个概念。
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 不会停止响应用户输入）。即使计时器正在运行，您仍然可以按下“停止”按钮。
