vba 关闭 Excel 兼容性检查器以查找“严重的功能丢失”错误

Turning off Excel Compatibility Checker for "Significant Loss of Functionality" errors

提问by mounty

I've found that, while it's possible to programatically disable Excel's "Compatibility Checker" via code (by using ActiveWorkbook.CheckCompatibility = False, either before a SaveAscall or globally by trapping the ActiveWorkbook.BeforeSaveevent), it doesn't seem to work if there is a "Significant loss of functionality" detected. Quick way to test this:

我发现，虽然可以通过代码以编程方式禁用 Excel 的“兼容性检查器”（通过使用ActiveWorkbook.CheckCompatibility = False，在SaveAs调用之前或通过捕获ActiveWorkbook.BeforeSave事件全局），但如果存在“重大损失功能”检测到。测试这个的快速方法：

• Create a new Excel 2010 workbook.
• Select A1:A2 and choose a conditional formatting (doesn't matter what).
• Select A2:A3 and choose a different conditional formatting. A2 should have two different conditional formats applied.

• Open the VBA editor, and add the following code to the Workbook module:

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      ActiveWorkbook.CheckCompatibility = False
  End Sub
  

• Put a breakpoint in the code.

• Back in the spreadsheet, select File > Save As. The code will immediately jump to the breakpoint. If you step through the code, you can verify the CheckCompatibilitysetting in the Immediate pane.
• Once the code has completed, choose the Excel 97-2003 file type and click "Save."
• The compatibility checker still comes up.

• 创建一个新的 Excel 2010 工作簿。
• 选择 A1:A2 并选择条件格式（无所谓）。
• 选择 A2:A3 并选择不同的条件格式。A2 应该应用两种不同的条件格式。

• 打开 VBA 编辑器，将以下代码添加到 Workbook 模块中：

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      ActiveWorkbook.CheckCompatibility = False
  End Sub
  

• 在代码中放置一个断点。

• 返回电子表格，选择文件 > 另存为。代码会立即跳转到断点。如果您单步执行代码，则可以验证CheckCompatibility“立即”窗格中的设置。
• 代码完成后，选择 Excel 97-2003 文件类型并单击“保存”。
• 兼容性检查器仍然出现。

I suspect this is because the error isn't a "minor compatibility issue" (cf. http://msdn.microsoft.com/en-us/library/office/gg132972(v=office.14).aspx) but nothing I do seems to suppress this error, not even creating a registry entry to disable it. Anyone have any idea how to suppress the checker even with "significant" incompatibility?

我怀疑这是因为错误不是“轻微的兼容性问题”（参见http://msdn.microsoft.com/en-us/library/office/gg132972(v=office.14).aspx）但没有我似乎抑制了这个错误，甚至没有创建一个注册表项来禁用它。任何人都知道如何在“严重”不兼容的情况下抑制检查器？

ETA: Without going into a lot of unnecessary detail, I'm trying to automate a process where a number of vendor templates are opened, populated with data, processed according to an enormous (and always slightly different) set of quality control rules, and saved back out as an .xls file (per the vendor's requirements). Because this happens on dozens of different template workbooks every two hours on an unattended system, I can't simply uncheck the compatibility requirement on a per-workbook basis. I mean, I suppose I could, but that would become my full-time job. I need to be able to turn off compatibility checking at run-time for any workbook, the first time, without human intervention.

ETA：在不涉及很多不必要的细节的情况下，我正在尝试自动化一个过程，其中打开许多供应商模板，填充数据，根据大量（并且总是略有不同）的质量控制规则集进行处理，并且保存为 .xls 文件（根据供应商的要求）。因为这种情况每两小时在无人值守系统上发生在几十个不同的模板工作簿上，所以我不能简单地取消选中每个工作簿的兼容性要求。我的意思是，我想我可以，但这将成为我的全职工作。我第一次需要能够在运行时关闭任何工作簿的兼容性检查，而无需人工干预。

采纳答案by mounty

Created a workaround that isn't completely full-featured but it at least hits everything I personally need; maybe it'll serve as a launching point for someone else. Note that this doesn't address the compatibility checker in all cases, just in case of overlapping custom formats.

创建了一个功能不完全但至少满足我个人需要的解决方法；也许它会成为其他人的起点。请注意，这并不适用于所有情况下的兼容性检查器，只是在自定义格式重叠的情况下。

In a nutshell, this goes through all active cells, and for any cell containing a conditional format, evaluates whether or not the custom formatting should be applied (in the correct order), then manually applies it. Finally, all custom formats are deleted. This leaves the workbook formatted but removes the cause of the compatibility checker being forced to appear. YMMV.

简而言之，这将遍历所有活动单元格，对于包含条件格式的任何单元格，评估是否应应用自定义格式（以正确的顺序），然​​后手动应用它。最后，删除所有自定义格式。这会使工作簿保持格式化，但消除了强制出现兼容性检查器的原因。天啊。

Sub FlattenFormats()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim asheet As Worksheet
    Set asheet = wb.ActiveSheet

    Dim cellvalue_regex As New RegExp
    cellvalue_regex.Pattern = "^""(.*)""$"

    Dim c As Range
    Dim conds As Collection

    For Each c In asheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
        If c.FormatConditions.Count > 0 Then
            Set conds = New Collection
            Dim fc As FormatCondition
            Set fc = Nothing
            For Each fc In c.FormatConditions
                conds.Add fc
            Next fc
            c.FormatConditions.Delete

            Sort conds

            Set fc = Nothing
            For Each fc In conds
                Select Case fc.Type
                    Case XlFormatConditionType.xlCellValue
                        Dim theMatches As MatchCollection
                        Set theMatches = cellvalue_regex.Execute(fc.Formula1)
                        Dim match1 As Match
                        Set match1 = theMatches.Item(0)
                        Dim checkFor As String
                        checkFor = match1.SubMatches(0)
                        If c.Value2 = checkFor Then
                            c.Interior.Color = fc.Interior.Color
                            If fc.StopIfTrue Then
                                Exit For
                            End If
                        End If
                    Case XlFormatConditionType.xlExpression
                        If Evaluate(fc.Formula1) Then
                            c.Interior.Color = fc.Interior.Color
                            If fc.StopIfTrue Then
                                Exit For
                            End If
                        End If
                End Select
            Next fc
        End If
    Next c

    ActiveSheet.Cells.FormatConditions.Delete
End Sub

Private Sub Sort(ByRef c As Collection)
    Dim i As Integer, j As Integer
    Dim temp As FormatCondition
    Dim i_item As FormatCondition, j_item As FormatCondition

    For i = 1 To c.Count - 1
        Set i_item = c(i)

        For j = i + 1 To c.Count
            Set j_item = c(j)

            If i_item.Priority > j_item.Priority Then
                Set temp = c(j)
                c.Remove j
                c.Add temp, temp.Priority, i
            End If
        Next j
    Next i
End Sub

回答by LeasMaps

Try Application.DisplayAlerts = False as a workaround.

尝试 Application.DisplayAlerts = False 作为解决方法。

Ciao

再见