Operation in VBA code throws type 13 mismatch error - entering it in direct window does not.

[vinkbram]

I am running into a problem I have no idea how to tackle, and the solutions provided otherwise in the issues do not seem to help with this problem. The debug.print line throws a type 13 Mismatch error - however if I just enter Print Parsed(1)("Description")(1)("nl_NL") in the direct window, it returns the expected value. Any other operations in the code also result in this error.

Sub Example()
Dim FSO As New FileSystemObject
Dim TS As TextStream
Dim Json As String
Dim Parsed As Variant

Set TS = FSO.OpenTextFile("C:\Example.txt")
Json = TS.ReadAll
TS.Close

Set Parsed = JsonConverter.ParseJson(Json)

Debug.Print Parsed(1)("Description")(1)("nl_NL")
End Sub

My JSON looks like this:

[ {
  "Description" : [ {
    "en_US" : null,
    "nl_BE" : "Text in dutch",
    "fr_BE" : null,
    "fr_LU" : null,
    "nl_NL" : "Text in dutch",
    "fr_FR" : null,
    "de_DE" : null
  } ],
  "Amount" : 3.3,
  "Code" : "1"
}, {
  "Description" : [ {
    "en_US" : null,
    "nl_BE" : "Geen",
    "fr_BE" : null,
    "fr_LU" : null,
    "nl_NL" : "Geen",
    "fr_FR" : null,
    "de_DE" : null
  } ],
  "Amount" : 0.0,
  "Code" : "99"
} ]

Any help would be much appreciated!
Best regards,

Bram

Example.txt

[ricardoba13]

Try with:

Debug.Print CStr(Parsed(1)("Description")(1)("nl_NL"))

CStr should convert the returned value to the best String approach

[vinkbram]

Hi Ricardo,

Thanks for your response!
Sadly,
Debug.Print CStr(Parsed(1)("Description")(1)("nl_NL"))

returns the same error; as does
If IsNull(Parsed(1)("Description")(1)("nl_NL")) Then

Also, both these lines return the same error, regardless if I declare Check as variant, dictionary, or not at all.

    Dim Check
    Check = Parsed(1)("Description")(1)("nl_NL")
    Set Check = Parsed(1)("Description")(1)("nl_NL")

Any further assistance would be much appreciated!

Best regards,
Bram
(edit: formatting)

[vinkbram]

My current workaround is as follows;
Example above was parsed into a variant, which seems to exhibit this behaviour when addressing other arrays after the top-level array.

To alleviate the issues with an unnamed top level array, I decode the json string, then encode it again with "{ ""Object"" : " & original string & " }" to have an object as the top level, which allows it to be parsed into a dictionary, which does not have the mentioned behaviour, and requires references to be written as:

Check = Parsed("Object")(1)("Description")(1)("nl_NL")

Which works!

[timhall]

@vinkbram Sorry about this, that's a bizarre error. I'm glad you found a workaround, but I'll investigate and see if I can fix the underlying issue.

[whisperio]

I ran into error 13 (Office 16). Couple of lines needed to be changed:

• All references to Dictionary were changed to form Scripting.Dictionary
• I made small modifications to function jsonParseObject:

Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Scripting.Dictionary
    Dim json_Key As String
    Dim json_NextChar As String
    
    Dim retVal As Scripting.Dictionary
    Set retVal = New Scripting.Dictionary
    'Set json_ParseObject = New Scripting.Dictionary
    
    json_SkipSpaces json_String, json_Index
    If VBA.Mid$(json_String, json_Index, 1) <> "{" Then
        Err.Raise 10001, "JSONConverter", json_ParseErrorMessage(json_String, json_Index, "Expecting '{'")
    Else
        json_Index = json_Index + 1

        Do
            json_SkipSpaces json_String, json_Index
            If VBA.Mid$(json_String, json_Index, 1) = "}" Then
                json_Index = json_Index + 1
                Set json_ParseObject = retVal
                Exit Function
            ElseIf VBA.Mid$(json_String, json_Index, 1) = "," Then
                json_Index = json_Index + 1
                json_SkipSpaces json_String, json_Index
            End If

            json_Key = json_ParseKey(json_String, json_Index)
            json_NextChar = json_Peek(json_String, json_Index)
            If json_NextChar = "[" Or json_NextChar = "{" Then
                retVal.Add json_Key, json_ParseValue(json_String, json_Index)
            Else
                retVal.Add json_Key, json_ParseValue(json_String, json_Index)
            End If
        Loop
    End If
End Function