Excel Timer Macros

In this post we review several options to create a timer with Excel VBA macros. There are basically three functions that allow to track time in Excel: Application.OnTime method, Timer VBA function, SetTimer function of the user32 library/Windows API.  Furthermore, there are many locations where the timer can be placed. In this post we see how to add the timer to a cell, a text box or shape, an activeX control, the status bar, and a userform.

Timer Functions

Application.OnTime method

The Application.OnTime method allows to run a macro at a certain time. It can be used to run a macro every one second that updates the timer value. See below the basic syntax of the Application.OnTime method used to run another macro (MoveTimer) after 1 second.

 Application.OnTime Now + TimeValue("00:00:01"), "MoveTimer"

 

The MoveTimer macro updates the value in the timer recipient by adding one second (see later). The same line of code can be inside the MoveTimer macro or in any other macro (for example, StartTimer, SetTimer, etc).

 

Timer VBA function

The Timer VBA function returns the number of seconds elapsed since midnight. The number returned by the Timer function has decimals and therefore allows to track time in milliseconds. Timer variables need to be declared as Single or Double. A loop gets the difference between the initial and current time. The variable TimerOn is declared at module level to allow another macro to stop the timer (setting TimerOn to False).

 Dim t0 As Single, t1 As Single
 t0 = timer
 Do While TimerOn = True
     DoEvents
     t1 = timer
     Range("N2") = t1 - t0
 Loop

 

The above example updates the timer in cell N2. That cell should be formatted accordlingly, for example, as a number with 3 decimals.

 Range("N2").NumberFormat = "#0.000"

 

SetTimer function (user32 library/Windows API)

See below the declaration of the SetTimer function of the user 32 library or Windows API and various macros to start and stop the timer. The SetTimer function accepts various parameters; the third parameter defines the interval in milliseconds before calling another macro (defined in the fourth parameter).

 #If Win64 Then
  Public Declare PtrSafe Function SetTimer Lib "User32" ( _
      ByVal hwnd As LongLong, _
      ByVal nIDEvent As LongLong, _
      ByVal uElapse As LongLong, _
      ByVal lpTimerFunc As LongLong) As LongLong
  Public Declare PtrSafe Function KillTimer Lib "User32" ( _
      ByVal hwnd As LongLong, _
      ByVal nIDEvent As LongLong) As LongLong
  Public TimerID As LongLong
 #Else
  Public Declare PtrSafe Function SetTimer Lib "User32" ( _
      ByVal hwnd As Long, _
      ByVal nIDEvent As Long, _
      ByVal uElapse As Long, _
      ByVal lpTimerFunc As Long) As Long
  Public Declare PtrSafe Function KillTimer Lib "User32" ( _
      ByVal hwnd As Long, _
      ByVal nIDEvent As Long) As Long
  Public TimerID As Long
 #End If
 
 Sub StartTimerAPI()
     If TimerID <> 0 Then
         KillTimer 0, TimerID
         TimerID = 0
     End If
     TimerID = SetTimer(0, 0, 1000, AddressOf TimerEvent)
 End Sub
 
 Private Sub TimerEvent()
     On Error Resume Next
     Call MoveTimer
 End Sub
 
 Sub StopTimerAPI()
     KillTimer 0, TimerID
     TimerID = 0
 End Sub

  

Timer Macros

The first two options above require to have a variable that indicates whether the timer is on or off. That’s usually a Boolean variable declared at module level. Thus, the MoveTimer routine will always check whether the timer is on to keep updating the timer value. See below how the structure of the timer looks like for an example using Application.OnTime.

 Dim TimerOn As Boolean
 
 Sub StartTimer()
 If TimerOn = False Then
     TimerOn = True
     Sheet1.Range("N4").Value = "00:00:00"
     Application.OnTime Now + TimeValue("00:00:01"), "MoveTimer1"
 End If
 End Sub
 
 Sub MoveTimer()
 If TimerOn = True Then
     Sheet1.Range("N4").Value = Sheet1.Range("N4").Value + TimeValue("00:00:01")
     Application.OnTime Now + TimeValue("00:00:01"), "MoveTimer"
 End If
 End Sub
 
 Sub StopTimer()
     TimerOn = False
 End Sub

