Parse JSON in Excel VBA

Advert: Come to the Foo Bar! See a whole working application based on Excel VBA, JSON, JavaScript, Angular with help of a beauty of C# COM Addin. The Foo Bar where the beers are easily replenished with Angular.

So I did quite a lot of work on StackOverflow answering my own questions about JSON parsing. I really ought to deposit some key code here on my blog as well.

Whilst I have praised Tim Hall's excellent JSON parsing code the following leverages Microsoft's own parser. Tim Hall's is better when you want to amend the object, i.e. read-write. If you are happy with read-only then the Microsoft parser could be a better option but there is one caveat in that Microsoft's parser will execute any Javascript embedded in the JSON and this is a security breach. But if you know the provenance of the JSON and know it to be free from Javascript then the following code will be of use to you.

Use ScriptControl.Eval to parse a JSON object in Excel VBA

In following code, I have given the canonical example from http://json.org/example.html. Paste this code into VBA, add Tools->References->Microsoft Script Control 1.0

Function JsonLiteral() As String
    Const sJSON As String = "{" _
        & " ""glossary"": {" _
        & " ""title"": ""example glossary""," _
        & " ""GlossDiv"": {" _
        & " ""title"": ""S""," _
        & " ""GlossList"": {" _
        & " ""GlossEntry"": {" _
        & " ""ID"": ""SGML""," _
        & " ""SortAs"": ""SGML""," _
        & " ""GlossTerm"": ""Standard Generalized Markup Language""," _
        & " ""Acronym"": ""SGML""," _
        & " ""Abbrev"": ""ISO 8879:1986""," _
        & " ""GlossDef"": {" _
        & " ""para"": ""A meta-markup language, used to create markup languages such as DocBook.""," _
        & " ""GlossSeeAlso"": [""GML"", ""XML""]" _
        & " }," _
        & " ""GlossSee"": ""markup""" _
        & " } } } } }"

    JsonLiteral = sJSON

    '* Requires Tools->References to Microsoft Script Control 1.0
    Dim oSC As ScriptControl
    Set oSC = New ScriptControl
    
    oSC.Language = "JScript"
    Dim obj As Object
    Set obj = oSC.Eval("(" + sJSON + ")")
    
    Stop
    'Look at the Locals window to see the structure of the JSON
End Function

You'll see once evaluated then the Locals object allows you to navigate the structure.

Use VBA.CallByName and hasOwnProperty to navigate parse structure

Some more examples show how you can use CallByName to get sub-objects and also elements of an array. Also shown here is hasOwnProperty which can be used for defensive programming.

Private Sub CallByNameAndHasOwnProperty()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"

    Dim sJsonString(0 To 1) As String
    sJsonString(0) = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"
    sJsonString(1) = "[ 1234, 2345, 3456, 4567, 5678, 6789 ]"



    Dim objJSON(0 To 1) As Object
    Set objJSON(0) = oScriptEngine.Eval("(" + sJsonString(0) + ")")
    Set objJSON(1) = oScriptEngine.Eval("(" + sJsonString(1) + ")")

    Debug.Assert objJSON(0).hasOwnProperty("key1")
    Debug.Assert objJSON(0).hasOwnProperty("key2")

    Debug.Assert CallByName(objJSON(1), "length", VbGet) = 6
    Debug.Assert CallByName(objJSON(1), "0", VbGet) = "1234"
End Sub

Caveat- Don't Overuse ScriptControl!

In addition to the warning about buried JavaScript in JSON I have another warning regarding Microsoft ScriptControl. Although it may look like a brilliant keyhole interface into the world of Javascript execution in VBA it is stuck in a time warp. I have established that Script Control is limited to Javascript EcmaScript v.3. Whilst current is version 6. Don't think you can code up some Javascript in say Microsoft Visual Studio Code and then paste into the ScriptControl with AddCode because chances are you will hit an incompatibility. Also with the ScriptControl you cannot debug code.

I experimented with ScriptControl for some time and tried to push its boundaries, initially it was exciting but it can be frustrating. Search this blog for JSON posts for any updates.

UPDATES and FURTHER READING

Update: Safer JSON parsing with Json.Parse (still via ScriptControl)