How to make a macro which executes periodically in Excel?

Question

How does one execute some VBA code periodically, completely automated?

Answer 1

You can use Application.OnTime to schedule a macro to be executed periodically. For example create a module with the code below. Call "Enable" to start the timer running.

It is important to stop the timer running when you close your workbook: to do so handle Workbook_BeforeClose and call "Disable"

Option Explicit

Private m_dtNextTime As Date
Private m_dtInterval As Date

Public Sub Enable(Interval As Date)
    Disable
    m_dtInterval = Interval
    StartTimer
End Sub

Private Sub StartTimer()
    m_dtNextTime = Now + m_dtInterval
    Application.OnTime m_dtNextTime, "MacroName"
End Sub

Public Sub MacroName()
    On Error GoTo ErrHandler:
    ' ... do your stuff here

    ' Start timer again
    StartTimer
    Exit Sub
ErrHandler:
    ' Handle errors, restart timer if desired
End Sub

Public Sub Disable()
    On Error Resume Next ' Ignore errors
    Dim dtZero As Date
    If m_dtNextTime <> dtZero Then
        ' Stop timer if it is running
        Application.OnTime m_dtNextTime, "MacroName", , False
        m_dtNextTime = dtZero
    End If
    m_dtInterval = dtZero
End Sub

Alternatively you can use the Win32 API SetTimer/KillTimer functions in a similar way.

Answer 2

There is an application method that can be used for timing events. If you want this to occur periodically you'll have to 'reload' the timer after each execution, but that should be pretty straightforward.

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

-Adam