excel - 范围数组

Question

我最近接到了加快公司一个大型宏的速度的任务。到目前为止，我已经完成了相当不错的工作，因为最后一位编写者或多或少使用了记录功能(使一些代码变得无关)，这导致我遇到这些格式化条件:

With Range("AH10:AP10").Interior
    .Pattern = xlSolid
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
End With
With Range("AH17:AP17").Interior
    .Pattern = xlSolid
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
End With
With Range("AH24:AP24").Interior
    .Pattern = xlSolid
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
End With
With Range("AH34:AP34").Interior
    .Pattern = xlSolid
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
End With
With Range("AH42:AP42").Interior
    .Pattern = xlSolid
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
End With

我的问题是，为什么这不起作用？

Dim RangeArr(5) As Variant
RangeArr(0) = Range("AH10:AP10")
RangeArr(1) = Range("AH17:AP17")
RangeArr(2) = Range("AH24:AP24")
RangeArr(3) = Range("AH34:AP34")
RangeArr(4) = Range("AH42:AP42")

For i = 0 To 4
With RangeArr(i).Interior
    .Pattern = xlSolid
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
End With
Next

我收到运行时错误 424，循环需要对象，但使用 msgbox 打印数组时，其值是这样的。我假设这是数组写错了，我只是不知道如何修复它。任何建议表示赞赏。谢谢!

Answer 1

你已经有了一个好的开始!您需要一个范围数组:

Sub human()
    Dim RangeArr(0 To 4) As Range
    Set RangeArr(0) = Range("AH10:AP10")
    Set RangeArr(1) = Range("AH17:AP17")
    Set RangeArr(2) = Range("AH24:AP24")
    Set RangeArr(3) = Range("AH34:AP34")
    Set RangeArr(4) = Range("AH42:AP42")

    For I = 0 To 4
    With RangeArr(I).Interior
        .Pattern = xlSolid
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
    End With
    Next

End Sub