VBA – Wait, Delay, Create a Timer While Running Code

Wait or Delay in VBA

There are many instances when a timer could be useful in Excel, here is one method to try:

Sub MyTimer()
Application.Wait Now + TimeValue("00:00:05")
MsgBox ("5 seconds")
End Sub

 

A couple uses of a timer in Excel VBA are pausing code and measuring elapsed time. The following will take you to the respective place I’ve posted an example of each use:

1. Pause code execution for a desired amount of time with Application.Wait (simulating a timer)
link: Hide Excel
2. Measure elapsed time with a Timer in VBA
link: Benchmarking Code

Wait or Delay in Access VBA

You can also use the methods above in Access VBA.  For example, you might like to know how long it takes to move through a recordset.

Sub LoopThroughRecords()
   Dim Count As Long
   Dim BenchMark As Double
   BenchMark = Timer
'Start of Code to Test
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblInvoices", dbOpenDynaset)
   With rst
      Do Until .EOF = True
        .MoveNext
      Loop
   End With
'End of Code to Test
   MsgBox "It took " & Timer - BenchMark & " seconds to loop"
End Sub

 

<<Return to VBA Examples