调用VBA的ParseJson遇Json-vba 2.2.3的10001错误，如何修正？

The 10001 error ("Expected '{' or '['") you're hitting is almost certainly due to a UTF-8 Byte Order Mark (BOM) at the start of the .json file's response. The text version of the file likely doesn't include this BOM, which is why it works fine. Here's how to fix it:

Root Cause #

When you fetch the .json URL, the server returns content prefixed with the UTF-8 BOM (bytes EF BB BF). The Json-VBA parser doesn't recognize this BOM as valid leading content, so it throws an error because it can't find the expected { or [ at the very start. The text version of the file is served without this BOM, hence no parsing issue.

Solution: Strip the BOM Before Parsing #

Instead of using http.responseText directly, read the raw byte data, check for and remove the BOM, then convert the cleaned bytes to a UTF-8 string for parsing. Here's your modified code:

Sub jsontest()
    Dim http As Object
    Dim responseBytes() As Byte
    Dim jsonText As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://bin.codingislove.com/ayequrimiy.json", False
    http.send
    
    ' Grab raw response bytes to avoid encoding/BOM issues with responseText
    responseBytes = http.responseBody
    
    ' Convert bytes to UTF-8 string, stripping BOM if present
    jsonText = GetCleanUtf8String(responseBytes)
    
    ' Now parse the cleaned JSON string
    MsgBox ParseJson(jsonText)("Count")
End Sub

' Helper function to convert UTF-8 bytes to string, removing BOM
Function GetCleanUtf8String(bytes() As Byte) As String
    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
    
    With stream
        .Charset = "UTF-8"
        .Open
        
        ' Check for UTF-8 BOM (first 3 bytes: EF BB BF)
        If UBound(bytes) >= 2 Then
            If bytes(0) = &HEF And bytes(1) = &HBB And bytes(2) = &HBF Then
                ' Write bytes starting after the BOM
                .Write MidB(bytes, 4)
            Else
                .Write bytes
            End If
        Else
            .Write bytes
        End If
        
        .Position = 0
        GetCleanUtf8String = .ReadText
        .Close
    End With
    
    Set stream = Nothing
End Function

How This Works #

1. responseBody instead of responseText: This gets the raw byte data, avoiding any automatic (and potentially flawed) encoding conversion by MSXML2.XMLHTTP.
2. BOM Check: We inspect the first 3 bytes to see if they match the UTF-8 BOM. If they do, we skip those bytes when writing to the stream.
3. ADODB.Stream Conversion: Using ADODB.Stream ensures proper UTF-8 decoding without retaining the BOM.

Verify the Issue (Optional) #

To confirm the BOM is the problem, add this line to your original code before calling ParseJson:

Debug.Print Asc(Left(http.responseText, 1)) ' Should return 65279 if BOM is present

A return value of 65279 (the Unicode character for the UTF-8 BOM) confirms the issue.

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