excel - 如何在Excel中制作一个定期执行的宏？

Question

如何定期、完全自动化地执行一些 VBA 代码？

Answer 1

您可以使用Application.OnTime 安排定期执行宏。例如，使用以下代码创建一个模块。调用“Enable”以启动计时器运行。

关闭工作簿时停止计时器运行非常重要:为此，请处理 Workbook_BeforeClose 并调用“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

或者，您可以以类似的方式使用 Win32 API SetTimer/KillTimer 函数。