VBA将JSON值输出到Excel工作表：解析API返回JSON时出现438错误

438错误的核心成因 #

报错本质是“对象不支持当前调用的属性或方法”，触发原因来自两个核心层面：

• 跨语言对象兼容性问题：通过MSScriptControl.ScriptControl.Eval返回的是JS原生COM对象，不支持VBA语法的对象("键名")、对象(下标)这类索引取值方式，直接在VBA侧对这类对象做层级取值，必然触发方法不匹配。
• 下标规则不匹配：日常使用的JsonConverter解析返回的是VBA原生嵌套Dictionary结构，数组下标从1开始，参考教程里用的data(0)是JS生态的0基下标，对应到JsonConverter的解析结果里也会触发调用错误。

可落地的修正实现 #

方案1：适配当前ScriptControl环境的写法 #

完全绕开跨对象调用的问题，所有JSON层级解析逻辑在JS运行时内完成，仅把最终需要的字段值返回给VBA，避免直接操作跨语言的JS对象。
修正后的完整代码：

Sub getData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2") '指定输出工作表
    
    Dim URL As String
    Dim scriptControl As Object
    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"
    
    URL = "https://api.gleif.org/api/v1/lei-records?filter[lei]=5493001ZDI5KQPZTPI85"
    
    Dim responseText As String
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "GET", URL, False
        '补充API要求的请求头，避免被接口拦截
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0.0.0"
        .send
        responseText = .responseText
        .abort
    End With
    
    '在JS环境内完成全链路取值，仅返回最终字符串结果
    scriptControl.Eval "var targetObj = (" & responseText & ");"
    Dim legalName As String
    legalName = scriptControl.Eval("targetObj.data[0].attributes.legalName.name")
    
    ws.Cells(1, 1).Value = legalName
End Sub

方案注意点：

• 绝对不要把scriptControl.Eval返回的JS对象赋值给VBA对象变量后再做层级取值，所有带点访问、数组下标的解析逻辑必须全部拼入JS执行的代码段中。
• 该方案仅支持32位Office环境，64位Office会因为缺少MSScriptControl组件无法运行。

方案2：回归JsonConverter解析方案的修正 #

所谓JsonConverter失效的问题，本质是之前的请求逻辑缺少GLEIF接口要求的请求头，导致返回了非JSON格式的拦截页面内容，不是解析器本身故障。修正后代码：

Sub getData_WithJsonConverter()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")
    
    Dim URL As String
    URL = "https://api.gleif.org/api/v1/lei-records?filter[lei]=5493001ZDI5KQPZTPI85"
    
    Dim json As Object
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "GET", URL, False
        '补充接口要求的User-Agent头，否则会被反向代理拦截
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0.0.0"
        .send
        Set json = JsonConverter.ParseJson(.responseText)
    End With
    
    'JsonConverter的数组下标从1开始，替换之前教程里的0基下标
    ws.Cells(1, 1).Value = json("data")(1)("attributes")("legalName")("name")
End Sub

方案注意点：

• JsonConverter是纯VBA实现的解析器，兼容32/64位全版本Office，稳定性远高于依赖系统组件的ScriptControl方案。
• 该方案的所有解析逻辑都在VBA侧完成，支持键名索引的取值写法，没有跨COM接口的兼容问题。

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