excel - Application.OnTime & BeforeClose

Question

只想问为什么我一直收到这个错误？

运行时错误“1004”：对象“_Application”的方法“OnTime”失败

所以，我正在尝试关闭我的工作簿，但每当我这样做时，都会显示错误。单击“调试”按钮后突出显示的行Application.OnTime timeCheck, "SaveThis", , False位于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

标签： excelvba

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