Simple Digital Clock Timer Excel VBA Macro

This macro runs a clock timer in Excel. The user can start/stop the time, and the clock shows minutes and seconds. It is a simple example to learn how to use the OnTime method of the Application object. 

Macro/VBA code:

 

  Dim TimerOn As Boolean

 

  Sub StartTimer()

      If TimerOn = False Then

          Range("B2").Value = 0

          Range("B2").NumberFormat = "hh:mm:ss"

          TimerOn = True

          SetTimer

      End If

  End Sub

 

  Sub SetTimer()

      Dim TimeToCall As Date

      TimeToCall = Now + TimeValue("00:00:01")

      If TimerOn = True Then Application.OnTime TimeToCall, "MoveTimer"

  End Sub

 

  Sub MoveTimer()

      If TimerOn = True Then

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

          If Range("B2").Value < TimeValue("10:00:00") Then Call SetTimer

      End If

  End Sub

 

  Sub StopTimer()

      TimerOn = False

  End Sub

 

 

Macro explained:

• The macro consists of four procedures or subroutines in the same module. We declare the TimerOn Boolean variable at module level, so that it can be used throughout all procedures.
• The first procedure (StartTimer) is triggered when pressing the Start button. If the timer is not running (TimerOn=False), it will reset the clock in cell B2, set TimerOn as True, and call the SetTimer procedure.
• The second procedure (SetTimer) uses Application.OnTime to call the MoveTimer procedure after one second from the current time (Now) has passed (with TimeToCall being the exact time at which the macro is called). Note that we declared TimeToCall as a Date variable.
• The third procedure writes the updated time in cell B2. The new value equals the previous value plus one second, which is set with the TimeValue function. This could also be done adding the numerical value of 0.00001 units, which is approximately 1 second for this time format (hh:mm:ss), but it is not as accurate as TimeValue. Then it calls back the SetTimer procedure that will repeat the same action after another second has elapsed, as long as TimerOn is True. In this example, the timer will continue for 10 hours (as per the selected TimeValue).
• The fourth procedure triggers when pressing the stop button and sets the TimerOn to False and ends the timer routine between SetTimer and MoveTimer.

This is how we run a simple clock timer in Excel with VBA macros.

Other examples:

• How to Copy Selection To Other Workbook with Excel VBA macros 
  
• How to Add Shapes To Selection with Excel VBA macros
  
• How to List All Files in Folder with Excel VBA macros