如何修正VBA代码解析嵌套JSON集合并写入Excel天气数据？

问题根源 #

运行时错误5（无效过程调用）或13（类型不匹配），本质是原代码未适配API返回的按天嵌套小时的集合结构——原逻辑直接遍历扁平的小时列表，现在需要先遍历forecastday集合，再遍历每个日期下的hour子集合。

修正后的完整代码 #

Sub FetchAndWriteWeatherData()
    Dim jsonObj As Object
    Dim forecastDays As Collection
    Dim singleDay As Object
    Dim hours As Collection
    Dim singleHour As Object
    Dim ws As Worksheet
    Dim rowNum As Long
    
    ' 初始化工作表和起始行
    Set ws = ThisWorkbook.Worksheets("Setup")
    rowNum = 4 ' 从第4行开始写入
    
    ' 假设已通过VBA-JSON获取并解析好API数据到jsonObj
    ' 示例：Set jsonObj = JsonConverter.ParseJson(HTTPResponseText)
    
    ' 检查forecast和forecastday是否存在，避免空引用
    If Not jsonObj.exists("forecast") Then Exit Sub
    Set forecastDays = jsonObj("forecast")("forecastday")
    
    ' 双层循环遍历嵌套集合
    For Each singleDay In forecastDays
        Set hours = singleDay("hour")
        For Each singleHour In hours
            ' 写入time_epoch到A列
            ws.Cells(rowNum, "A").Value = singleHour("time_epoch")
            ' 写入temp_c到B列
            ws.Cells(rowNum, "B").Value = singleHour("temp_c")
            rowNum = rowNum + 1 ' 行号递增
        Next singleHour
    Next singleDay
    
    ' 释放对象
    Set singleHour = Nothing
    Set hours = Nothing
    Set singleDay = Nothing
    Set forecastDays = Nothing
    Set jsonObj = Nothing
    Set ws = Nothing
End Sub

关键修正点 #

• 双层循环结构：外层遍历forecastday集合（每个元素是单天的天气数据），内层遍历该日期下的hour集合（每个元素是小时级数据）
• 类型安全判断：通过exists方法检查关键节点是否存在，避免因API返回结构异常导致的空引用错误
• 集合元素访问：VBA-JSON解析后的集合元素是Dictionary对象，直接通过键名（如"time_epoch"）取值即可，无需额外类型转换（若API返回数值类型，VBA会自动适配）

常见问题排查 #

• 若仍报错13：检查time_epoch或temp_c的返回类型，若API返回字符串格式的数值，需用CLng()或CDbl()转换，比如ws.Cells(rowNum, "A").Value = CLng(singleHour("time_epoch"))
• 若报错5：确认forecastday和hour确实是VBA集合对象，而非数组，可通过TypeName(forecastDays)验证类型

内容的提问来源于stack exchange，提问作者laisp-lostlamb