[Excel VBA] How to Execute Macros with a Delay or Repeatedly (OnTime, GetTickCount)

Introduction

In VBA, there are many situations where you want to implement time-based processing, such as “saving a file automatically after 5 minutes” or “updating sheet values every 10 seconds.” Additionally, for more advanced usage, you might want to create animations where shapes move smoothly.

Excel VBA provides several mechanisms for implementing such time-related processing. This article explains three different methods for implementing timer processing according to your needs.

1. Executing a macro once after a specified time.
2. Executing a macro repeatedly at fixed intervals.
3. Executing processing repeatedly at high-speed intervals of less than 1 second.

1. Execute a Macro Once After a Specified Time (Application.OnTime)

The Application.OnTime method allows you to schedule a specific macro to run at a specific time. By combining Now (current time) and TimeValue (time duration), you can specify “X seconds from now.”

Sample Code

' Running this macro will execute "DisplayMessage" after 10 seconds
Sub ScheduleSingleExecution()
    Dim scheduledTime As Date
    scheduledTime = Now + TimeValue("00:00:10") ' 10 seconds from the current time
    
    ' Schedule the execution time and macro name using the OnTime method
    Application.OnTime EarliestTime:=scheduledTime, Procedure:="DisplayMessage"
    
    MsgBox "The message will be displayed in 10 seconds."
End Sub

' The macro that is scheduled and executed
Sub DisplayMessage()
    MsgBox "The scheduled task has been executed!"
End Sub

Explanation

Pass the future time to the EarliestTime argument of Application.OnTime, and pass the name of the macro you want to execute as a string to the Procedure argument.

2. Execute a Macro Repeatedly at Fixed Intervals (Application.OnTime)

Application.OnTime can achieve repetitive processing by scheduling itself to run again. It is important to set up a counter variable or conditional branching to stop the process.

Sample Code

' --- Write at the top of the module ---
Private nextRunTime As Date
Private executionCount As Long

' Start the repeating task
Sub StartRepeatingTask()
    executionCount = 0
    Call UpdateCellPeriodically ' Execute the first run
End Sub

' Process to update the cell every 3 seconds
Sub UpdateCellPeriodically()
    ' Exit if the execution count reaches 5
    If executionCount >= 5 Then
        MsgBox "Repeating task finished."
        Exit Sub
    End If
    
    executionCount = executionCount + 1
    Range("A1").Value = executionCount & "th update (" & Time & ")"
    
    ' --- Schedule this macro to run again in 3 seconds ---
    nextRunTime = Now + TimeValue("00:00:03")
    Application.OnTime EarliestTime:=nextRunTime, Procedure:="UpdateCellPeriodically"
End Sub

Explanation

At the end of the UpdateCellPeriodically macro, it schedules itself using OnTime to run 3 seconds later. This repeats the process every 3 seconds. When executionCount reaches 5, the macro exits without scheduling the next run, stopping the loop.

3. Execute Processing at High-Speed Intervals of Less Than 1 Second (Windows API)

The precision of Application.OnTime is in seconds, so it is unsuitable for high-speed repetitive processing of less than 1 second, such as smooth animation. In such cases, use the GetTickCount function from the Windows API.

Sample Code

'--- Write at the top of the module (API Function Declaration) ---
' Compatible with both 64-bit and 32-bit
#If VBA7 Then
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Declare Function GetTickCount Lib "kernel32" () As Long
#End If

'--- Declare module-level variables ---
Private lastTick As Long
Private loopCounter As Long

' Start animation to move a shape every 100 milliseconds
Sub StartShapeAnimation()
    Const FRAME_INTERVAL As Long = 100 ' Execution interval (milliseconds)
    Const MAX_LOOPS As Long = 50      ' Maximum number of loops
    
    Dim targetShape As Shape
    Set targetShape = ActiveSheet.Shapes(1)
    
    loopCounter = 0
    lastTick = GetTickCount ' Record start time
    
    Do While loopCounter < MAX_LOOPS
        ' Check if the specified time has passed since the last execution
        If GetTickCount > lastTick + FRAME_INTERVAL Then
            '--- Process to execute ---
            targetShape.Left = targetShape.Left + 10
            
            '--- Update time and counter ---
            lastTick = GetTickCount
            loopCounter = loopCounter + 1
        End If
        
        ' Release CPU so Excel doesn't freeze
        DoEvents
    Loop
    
    MsgBox "Animation finished."
End Sub

Explanation

• GetTickCount: An API function that returns the elapsed time in milliseconds since the PC started.
• Do While … Loop: The loop constantly monitors the value of GetTickCount. The moment the specified interval (FRAME_INTERVAL) has passed since the last execution time (lastTick), the next process runs.
• DoEvents: A command to prevent Excel from becoming unresponsive during high-speed loops.

Summary

In this article, I explained three methods for implementing time-delayed and repetitive processing in VBA.

• Application.OnTime: Best for simple delayed execution in seconds or low-frequency repetitive tasks.
• GetTickCount + Do…Loop: Best for high-speed repetitive processing in milliseconds (such as animation).

By using these techniques according to your needs, you can create more advanced and dynamic macros.