不需要的Loop Workbook_aftersave

Question

我是宏的新手，我在VBA上使用了Workbook_AfterSave功能。由于某种原因，它继续循环保存功能。我不知道如何摆脱这一点。它永远保存了Excel文件，并最终崩溃。这是代码。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        "F:Ten Year Load Forecasts 2017-2026.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Answer 1

添加静态变量以防止递归：

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  Static bHere as Boolean
  If bHere then Exit Sub
  bHere = True
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs Filename:= _
        "F:Ten Year Load Forecasts 2017-2026.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  Application.DisplayAlerts = True
  bHere = False
End Sub

Answer 2

您可以将应用程序对象的EnableEvents属性设置为False，以防止在Workbook_AfterSave事件中保存时再次触发。

下面的代码获得标志的当前状态；将其设置为False;运行您的原始代码；然后将标志重置在错误处理程序中。如果在保存过程中发生IO错误，则在此处有一个错误处理程序，并将重置放在错误处理程序中确保EnableEvents设置还原为原始值。示例代码：

Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim blnSetting As Boolean
    ' IO operations should have an event handler
    On Error GoTo CleanExit
    ' remember existing setting and set flag to False
    blnSetting = Application.EnableEvents
    Application.EnableEvents = False
    ' your original code
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        "F:Ten Year Load Forecasts 2017-2026.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
CleanExit:
    If Err.Number <> 0 Then
        ' handle error
        Debug.Print Err.Description
    End If
    ' reset the Application flag here
    Application.EnableEvents = blnSetting
End Sub