VBA中是否存在可作用于数组的类似With语句的语法？

实现方案 #

核心思路 #

• 提前将spbe30、spbe60两张工作表的使用区域内容读取为二维数组，既可以替代工作表引用，还能大幅提升循环运行效率
• VBA的变体类型变量可直接存储数组，无需依赖With绑定工作表对象，判断后直接将对应数组赋值给变体变量即可使用
• 从工作表直接读取的数组默认是1起始的二维数组，第一维对应行号、第二维对应列号，和原有单元格引用逻辑高度匹配：Range("B"&i)对应数组第i行第2列、Range("AA"&i)对应第i行第27列、Range("T"&i)对应第i行第20列。

修改后代码 #

' 提前读取两个工作表内容到数组，放在逻辑执行前即可
Dim spbe30Arr As Variant, spbe60Arr As Variant
spbe30Arr = spbe30.UsedRange.Value
spbe60Arr = spbe60.UsedRange.Value

' 原有判断逻辑调整为赋值数组
Dim spbeArr As Variant
If spbe30array(i, 22) >= orders Then
    spbeArr = spbe30Arr
Else
    spbeArr = spbe60Arr
End If
            
' 原With块替换为直接操作数组
i = i + 1
Do While LCase(spbeArr(i, 2)) = "placement"
    If LCase(spbeArr(i, 27)) = "product" Then
        PPclicks = spbeArr(i, 20)
    ElseIf LCase(spbeArr(i, 27)) = "rest" Then
        ROSclicks = spbeArr(i, 20)
    End If
    i = i + 1
    ' 新增边界判断，防止i超出数组最大行号报错
    If i > UBound(spbeArr, 1) Then Exit Do
Loop

可选优化 #

如果需要适配0起始的数组习惯，可以在读取数组后做下标转换，上述1起始的写法和原有行号逻辑完全对齐，不需要额外调整计数规则。

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