☰

VBA 类型不匹配错误

tag-icon • tag-icon microsoft-excel vba
VBA 类型不匹配错误

我正在尝试使用 VBA 创建一个循环，它将更新选定范围的边框颜色。循环遍历我想要更新的每个边框的数组。这是我根据一些教程得出的结论

Sub Test02()
    Dim border(1 To 5) As String
    Dim i As Variant

    border(1) = "xlEdgeTop"
    border(2) = "xlEdgeBottom"
    border(3) = "xlEdgeLeft"
    border(4) = "xlEdgeRight"
    border(5) = "xlInsideHorizontal"

    Range("'Timesheet Calculator.xlsb'!Feilds").Select

    For Each i In border
        With Selection.Borders(i)
            .LineStyle = xlContinuous
            .color = -2315144
            .TintAndShade = 0
            .Weight = xlThin
        End With
    Next i

End Sub

我在“With Selection.Borders(i)”行上不断收到“运行时错误‘13’：类型不匹配”错误。我不确定要为我的数组使用哪种数据类型才能避免此错误。

任何帮助都将不胜感激。

答案1

将数组更改为 Long，因为边框类型可以这样表达。

使用数组，简单的 For 比 For Each 更快。

我也改变了数组的名称，只是为了不像命令。

然后使用：Range.Borders(brder(i))

Sub Test02()
    Dim brder(1 To 5) As Long
    Dim i As Long

    brder(1) = xlEdgeTop
    brder(2) = xlEdgeBottom
    brder(3) = xlEdgeLeft
    brder(4) = xlEdgeRight
    brder(5) = xlInsideHorizontal

    For i = LBound(brder) To UBound(brder)
        With Range("'Timesheet Calculator.xlsb'!Feilds").Borders(brder(i))
            .LineStyle = xlContinuous
            .Color = -2315144
            .TintAndShade = 0
            .Weight = xlThin
        End With
    Next i

End Sub

相关内容