Excel Digital Stopwatch

In a previous post we saw how to create a digital timer in Excel using VBA macros (see that post here). In that example we used the OnTime method of the Application object. In this post we see how to create a stopwatch using the Timer VBA function in Excel. The stopwatch time is shown in a cell in the Excel worksheet. It can also be shown in a text box, shape, userform, or the status bar in Excel, among other places.

Format Cell

The cell is formatted as a number with three decimals to show seconds and milliseconds as decimals. Showing three decimals allows to see the exact number of milliseconds. Alternatively, the Excel stopwatch can be set to have only two (or even 1 decimal), thus showing the corresponding proportion of milliseconds.

The format in cell B2 is set with NumberFormat as shown below. When starting the stopwatch in Excel, the value is set to 0; but we can choose to have another button to reset (set to 0) and start/pause buttons to keep the time value after pause (more like a chronometer than a stopwatch).

 
  Range("B2").NumberFormat = "#0.000"
  Range("B2").Value = 0
 

Note that the cell shows the number of seconds and milliseconds, not minutes (or hours). When run for more than 60 seconds, it keeps showing seconds (61, 62, etc.). The format of the Excel stopwatch can be adapted to show minutes if needed. But in such case, the other macros should be updated to display the time in that different format. The easiest approach would be to have minutes and seconds in one cell (mm:ss or even hh:mm:ss) and milliseconds in another cell. See below a possible variation of the stopwatch in Excel.

 

Timer Function

We use the VBA Timer function to get the time when the Excel stopwatch starts and the difference while running. The Timer function returns the number of seconds elapsed since midnight as a floating number. Thus, it includes milliseconds as decimals. When put into a variable, it needs to be declared as a Single.

 
  Dim startT As Single
  startT = Timer
 

We use a Boolean variable (TimerOn) to determine whether the Excel stopwatch has started or not. The variable needs to be declared at the module level because is used in two different macros (see later). When starting, we set the variable to True.

Here’s how the first macro looks like. The macro formats the number in cell B2 and resets the value to 0, sets TimerOn to True, and assigns the current time to the variable startT. It then calls another macro to run the stopwatch in Excel.

 
  Dim TimerOn As Boolean
 
  Sub StartTimer()
      Dim startT As Single
      Range("B2").NumberFormat = "#0.000"
      Range("B2").Value = 0
      TimerOn = True
      startT = Timer  'number of seconds elapsed since midnight (start time)
      Call RunTimer(startT)
  End Sub

We call another macro and pass the starting time as argument of the sub-routine. Alternatively, the code of the other macro (RunTimer – see next) can be just added to this one.

 

Run Timer

A second macro runs a loop that updates the value in cell B2 continuously. The macro accepts the start time variable as argument. The value is the difference between the current time and the start time, both taken from the function Timer. The macro keeps looping while TimerOn is True to run the stopwatch in Excel.

 
  Sub RunTimer(startT)
      Dim runT As Single
      Do While TimerOn = True
          DoEvents
          runT = Timer
          Range("B2").Value = runT - startT
      Loop
  End Sub
 

It is imperative to use DoEvents in this macro, otherwise Excel would freeze. DoEvents allows to run the loop while keeping the application alive. We can navigate the worksheet, or even add or activate other worksheets. Note that the code above updates cell B2 in the active sheet. To have the Excel stopwatch always in the same sheet we need to specify the sheet as indicated below.

  Sheet1.Range("B2").Value = runT – start

We can also show it in a userform, a text box or other shape, or the status bar. The principle is the same and the only thing that changes is the target recipient (i.e., object) of the stopwatch in Excel.

 

Stop Timer

There are two buttons to start and stop the stopwatch in Excel. The start button calls the first macro above (StartTimer). The second button calls the macro below, which sets the Boolean variable TimerOn to False. That Boolean is declared at module level and therefore kept in memory while executing the macro RunTimer.

 
  Sub StopTimer()
      TimerOn = False
  End Sub
 

The buttons can be updated as needed, or additional buttons can be added for example to start, pause, and reset the stopwatch in Excel.  The StartTimer routine should then be updated to not set the value of cell B2 to 0. Additionally, it could rename the button to “resume” or something like that. The pause button would set TimerOn to False and the reset button would just set the value to 0.

 

Download Excel Digital Stopwatch