Have Geek Brain

Need VB for Excel to calculate sheet or range in real time and in background

How can I get excel to continuously calculate sheets / range in real time (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))

+1


a source to share


2 answers


I used the following to create 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 a macro that I bind to the "Start / Stop" button for the stopwatch. You can get fancy and change your name to "Start" or "Stop" depending on the meaning TimerRunning

, but I've simplified everything to illustrate the concept.

There are two main things:



Application.Calculate

Forces Excel to calculate the worksheet and:

DoEvents

This allows VBA to run in the background (i.e. Excel doesn't stop responding to user input). This allows you to still press the Stop button even if the timer is running.

+2


a source


I think it might cause your criteria to fail (not 1 calc / sec), but I achieved something similar in the following way. Suppose your formula is in cell A1 of a worksheet named Sheet1.

In the ThisWorkbook code unit:

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


... and in a regular code unit:

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


a source






More articles:


All Articles