如何修复VBA填充数组时出现的'Subscript out of range'错误？

核心错误原因 #

• 数组维度误用：通过dataArr = wsSim.Range(...).Value获取的是二维数组（哪怕是单列数据，结构也是dataArr(行号, 1)），但你直接用dataArr(year + 1)访问，会触发下标越界或类型不匹配错误。
• 无效的ReDim语句：ReDim dataArr(1 To term + 1)完全多余，因为Range.Value会直接覆盖数组的维度和内容，之前的ReDim操作会被忽略。
• 变量声明不规范：Dim term, tranche As Integer里只有tranche是Integer类型，term默认是Variant，可能引发数值计算异常。
• 数组越界风险：需确保term + 13 + 41 <= 94（即term ≤ 40），否则resArr的下标会超出1-94的定义范围。

修复后的代码 #

Public Sub Simulate()
    Dim term As Integer, tranche As Integer
    Dim dataArr() As Variant, resArr(1 To 94) As Variant
    Dim counter As Integer, year As Integer
    Dim noiterations As Long, Tresv As Variant

    noiterations = wsPrm.Range("iterations").Value
    tranche = wsPrm.Range("tranche").Value
    term = wsPrm.Range("Period").Value
    Tresv = wsPrm.Range("Tresv").Value

    ' 提前检查Period值是否会导致数组越界
    If term + 54 > 94 Then
        MsgBox "Period值过大，会触发数组越界，请设置Period≤40"
        Exit Sub
    End If

    For counter = 1 To noiterations
        ' 将单列二维数组转成一维数组，方便直接按索引访问
        dataArr = Application.Transpose(wsSim.Range(wsSim.Cells(6, 43), wsSim.Cells(6 + term, 43)).Value)
        
        For year = 0 To term
            resArr(year + 13) = dataArr(year + 1)
            resArr(year + 13 + 41) = dataArr(year + 1)
        Next year
        
        ' 示例：将构建好的resArr写入工作表某一行，可根据需求调整目标位置
        ' wsTarget.Cells(counter, 1).Resize(1, 94).Value = resArr
    Next counter
End Sub

关键调整说明 #

• 用Application.Transpose把单列二维数组转为一维数组，解决维度不匹配的问题。
• 补全所有变量的类型声明，避免Variant类型带来的潜在问题。
• 添加越界检查，提前拦截无效的Period参数。
• 注释了数组写入工作表的代码，可根据实际需求修改目标工作表和位置。

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