Using the VBA Timer function requires only one macro to start/move the timer (as shown earlier) and another to stop it (same as above).

 

Timer Locations

Cell/Range

The timer can run on a cell in a given worksheet (Sheet1 in example below). Note that Sheet1 is the module name – to reference the sheet by its name we use Sheets("Sheet1").

In StartTimer:

 Sheet1.Range("N4").Value = "00:00:00"

 

In MoveTimer:

 Sheet1.Range("N4").Value = Sheet1.Range("N4").Value + TimeValue("00:00:01")

 

The timer is visible only in that sheet. Alternatively, we can run the timer in the ActiveSheet, thus being visible when moving to other sheets. But in that case we should keep the time in a variable instead of adding one second to the value in the cell (as done above).

 

Text Box/Shape

We can run the timer on a text box or any other shape. A Shape object variable needs to be declared at module level (it will be used in two procedures). The TextBox or Shape is specific of a given worksheet.

 Dim TimerBox As Shape

 

The text inside the text box or shape is set to 0 when the timer starts and updated every one second later (see below). Note the various properties used to update the text in a shape – the properties TextFrame and Characters  are also used. Note that formatting the text value may be needed (Format as hh:mm:ss without AM/PM).

 

In StartTimer:

 Set TimerBox = Sheet1.Shapes("TimerBox")
 TimerBox.TextFrame.Characters.Text = "00:00:00"

 

In MoveTimer:

 With TimerBox.TextFrame.Characters
     .Text = Format(TimeValue(.Text) + TimeValue("00:00:01"), "hh:mm:ss")
 End With

 

ActiveX Control

We can also run the timer in an ActiveX control that accepts text such as a Label or a TextBox. The example below runs the timer in a TextBox ActiveX control with the name TimerX. The ActiveX control can have events, for example click or double-click the box to perform some action. The ActiveX control is specific of a given worksheet.

 

In StartTimer:

 Sheet1.TimerX.Text = "00:00:00"

 

In MoveTimer:

 With Sheet1.TimerX
     .Text = Format(TimeValue(.Text) + TimeValue("00:00:01"), "hh:mm:ss")
 End With

 

Status Bar

The Excel status bar shows notifications and other relevant information about the file.  We can get or set the value on the left with the property StatusBar and run a timer just there. As the timer value is updated every second, any other notifications will be overwritten.

 

In StartTimer:

 Application.StatusBar = "00:00:00"

 

In MoveTimer:

 Dim TimerDisplay As String
 TimerDisplay = TimeValue(Application.StatusBar) + TimeValue("00:00:01")
 Application.StatusBar = Format(TimerDisplay, "hh:mm:ss")

 

We can remove the timer and get back to the default notification message setting the value of the StatusBar property to nothing. The default value of the status bar is “Ready”.

 Application.StatusBar = ""

 

UserForm

Running the timer on a modeless userform is probably one of the best options. We simply need to add an empty label and update the caption of the label with time. The userform in the example below is TimerForm. The name of the Label is TimeLabel.

 

 

In StartTimer:

 With TimerForm
     .TimeLabel.Caption = "00:00:00"
     .Show
 End With

 

In MoveTimer:

 With TimerForm.TimeLabel
     .Caption = Format(TimeValue(.Caption) + TimeValue("00:00:01"), "hh:mm:ss")
 End With

 

Additional macros to stop timer and unload the form.

 Sub StopTimer5()
     TimerOn = False
     'Unload TimerForm
 End Sub
 
 Private Sub UserForm_Terminate()
    StopTimer5
    StopTimer6
 End Sub

  

Download Excel Timer Macros