In this post we will explore the various ways we can set a Timer in Excel VBA as well as how to build your own VBA Stopwatch. Setting timers can be extremly useful if you want to run code as specific time intervals. On the otherhand a practical example can be a VBA Stopwatch which can be useful to measure time elapsed directly in a Excel spreadsheet.

VBA Timer

The most simple example of a VBA Timer can be made using the VBA Timer function:

Dim startTime as Single
 
startTime = Timer 'Get current time in seconds
'... Some code here ...
Debug.Print Timer - startTime 
'Result for about 1-2 seconds e.g. 1,90625 

VBA Timer with Hours, Minutes and Seconds

The above is very useful if you want to measure time elapsed in Seconds, if you want to calculate time elapsed in Hours, Minutes and Seconds you can simply use the VBA Now function with text formatting using VBA Format instead:

Dim currTime as Date 

currTime = Now()
'... Some code here ...
Debug.Print Format(currTime  - Now(), "HH:MM:SS")
'Result for 10 minutes and 1 second: 00:10:01 

VBA Wait

In case you don’t want to measure time elapsed and instead set an interval or delay between code execution you either use the VBA Sleep or VBA Wait procedures like below. Remember that the VBA Sleep function is not a native VBA function and needs to be declared.

'Sleep for 1 second
Call Sleep(1000) 
 
'Wait for 1 second
Call Application.Wait( DateAdd("s", 1, Now) )

VBA Alarms and Scheduling

Another VBA Timer scenario is scheduling procedures to run at a specific time or after a specific time interval like an alarm clock. To do this we use the Excel VBA OnTime procedure:

Sub SetAlarm()
    Dim timeout As Date
    timeout = TimeValue("21:50:00") 'Set alarm for 21:50 PM
    Application.OnTime timeout, "WhatHour"
End Sub
 
Sub WhatHour()
    Call MsgBox("The time is " & Format(Now(), "HH:MM"))
End Sub

You can use the VBA OnTime function also to schedule code execution after a specific duration of time. The below will save the current file after every 5 min.

Sub ScheduleAfter()
    Dim timeout As Date
    timeout = Now() + TimeValue("00:05:00") 
    Application.OnTime timeout, "SaveBackup"
End Sub
 
Sub SaveBackup()
    ActiveWorkbook.Save 'Save workbook
    'Reschedule next Save event
    Dim timeout As Date
    timeout = Now() + TimeValue("00:05:00") 
    Application.OnTime timeout, "SaveBackup"
End Sub

VBA Stopwatch

Using the VBA OnTime function we can also create a simple Excel VBA Stopwatch:

As you can see in the example above I created a button that launches a Start / Stop sequence. When the stopwatch is running it increments the TIMESTAMP cell (a named cell). You can use the code below to achieve this. Remember to select your named range as well as to connect your button to the StartStop procedure.

Dim timerOn As Boolean
Dim timeElapsed As Single
Sub StartStop()
    If timerOn Then
        Application.OnTime Now() + TimeValue("00:00:01"), "AddSecond", Schedule:=False
        timerOn = False
    Else
        timeElapsed = 0
        timerOn = True
        Application.OnTime Now() + TimeValue("00:00:01"), "AddSecond"
    End If
End Sub
Sub AddSecond()
    If timerOn Then
        timeElapsed = timeElapsed + 1
        Dim s As Long, h As Long, m As Long
        h = timeElapsed / 3600
        s = timeElapsed - h * 3600
        m = s / 60
        s = s - m * 60
        
        [TIMESTAMP] = Format(TimeSerial(h, m, s), "HH:MM:SS")
        Application.OnTime Now() + TimeValue("00:00:01"), "AddSecond"
    End If
End Sub

What happens above? When you hit the button the AddSecond procedure will be set to run within a second and then automatically sets itself to run in the next second after updating the timestamp. The next hit of the button will turn of the schedule. This approach may see some slight delays over a longer duration of time (as we are running code before the schedule), however, it should be good enough.