Need VB to make Excel calculate a sheet or range in realtime and in the background

Question

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

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))

Answer 1

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.

In the ThisWorkbook code module:

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

Answer 2

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.

The two key things here are:

Application.Calculate

Which forces Excel to calculate the worksheet, and:

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.