点击UserForm按钮后实现子程序每5分钟自动运行的问题

核心问题 #

1. Application.OnTime 使用错误：原代码仅指定了时间，但未声明要执行的过程，导致定时任务无法触发；且未循环注册下一次任务，无法实现每5分钟重复执行。
2. Select/Activate 操作隐患：工作表隐藏时，Sheets.Select、Range.Select 会触发错误，必须改为直接引用对象的方式。
3. 缺少任务取消机制：未保存定时任务的时间，无法在关闭UserForm时终止定时，可能导致Excel后台持续运行任务。

修改后的完整代码 #

1. 标准模块中声明全局变量（用于保存定时任务时间） #

Public NextRunTime As Date ' 保存下一次定时任务的时间，方便后续取消

2. 提取执行逻辑为独立过程 #

将原按钮点击事件中的业务逻辑提取为公共过程，供Application.OnTime调用：

Public Sub AutoUpdateData()
    Dim FName As String
    Dim dumpWs As Worksheet, oiDataWs As Worksheet, oiChartsWs As Worksheet
    
    ' 提前引用工作表，避免重复调用Sheets()
    Set dumpWs = ThisWorkbook.Sheets("Dump")
    Set oiDataWs = ThisWorkbook.Sheets("OI Data")
    Set oiChartsWs = ThisWorkbook.Sheets("OI Charts")
    
    ' 刷新Power Query并等待完成
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    
    ' 更新UserForm标签（直接引用Trade窗体控件）
    With Trade
        .Label155.Caption = dumpWs.Range("C16").Value
        .Label156.Caption = dumpWs.Range("C20").Value
        .Label158.Caption = dumpWs.Range("C18").Value
        .Label159.Caption = dumpWs.Range("C22").Value
        .Label161.Caption = dumpWs.Range("C17").Value
        .Label162.Caption = dumpWs.Range("C21").Value
        .Label164.Caption = Format(dumpWs.Range("C19").Value, "#.00")
        .Label165.Caption = Format(dumpWs.Range("C23").Value, "#.00")
        .Label166.Caption = Format(dumpWs.Range("C37").Value, "hh:mm")
    End With
    
    ' 复制Nifty数据到Dump表（避免Select操作）
    oiDataWs.Range("G31:K31").Copy
    dumpWs.Range("DI" & dumpWs.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    ' 导出并加载Nifty图表到UserForm
    Call NiftyChart(oiChartsWs)
    FName = ThisWorkbook.Path & "\temp.gif"
    Trade.Image3.Picture = LoadPicture(FName)
    
    ' 复制BankNifty数据到Dump表（避免Select操作）
    oiDataWs.Range("AF31:AJ31").Copy
    dumpWs.Range("DQ" & dumpWs.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    ' 导出并加载BankNifty图表到UserForm
    Call BankNiftyChart(oiChartsWs)
    FName = ThisWorkbook.Path & "\temp2.gif"
    Trade.Image4.Picture = LoadPicture(FName)
    
    ' 调整控件尺寸
    Trade.MultiPage1.Height = 550
    Trade.Height = 650
    
    ' 注册下一次定时任务（每5分钟执行）
    NextRunTime = Now + TimeValue("00:05:00")
    Application.OnTime NextRunTime, "AutoUpdateData"
End Sub

3. 修改图表导出过程（添加工作表参数，避免重复引用） #

Public Sub NiftyChart(oiChartsWs As Worksheet)
    Dim MyChart As Chart
    Dim FName As String
    
    Set MyChart = oiChartsWs.ChartObjects(1).Chart
    FName = ThisWorkbook.Path & "\temp.gif"
    MyChart.Export Filename:=FName, FilterName:="GIF"
End Sub

Public Sub BankNiftyChart(oiChartsWs As Worksheet)
    Dim MyChart As Chart
    Dim FName As String
    
    Set MyChart = oiChartsWs.ChartObjects(2).Chart
    FName = ThisWorkbook.Path & "\temp2.gif"
    MyChart.Export Filename:=FName, FilterName:="GIF"
End Sub

4. 修改按钮点击事件（初始化定时任务） #

Private Sub CommandButton12_Click()
    ' 首次执行更新逻辑并启动定时
    Call AutoUpdateData
End Sub

5. 添加UserForm关闭事件（取消定时任务） #

确保关闭UserForm时终止定时任务，避免Excel后台继续运行：

Private Sub UserForm_Terminate()
    ' 取消未执行的定时任务
    On Error Resume Next ' 防止任务已执行导致报错
    Application.OnTime NextRunTime, "AutoUpdateData", , False
    On Error GoTo 0
End Sub

关键说明 #

• 避免Select/Activate：直接通过工作表对象引用单元格，解决工作表隐藏时的操作错误。
• 循环注册定时任务：每次执行完AutoUpdateData后，重新注册下一次的运行时间，确保每5分钟重复执行。
• 全局变量保存定时时间：用于在UserForm关闭时取消任务，防止内存泄漏。
• 提前引用工作表：减少重复调用Sheets()，提升代码效率与可读性。

内容的提问来源于stack exchange，提问作者Athang Tikekar