Application.OnTime & BeforeClose

Question

我只想问一下，为什么我总是收到这个错误？

运行时错误'1004'：对象‘_Application’的方法'OnTime‘失败

因此，我试图关闭我的工作簿，但每当我这样做，错误显示。单击Debug按钮后突出显示的行是Workbook_BeforeClose子中的Workbook_BeforeClose。这里似乎有什么问题？

Private Sub Workbook_Open() 'place in ThisWorkbook
 timeCheck = Now + TimeValue("00:15:00")
 Application.OnTime timeCheck, "SaveThis"
End Sub

Sub SaveThis() 'place in Module
 timeCheck = Now + TimeValue("00:15:00")
 Application.DisplayAlerts = False
 ThisWorkbook.Save
 Application.DisplayAlerts = True
 Application.OnTime timeCheck, "SaveThis"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'place in ThisWorkbook
 Application.OnTime timeCheck, "SaveThis", , False
End Sub

Answer 1

在我看来，在应用程序即将关闭的时候，您正试图在将来安排对SaveThis()的调用。为什么要这样做？，如果错误从未发生，那么如何保证在工作簿已经关闭时调用会发生？

如果您打算在关闭工作簿之前保存工作簿，那么您需要按如下方式更改代码：

Private Sub Workbook_Open() 'place in ThisWorkbook
 timeCheck = Now + TimeValue("00:15:00")
 Application.OnTime timeCheck, "SaveThis"
End Sub

Sub SaveThis() 
 timeCheck = Now + TimeValue("00:15:00")
 SaveRightNow     ' This is a new sub that you will need to add
 Application.OnTime timeCheck, "SaveThis"
End Sub

Sub SaveRightNow()
 Application.DisplayAlerts = False
 ThisWorkbook.Save
 Application.DisplayAlerts = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'place in ThisWorkbook
  SaveRightNow  ' Saves the workbook without having to schedule a call in the future
End Sub