使用Variant数组时持续触发Subscript out of range (Error 9)问题排查

问题根源 #

1. Range赋值生成二维数组：从Excel单元格区域（哪怕是单列）赋值给Variant数组时，得到的是二维数组（结构为arr(行号, 列号)），但你的IsInArray函数用了一维索引arr(i)直接访问，必然触发下标越界。
2. ReDim Preserve误用：对二维数组执行ReDim Preserve OldReportsArray(UBound(OldReportsArray)+1)不符合规则——ReDim Preserve仅允许修改数组的最后一维，二维数组的第一维是行，直接修改会导致崩溃。

修复方案 #

1. 辅助函数：将单列二维数组转为一维数组

Private Function ConvertTo1D(arr As Variant) As Variant
    Dim tempArr() As Variant
    Dim i As Long
    If IsArray(arr) Then
        ReDim tempArr(LBound(arr, 1) To UBound(arr, 1))
        For i = LBound(arr, 1) To UBound(arr, 1)
            tempArr(i) = arr(i, 1)
        Next i
        ConvertTo1D = tempArr
    Else
        ' 单个值的情况，返回包含该值的一维数组
        ReDim tempArr(1 To 1)
        tempArr(1) = arr
        ConvertTo1D = tempArr
    End If
End Function

2. 修正主逻辑中的数组处理

'Loop through all files in Folder
Do While TargetString <> ""
    Set SourceWB = Workbooks.Open(NavigationString & TargetString)

    ' 获取列表数据并赋值给数组
    If Not BankWS.Range("I3") = "" Then
        BottomOfList = BankWS.Range("I2").End(xlDown).Row ' 用Row代替Address更高效
        OldReportsArray = BankWS.Range("I2:I" & BottomOfList).Value
    Else
        OldReportsArray = BankWS.Range("I2:I3").Value
    End If
    
    ' 转为一维数组
    OldReportsArray = ConvertTo1D(OldReportsArray)
    ' 正确扩展一维数组
    ReDim Preserve OldReportsArray(LBound(OldReportsArray) To UBound(OldReportsArray) + 1)

    ' 检查当前文件是否已在数组中
    If IsInArray(SourceWB.Name, OldReportsArray) = True Then
        SourceWB.Close SaveChanges:=False ' 明确关闭时不保存
        GoTo NextLoop
    End If

' ... 此处省略后续未展示的代码 ...

NextLoop:
Loop

3. 修正IsInArray函数

'Check Passed String inside of Array, return true or false
Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i As Long
    ' 处理非数组的单个值情况
    If Not IsArray(arr) Then
        IsInArray = (arr = stringToBeFound)
        Exit Function
    End If
    For i = LBound(arr) To UBound(arr)
        ' 跳过空值，避免类型不匹配
        If Not IsEmpty(arr(i)) And arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False
End Function

额外优化建议 #

• 避免使用Activate，直接通过工作表对象引用单元格，代码更稳定高效。
• 关闭工作簿时明确SaveChanges:=False，防止意外保存修改。
• 增加空值判断，避免数组元素为空时的类型匹配错误。

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