excel - SpecialCells(xlCellTypeFormulas, xlErrors) 仅在宏独立运行时返回预期结果

Question

我正在创建一个宏来检查工作簿中每个工作表的公式中的错误。宏在触发 BeforeSave 挂钩时运行，并提示用户在发现错误时决定是否仍要保存。

     Public Sub errorCheck()
         On Error Resume Next
         Application.StatusBar = "Running: formulaErrorCheck"

         Dim ws As Worksheet
         Dim errors As range
         Dim r As range

         For Each ws In Worksheets
             Set errors = Nothing
             Set errors = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).cells

             If Not (errors Is Nothing) Then
                 For Each r In errors
                     If IsError(r.Value) = True Then
                         Select Case r.Value
                         Case CVErr(xlErrValue), CVErr(xlErrDiv0), CVErr(xlErrName), CVErr(xlErrRef)
                             If MsgBox("Excel Sheet " + ws.name + " contains a reference error in cell " + r.Address(False, False) + ". Save anyway?", vbYesNo, "") = vbNo Then
                                 Application.GoTo Reference:=r
                                 GoTo quit_checking
                             End If
                         End Select
                     End If
                 Next
             End If
         Next

     quit_checking:
         Application.StatusBar = False

     End Sub

检测事件的类模块中的代码：

     OptionExplicit
     Private WIthEvents App As Application

     Private Sub App_WorkbookBeforeSave(ByVal wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

         Book_BeforeSave SaveAsUI, Cancel

    End Sub

     Public Sub Book_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
          Call errorCheck
     End Sub

我遇到的问题是 Set errors = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).cells 当我独立运行宏时返回预期的单元格。但是，当从 BeforeSave 挂钩调用宏时，它会返回整个使用范围。我认为这可能是对工作簿的引用存在问题，但工作表仍按预期迭代。我尝试删除 On Error Resume Next 以确保实际上没有引发错误。我不知道有什么区别。

标签： excelvba