为什么我的var返回不兼容问题与错误13?

Question

我试图在VBA中解析Json。我从一个API收集数据，返回json格式的字符串。我使用JsonConverter来解析我的字符串。现在当我想搜索它，我得到一个错误13不兼容类型。

查看下面我的Java API:

@GetMapping("/rest/collectData/excel/exportAll")
public HashMap<Object, Object> collectAll(){
HashMap<Object, Object> result = new HashMap<>();
String sql = "SELECT affair_code AS codeAffair, name, amount, end_date AS state FROM service_record WHERE affair_code IS NOT NULL AND affair_code != ''";
List<Map<String, Object>> allServiceRecords = jdbcTemplate.queryForList(sql);

if(allServiceRecords != null && allServiceRecords.size() >0){
result.put("result", true);
for(Map<String, Object> serviceRecord : allServiceRecords){
HashMap<Object, Object> details = new HashMap<>();
if(result.containsKey(serviceRecord.get("codeAffair"))){
details.put("alone", false);
details.put("message", "Plusieurs prestations ont été trouvées.");
} else {
details.put("alone", true);
details.put("name", (String) serviceRecord.get("name"));
details.put("amount", (Double) serviceRecord.get("amount"));
details.put("state", ((Date) serviceRecord.get("state")).compareTo(new Date()) < 0 ? "En cours" : "Clos");
}
result.put(serviceRecord.get("codeAffair"), details);
}
} else{
result.put("result", false);
result.put("error", "La liste n'est pas définie, ou vide.");
}
return result;
}

返回json:

{"03-045251":{"alone":true,"amount":0.0,"name":"name1","state":"En cours"},"03_05494":{"alone":true,"amount":16743.0,"name":"name2","state":"En cours"}}

首先，我执行sql请求收集我的数据，并把它放在一个映射。然后，我把这个地图发送到我的excel VBA。

现在看到我的VBA:

Sub JsonDataSqwal()
firstRow = Range("A" & 11).End(xlDown).Row
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim httpObject As Object
Set httpObject = CreateObject("MSXML2.XMLHTTP")

sUrl = "http://localhost/rest/collectData/excel/exportAll"
sRequest = sUrl
httpObject.Open "GET", sRequest, False
httpObject.send
sGetResult = httpObject.responseText
If Not IsNull(sGetResult) Then
Dim oJson As Object
JsonConverter.JsonOptions.AllowUnquotedKeys = True
Set oJson = JsonConverter.ParseJson(sGetResult)

Dim i As Long
For i = firstRow To lastRow
Dim codeAffString As String
codeAffString = Cells(i, 4)
Debug.Print oJson(codeAffString)("name")
Next i

End If

结束子

目前，我试着打印我的数据。循环从列中收集值，该列包含所有codeAffair，如00_00000或00-00000

这是这些数据，我试图在我的vba代码中使用varcodeAffString。当我执行我的代码，我总是得到错误13关于类型不兼容。

为了解决这个问题，我尝试了很多方法:

1. 在变量
中添加引号
2. 将我的HashMap重命名为HashMap<String, Object>
3. 允许取消引用键
4. 更改后台程序
5. 替换我的值"""" + codeAffairString + """"
6. 将我的var替换为固定字符串"00_00000"。
7. 使用vartype函数检查var的类型，该函数为字符串索引返回8。

现在我实在想不出别的办法来解决这个问题如果有人看出我错在哪里……

谢谢你!

Answer 1

简单测试一下:

我使用了你给的JSON字符串和你给codeAffString的值来构建一个最小的可重复的例子，它不会产生任何错误:

Sub test()
Const JsonString As String = "{""03-045251"":{""alone"":true,""amount"":0.0,""name"":""name1"",""state"":""En cours""},""03_05494"":{""alone"":true,""amount"":16743.0,""name"":""name2"",""state"":""En cours""}}"
Const codeAffString As String = "03-045251"

Dim oJson As Object
JsonConverter.JsonOptions.AllowUnquotedKeys = True
Set oJson = JsonConverter.ParseJson(JsonString)

Debug.Print oJson(codeAffString)("name")  ' outputs name1
End Sub

如果在JSON中找不到codeAffString，则会发生您描述的错误。

在你的代码中测试它:

For i = firstRow To lastRow
Dim codeAffString As String
codeAffString = Cells(i, 4)
If IsEmpty(oJson(codeAffString)) Then
Debug.Print codeAffString & " does not exist in the json"
Else
Debug.Print oJson(codeAffString)("name")
End If
Next i