VBA-tools/VBA-JSON

Will no process leading/trailing JSON brackets []

jpmbiz70 opened this issue · 5 comments

jpmbiz70 commented

JSON spec includes a leading/trailing bracket. It is an array of objects. Subsequently, Microsoft VBA throws a "Run-time error '424: Object required" error. If I remove the leading/trailing brackets it works fine. This either appears to be a bug in VBA for Excel or this tool.

Nick-vanGemeren commented

The JSON array [a,b,c ...] is a (possibly empty) list of JSON values, not necessarily JSON objects.
It's not clear what your issue is. Can you post a simple code example which reproduces the problem?

TomasDohnal7 commented

The error can be reproduced, if you try to parse JSON:
{ "_Issues": "Issues", "description": "description", "id": "123", "milestonesList": "", "modulesList": "Module1", "priority": "", "projects": [ { } ], "projectsNamesList": "affas", "requirementsEstimate": "", "requirementsID": "2977", "requirementsLevel1": "Req1", "requirementsLevel2": "Req2", "requirementsLevel3": "blavla zak\u00e1zat", "requirementsProjectName": "Future", "requirementsState": "Sleeping", "requirementsTitle": "Something zak\u00e1zat", "sprint": ".4-Someday", "state": "Sleeping", "typeB": "Suggestion" }

Nick-vanGemeren commented

I did ask for a code example, not just the long input string.
This routine:

Sub Issue254()
    Dim str As String
    Dim result As Object
    str = "{ ""_Issues"": ""Issues"", ""description"": ""description"", ""id"": ""123"", " _
        & "  ""milestonesList"": """", ""modulesList"": ""Module1"", ""priority"": """", " _
        & "  ""projects"": [ { } ], ""projectsNamesList"": ""affas"", " _
        & "  ""requirementsEstimate"": """", " _
        & "  ""requirementsID"": ""2977"", ""requirementsLevel1"": ""Req1"", " _
        & "  ""requirementsLevel2"": ""Req2"", ""requirementsLevel3"": ""blavla zak\u00e1zat"", " _
        & "  ""requirementsProjectName"": ""Future"", ""requirementsState"": ""Sleeping"", " _
        & "  ""requirementsTitle"": ""Something zak\u00e1zat"", ""sprint"": "".4-Someday"", " _
        & "  ""state"": ""Sleeping"", ""typeB"": ""Suggestion"" } "
    Set result = ParseJson(str)
    Debug.Print ConvertToJson(result, 2)
End Sub

... using VBA-JSON v2.3.2, runs without problems and prints as expected:

{
  "_Issues": "Issues",
  "description": "description",
  "id": "123",
  "milestonesList": "",
  "modulesList": "Module1",
  "priority": "",
  "projects": [
    {
    }
  ],
  "projectsNamesList": "affas",
  "requirementsEstimate": "",
  "requirementsID": "2977",
  "requirementsLevel1": "Req1",
  "requirementsLevel2": "Req2",
  "requirementsLevel3": "blavla zak\u00E1zat",
  "requirementsProjectName": "Future",
  "requirementsState": "Sleeping",
  "requirementsTitle": "Something zak\u00E1zat",
  "sprint": ".4-Someday",
  "state": "Sleeping",
  "typeB": "Suggestion"
}

Did you remember that you need a Set command to receive the object returned by ParseJson?

If the above solves your problem, please close the issue.

  • 👍1
jpmbiz70 commented
Nick-vanGemeren commented

Using your string (or adding surrounding square brackets to the original) to the test routine I posted earlier gives expected results on my system.

Are you on a Mac or using Dictionary.cls?

'It will fail' is as unhelpful as 'It doesn't work'.
If you parse the string with the VBA Debugger, which code line in which sub/function raises the 424 error?