从含特殊字符的JSON响应中提取values值的VBA技术问题

问题背景 #

需要实现报表自动化，从API端点获取JSON响应（存储在strResult变量中），提取其中的values部分。尝试过正则表达式匹配但返回空集合，使用VBA-JSON解析变量中的JSON也未成功，希望得到可扩展的对象式解决方案。

给出的API返回JSON（HTML转义格式）：

{"totalCount":3,"nextPageKey":null,"resolution":"4h","result":[{"metricId":"builtin:service.keyRequest.count.total:names","dataPointCountRatio":3.6E-6,"dimensionCountRatio":3.0E-5,"data":[{"dimensions":["IMPORTANT_ARL_#1","SERVICE_METHOD-HEX#1"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#1","dt.entity.service_method.name":"IMPORTANT_ARL_#1"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[null,1,30,26,null,null]},{"dimensions":["IMPORTANT_ARL_#2","SERVICE_METHOD-HEX#2"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#2","dt.entity.service_method.name":"IMPORTANT_ARL_#2"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[60,371,1764,1964,1707,1036]},{"dimensions":["IMPORTANT_ARL_#3","SERVICE_METHOD-HEX#3"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#3","dt.entity.service_method.name":"IMPORTANT_ARL_#3"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[9,6,1077,1171,462,null]}]}]}

正则方案失效原因 #

你使用的正则表达式(?<=values\&quot;\:\[)(.+?)(?=\])在VBA中无法工作，核心原因是：

• VBA内置的RegExp对象不支持正向零宽断言（(?<=...)语法），这是导致匹配返回空集合的直接原因。
• 正则处理JSON本身存在稳定性问题：JSON格式的微小变化（如空格、换行、字段顺序调整）都会导致匹配失效，完全不适用于可扩展的API数据处理场景。

推荐方案：VBA-JSON解析变量中的JSON #

VBA-JSON可以直接解析变量中的JSON字符串，无需从文件读取，且能将JSON转为可操作的对象，完美满足可扩展需求。

步骤1：准备VBA-JSON模块 #

将VBA-JSON的代码导入到你的VBA项目中（复制官方代码到新建模块即可）。

步骤2：解析JSON并提取values #

Sub ExtractJSONValues()
    Dim strResult As String
    ' 假设strResult已存储API返回的HTML转义格式JSON
    strResult = "{"totalCount":3,"nextPageKey":null,"resolution":"4h","result":[{"metricId":"builtin:service.keyRequest.count.total:names","dataPointCountRatio":3.6E-6,"dimensionCountRatio":3.0E-5,"data":[{"dimensions":["IMPORTANT_ARL_#1","SERVICE_METHOD-HEX#1"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#1","dt.entity.service_method.name":"IMPORTANT_ARL_#1"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[null,1,30,26,null,null]},{"dimensions":["IMPORTANT_ARL_#2","SERVICE_METHOD-HEX#2"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#2","dt.entity.service_method.name":"IMPORTANT_ARL_#2"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[60,371,1764,1964,1707,1036]},{"dimensions":["IMPORTANT_ARL_#3","SERVICE_METHOD-HEX#3"],"dimensionMap":{"dt.entity.service_method":"SERVICE_METHOD-HEX#3","dt.entity.service_method.name":"IMPORTANT_ARL_#3"},"timestamps":[1667289600000,1667304000000,1667318400000,1667332800000,1667347200000,1667361600000],"values":[9,6,1077,1171,462,null]}]}]}"
    
    ' 第一步：将HTML转义的双引号替换为标准双引号
    strResult = Replace(strResult, """, """")
    
    ' 第二步：解析JSON为对象
    Dim json As Object
    Set json = JsonConverter.ParseJson(strResult)
    
    ' 第三步：遍历JSON层级提取所有values
    Dim resultItem As Object
    Dim dataItem As Object
    Dim values As Variant
    Dim allValues As Collection
    Set allValues = New Collection
    
    For Each resultItem In json("result")
        For Each dataItem In resultItem("data")
            values = dataItem("values")
            ' 将values存入集合，方便后续报表处理
            allValues.Add values
            ' 调试输出：打印当前values的字符串形式
            Debug.Print "Values: " & Join(Replace(Replace(values, "null", "Empty"), "Empty", ""), ", ")
        Next dataItem
    Next resultItem
    
    ' 后续可从allValues集合中提取数据生成报表
End Sub

方案优势 #

• 可扩展性：通过对象层级访问数据，无论API返回的JSON结构如何调整（只要字段名不变），只需修改遍历的层级即可适配。
• 稳定性：完全遵循JSON语法解析，不会因格式细节变化失效。
• 易用性：可以直接操作解析后的对象，提取任意字段（如dimensions、timestamps），满足报表自动化的多数据需求。

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