将JSON解析代码改为数组后出现Run-time error 9下标越界问题求助

问题根源 #

你碰到的Run-time error 9（Subscript out of range）是数组行范围定义错误导致的：

• 原代码里lastRow = Sheet2.Range("A1").End(xlUp).Row取的是工作表现有数据的最后一行，但API返回的timeentries条目数可能远大于这个数值，循环时i持续递增，最终超过数组1 To lastRow的行上限，触发下标越界。
• 最后写入工作表时用Resize(lastRow, 12)也不合理，应该用实际处理的条目数，而非原表的行数。

修复后的代码 #

Dim json As Object
Dim timeEntry As Object
Dim ti As Object
Dim entryCount As Long
Dim i As Long ' 显式声明变量类型，避免默认变体类型的潜在问题

Set json = JsonConverter.ParseJson(Data)
' 先获取API返回的timeentries总数量
entryCount = json("timeentries").Count

' 根据条目总数定义数组行范围，确保能容纳所有新数据
Dim dataArray() As Variant
ReDim dataArray(1 To entryCount, 1 To 12)

i = 1 ' 数组从1开始计数，更贴合数组索引的常规用法
For Each timeEntry In json("timeentries")
    dataArray(i, 1) = timeEntry("projectName")
    dataArray(i, 5) = timeEntry("taskName")
    dataArray(i, 9) = timeEntry("description")
    dataArray(i, 10) = timeEntry("clientName")

    Set ti = timeEntry("timeInterval")
    dataArray(i, 11) = ti("start")
    dataArray(i, 7) = ti("duration")
    i = i + 1
Next timeEntry

' 从A2开始写入，仅写入实际有数据的行数
Sheet2.Range("A2").Resize(entryCount, 12).Value = dataArray

关键改动说明 #

1. 基于API返回数量定义数组：用json("timeentries").Count拿到真实的条目总数，以此确定数组行范围，彻底避免下标越界问题。
2. 优化数组索引逻辑：数组从1开始计数，避免原代码中从2开始导致的数组第一行空间浪费。
3. 精准写入数据范围：写入工作表时用实际条目数entryCount调整写入范围，不会覆盖工作表中多余的行。

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