如何在Excel中定期执行宏？ [英] How to make a macro which executes periodically in Excel?

问题描述

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

解决方案

可以使用Application.OnTime来调度宏定期执行例如，创建一个包含以下代码的模块。调用启用启动定时器运行。

关闭工作簿时停止计时器运行非常重要：为此处理Workbook_BeforeClose并调用禁用

  Option Explicit 
 
私有m_dtNextTime作为日期
私有m_dtInterval作为日期
 
公共子启用（间隔为日期）
禁用
 m_dtInterval =间隔
 StartTimer 
 End Sub 
 
 Private Sub StartTimer（）
 m_dtNextTime = Now + m_dtInterval 
 Application.OnTime m_dtNextTime，MacroName
 End Sub 
 
公共子宏名（）
错误GoTo ErrHandler：
'...你的东西在这里
 
'再次启动计时器
 StartTimer 
退出子
 ErrHandler：
'处理错误，如果需要重新启动计时器
 End Sub 
 
 Public Sub Disable（）
 On Error Resume Next'忽略错误
 Dim dtZero As Date 
如果m_dtNextTime<> dtZero然后
'如果运行
停止计时器Application.OnTime m_dtNextTimeMacroName，False 
 m_dtNextTime = dtZero 
 End If 
 m_dtInterval = dtZero 
 End Sub 
  

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

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

解决方案

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.

这篇关于如何在Excel中定期执行宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！