VBA - JsonConverter下载与使用

下载步骤

JsonConverter是一个用于VBA解析JSON数据的库，使用步骤：

1. 上github下载(https://github.com/VBA-tools/VBA-JSON)
2. 下载后打开excel中的visual basic编辑器，导入下载好的JsonConverter.bas文件
3. 在使用这个库前需要在菜单栏中的工具选择引用，勾选microsoft scripting runtime和vbscript regular expressions 5.5，然后确定

简单操作

Dim Json As Object
Set Json = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

' Json("a") -> 123
' Json("b")(2) -> 2
' Json("c")("d") -> 456
Json("c")("e") = 789

Sub SerializeNestedObjectToJson()
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary
    
    ' 添加嵌套数据
    dict.Add "name", "Jane Doe"
    dict.Add "age", 25
    dict.Add "address", New Scripting.Dictionary
    dict("address").Add "street", "123 Main St"
    dict("address").Add "city", "New York"
    dict("address").Add "zip", "10001"
    
    ' 将嵌套对象序列化为JSON字符串
    Dim jsonString As String
    jsonString = JsonConverter.ConvertToJson(dict)

输出结果

{
    "name": "Jane Doe",
    "age": 25,
    "address": {
        "street": "123 Main St",
        "city": "New York",
        "zip": "10001"
    }
}