VBA Time

Time is a valuable resource. VBA has functions and methods which can be used to work with time. Timing code, scheduling tasks to run, delaying code for a period of time, or logging the current time are all examples of using time in a program.

Timer

The Timer function can be used to calculate the difference between two points in time. This can be used to make a program wait or to time the execution of code.

Option Explicit

Public Sub TimeSleepProcedure()
    Dim ST As Single
    ST = Timer
    Sleep 3
    ST = Timer - ST
    Debug.Print ST & " seconds."
End Sub

Public Sub Sleep(Seconds As Long)
    Dim ST As Single
    ST = Timer
    Do While Timer - ST < Seconds
        DoEvents
    Loop
End Sub

Time

The Time and Time$ functions are used to return the current time. Time returns the time as a Date type whereas Time$ returns the time as a String.

Public Sub Example()
    
    Debug.Print Time
    Debug.Print Time$
    
End Sub

Now

The Now function returns the current date and time.

Public Sub Example()

    Debug.Print Now

End Sub

Application.Wait

In Excel, the Application.Wait method can be used to pause code execution until a certain amount of time has elapsed and then resume execution.

Option Explicit

Public Sub Example()

    Debug.Print Now

    Application.Wait Now + TimeValue("00:00:03")

    Debug.Print Now

End Sub

Application.OnTime

In Excel, the Application.OnTime method can be used to schedule a procedure to run at a certain time.

Option Explicit

Public Sub ScheduleTasks()
    Application.OnTime Now + TimeValue("00:00:03"), "PrintHelloWorld"
    Application.OnTime Now + TimeValue("00:00:05"), "DisplayReminderAlert"
    Debug.Print "Tasks Scheduled."
End Sub

Public Sub PrintHelloWorld()
    Debug.Print "Hello, World!"
End Sub

Public Sub DisplayReminderAlert()
    MsgBox "Remember to do the thing!"
End Sub

WinAPI

There are a number of Windows API functions that deal with time.

Option Explicit

#If VBA7 = 1 Then
    
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
#Else
    
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

#End If

MicroTimer

Charles Williams, founder of Decision Models, created a more precise alternative to the Timer function which returns a double and can be used to measure smaller increments of time. Charles references the MicroTimer function in his articles:

Option Explicit

#If VBA7 = 1 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" _
        Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" _
        Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" _
        Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" _
        Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If

Public Function MicroTimer() As Double

    'Returns seconds.

    Dim cyTicks As Currency
    Static cyFrequency As Currency
    
    MicroTimer = 0
    
    'Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency
    
    'Get ticks.
    getTickCount cyTicks
    
    'Calculate seconds.
    If cyFrequency Then MicroTimer = cyTicks / cyFrequency

End Function