VBA私有子过程向模块传参及每秒自动保存工作簿问题

1. 当前传参方式的错误点 #

• 你的SaveBook需要两个参数，但调用Application.OnTime时仅指定宏名，未传递参数，导致过程因参数不匹配无法执行。
• Ontimer_s是工作簿对象内的公共变量，在标准模块的SaveBook中直接引用时，需明确指定对象（比如ThisWorkbook.Ontimer_s），否则会被当作模块内未定义的变量。
• 你用ByVal传递Ontimer_s，修改的只是参数副本，不会改变原公共变量的值，应该改用ByRef或者直接操作公共变量。

2. 修正后的代码实现 #

步骤1：将公共变量移至标准模块

把公共变量放到标准模块（比如Module1）中，让所有模块都能直接访问：

Option Explicit
Public Ontimer_s As Date
Public SavePath As String ' 统一管理保存路径

步骤2：修改工作簿打开事件

Private Sub Workbook_Open()
    ' 初始化保存路径（取当前工作簿路径+文件名，不带后缀）
    SavePath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".") - 1)
    ' 设置第一次执行时间
    Ontimer_s = Now() + TimeValue("00:00:01")
    ' OnTime传参需用字符串拼接格式：宏名+括号，字符串参数用双引号包裹，日期用#包裹
    Application.OnTime Ontimer_s, "'SaveBook """ & SavePath & """, #" & Ontimer_s & "#'"
End Sub

步骤3：修正SaveBook过程

Public Sub SaveBook(ByVal SavePath As String, ByRef Ontimer_s As Date)
    Application.DisplayAlerts = False
    ' 用SaveCopyAs替代SaveAs：不会改变当前工作簿的关联路径，仅生成副本
    ThisWorkbook.SaveCopyAs Filename:=SavePath & ".xlsm"
    Application.DisplayAlerts = True
    
    ' 更新下一次执行时间
    Ontimer_s = Now() + TimeValue("00:00:01")
    ' 循环调用OnTime，保持传参格式一致
    Application.OnTime Ontimer_s, "'SaveBook """ & SavePath & """, #" & Ontimer_s & "#'"
End Sub

3. 更优实现建议 #

• 添加停止机制：每秒保存容易导致Excel卡顿，且关闭文件时可能报错，可在关闭事件中取消定时任务：

  Private Sub Workbook_BeforeClose(Cancel As Boolean)
      On Error Resume Next ' 避免任务已执行导致报错
      Application.OnTime Ontimer_s, "'SaveBook """ & SavePath & """, #" & Ontimer_s & "#'", , False
      On Error GoTo 0
  End Sub
  

• 增加修改判断：仅当工作簿有修改时才保存，减少资源消耗：

  Public Sub SaveBook(ByVal SavePath As String, ByRef Ontimer_s As Date)
      Application.DisplayAlerts = False
      If Not ThisWorkbook.Saved Then
          ThisWorkbook.SaveCopyAs Filename:=SavePath & ".xlsm"
          ThisWorkbook.Saved = True ' 标记为已保存，避免重复操作
      End If
      Application.DisplayAlerts = True
      
      Ontimer_s = Now() + TimeValue("00:00:01")
      Application.OnTime Ontimer_s, "'SaveBook """ & SavePath & """, #" & Ontimer_s & "#'"
  End Sub
  

• 降低保存频率：每秒保存过于频繁，建议改为30秒或1分钟一次，除非有特殊业务需求。

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