Excel VBA中FormatCondition的.Borders属性报错的优化处理问询

问题描述 #

我编写了一段Excel VBA代码，用于删除带有底部边框的条件格式规则，但处理未设置边框的规则时方法不够优雅。代码中If cf.Borders(xlBottom).LineStyle <> xlNone Then这一行偶尔会抛出运行时错误438：对象不支持该属性或方法。

推测原因是：当条件格式规则未涉及边框设置时，cf.Borders对象并未初始化，直接访问会触发错误。目前用On Error Resume Next跳过错误虽能运行，但会忽略该行所有错误，希望找到更精准的处理方式。

注：cf声明为Variant类型是为了避免For Each循环中的类型不匹配问题。

当前工作代码（不满意版本） #

'=========================================================
' Clear existing conditional formatting with lower borders
'=========================================================

Dim ws As Worksheet
Dim cf  As Variant ' UniqueValues
Dim i As Long

Set ws = ThisWorkbook.Sheets("Sheet1")

' 倒序遍历条件格式规则，避免删除时跳过条目
For i = ws.Cells.FormatConditions.Count To 1 Step -1
    Set cf = ws.Cells.FormatConditions(i)
    
    On Error Resume Next
    ' 检查条件格式是否添加了底部边框
    If cf.Borders(xlBottom).LineStyle <> xlNone Then
    On Error GoTo borderNotInitialized
    ' 删除条件格式
        cf.Delete
borderNotInitialized:
        End If
Next i

优化方案 #

方案1：精准捕获Borders属性错误 #

仅在访问cf.Borders时临时启用错误捕获，之后立刻恢复默认错误处理，避免忽略其他代码的错误：

'=========================================================
' Clear existing conditional formatting with lower borders
'=========================================================
Dim ws As Worksheet
Dim cf As Variant
Dim i As Long
Dim hasBottomBorder As Boolean

Set ws = ThisWorkbook.Sheets("Sheet1")

' 倒序遍历避免删除规则时索引混乱
For i = ws.Cells.FormatConditions.Count To 1 Step -1
    Set cf = ws.Cells.FormatConditions(i)
    hasBottomBorder = False
    
    ' 仅针对Borders属性的访问做错误捕获
    On Error Resume Next
    hasBottomBorder = (cf.Borders(xlBottom).LineStyle <> xlNone)
    On Error GoTo 0 ' 恢复默认错误处理
    
    If hasBottomBorder Then
        cf.Delete
    End If
Next i

方案2：先判断条件格式类型 #

部分条件格式类型（如UniqueValues）本身不支持Borders属性，先判断类型可减少错误触发概率：

'=========================================================
' Clear existing conditional formatting with lower borders
'=========================================================
Dim ws As Worksheet
Dim cf As Variant
Dim i As Long
Dim hasBottomBorder As Boolean

Set ws = ThisWorkbook.Sheets("Sheet1")

For i = ws.Cells.FormatConditions.Count To 1 Step -1
    Set cf = ws.Cells.FormatConditions(i)
    hasBottomBorder = False
    
    ' 仅对支持Borders的FormatCondition类型做检查
    If TypeOf cf Is FormatCondition Then
        On Error Resume Next
        hasBottomBorder = (cf.Borders(xlBottom).LineStyle <> xlNone)
        On Error GoTo 0
    End If
    
    If hasBottomBorder Then
        cf.Delete
    End If
Next i

方案说明 #

• 两种方案都避免了全局On Error Resume Next带来的隐患，仅针对性处理Borders属性的访问错误
• 用布尔变量hasBottomBorder存储检查结果，逻辑更清晰易读
• 保留倒序遍历的逻辑，确保删除规则时不会跳过条目

内容的提问来源于stack exchange，提问作者susi33