Search code examples
excelexcel-2010vba

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


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 SaveAs call or globally by trapping the ActiveWorkbook.BeforeSave event), it doesn't seem to work if there is a "Significant loss of functionality" detected. Quick way to test this:

  • 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 CheckCompatibility setting 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.

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?

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.


Solution

  • 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