excel - Excel 错误消息“一个或多个命名的无效引用”

Question

根据 Excel，我找不到无效的命名范围或引用。我检查了我的命名范围，包括图表中的范围。excel 文件包含一个宏，该宏创建一个在文件本身中启动时可以正常工作的报告。但是，如果我从另一个工作簿调用该函数来运行报告，则会收到无效引用的错误消息。在浏览直接和间接创建的报告时，它们看起来是相同的。设置Application.DisplayAlerts = False不起作用。

我尝试使用Allen Wyatt提供的以下代码来浏览所有参考资料，但没有任何参考资料涉及外部工作表，也不包含任何错误。

    Sub CheckReferences()
    ' Check for possible missing or erroneous links in
    ' formulas and list possible errors in a summary sheet

      Dim iSh As Integer
      Dim sShName As String
      Dim sht As Worksheet
      Dim c, sChar As String
      Dim rng As Range
      Dim i As Integer, j As Integer
      Dim wks As Worksheet
      Dim sChr As String, addr As String
      Dim sFormula As String, scVal As String
      Dim lNewRow As Long
      Dim vHeaders

      vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")
      'check if 'Summary' worksheet is in workbook
      'and if so, delete it
      With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
      End With

      For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "Summary" Then
          Worksheets(i).Delete
        End If
      Next i

      iSh = Worksheets.Count

      'create a new summary sheet
        Sheets.Add After:=Sheets(iSh)
        Sheets(Sheets.Count).Name = "Summary"
      With Sheets("Summary")
        Range("A1:D1") = vHeaders
      End With
      lNewRow = 2

      ' this will not work if the sheet is protected,
      ' assume that sheet should not be changed; so ignore it
      On Error Resume Next

      For i = 1 To iSh
        sShName = Worksheets(i).Name
        Application.Goto Sheets(sShName).Cells(1, 1)
        Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)

        For Each c In rng
          addr = c.Address
          sFormula = c.Formula
          scVal = c.Text

          For j = 1 To Len(c.Formula)
            sChr = Mid(c.Formula, j, 1)

            If sChr = "[" Or sChr = "!" Or _
              IsError(c) Then
              'write values to summary sheet
              With Sheets("Summary")
                .Cells(lNewRow, 1) = sShName
                .Cells(lNewRow, 2) = addr
                .Cells(lNewRow, 3) = scVal
                .Cells(lNewRow, 4) = "'" & sFormula
              End With
              lNewRow = lNewRow + 1
              Exit For
            End If
          Next j
        Next c
      Next i

    ' housekeeping
      With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
      End With

    ' tidy up
      Sheets("Summary").Select
      Columns("A:D").EntireColumn.AutoFit
      Range("A1:D1").Font.Bold = True
      Range("A2").Select
    End Sub

标签： excelvba