动态截取VBA数组子集时出现Error 2023错误的解决方法

问题根源 #

你用[ROW(c:d)]时，VBA会将c和d解析为工作表单元格引用，而非你定义的VBA变量，这导致Index函数无法获取正确的行号数组，从而抛出错误。

两种可行解决方案 #

方案1：用Evaluate动态拼接行号范围

通过字符串拼接将变量c和d的值代入ROW函数，再用Evaluate执行，生成正确的行号数组：

b = Application.Average(Application.Index(MyArray, Evaluate("ROW(" & c & ":" & d & ")"), 2))

方案2：手动生成行号数组

完全脱离Excel工作表函数，直接在VBA中创建包含目标行号的数组，避免Evaluate的解析问题：

' 生成c到d的行号数组
Dim rowNums() As Long
ReDim rowNums(c To d)
Dim i As Long
For i = c To d
    rowNums(i) = i
Next i

' 计算平均值
b = Application.Average(Application.Index(MyArray, rowNums, 2))

完整修正后的代码 #

Sub test2()
    Dim MyArray() As Double
    Dim c As Long ' 行号用Long类型更合理，避免浮点类型转换
    Dim d As Long
    Dim a As Double, b As Double, b2 As Double
    
    ' 数组索引匹配实际赋值范围（0基，第一维0-4，第二维0-1）
    ReDim MyArray(4, 1)
    
    c = 3
    d = 5
    
    ' 给数组赋值
    MyArray(0, 0) = 1
    MyArray(1, 0) = 2
    MyArray(2, 0) = 3
    MyArray(3, 0) = 4
    MyArray(4, 0) = 5
    MyArray(0, 1) = 10
    MyArray(1, 1) = 20
    MyArray(2, 1) = 30
    MyArray(3, 1) = 40
    MyArray(4, 1) = 50
    
    ' 固定范围写法（正确）
    a = Application.Average(Application.Index(MyArray, [ROW(3:5)], 2))
    
    ' 动态变量写法1：Evaluate拼接
    b = Application.Average(Application.Index(MyArray, Evaluate("ROW(" & c & ":" & d & ")"), 2))
    
    ' 动态变量写法2：手动生成行号数组
    Dim rowNums() As Long
    ReDim rowNums(c To d)
    Dim i As Long
    For i = c To d
        rowNums(i) = i
    Next i
    b2 = Application.Average(Application.Index(MyArray, rowNums, 2))
    
    ' 输出结果验证
    Debug.Print "固定范围结果：" & a ' 输出40
    Debug.Print "动态拼接结果：" & b ' 输出40
    Debug.Print "手动数组结果：" & b2 ' 输出40
End Sub

额外优化提示 #

• 变量类型：行号是整数，将c、d定义为Long而非Double，避免不必要的类型转换。
• 数组维度：根据实际赋值范围调整ReDim参数，避免创建冗余的数组空间。

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