In Excel VBA on Windows, how to mitigate problem of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?
Hi, answering my own question here. I have done some work with JSON in Excel VBA and lots of findings to post which I will do so in Q & A format https://stackoverflow.com/help/self-answer https://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/
So elsewhere on stackoverflow one can see questions about parsing JSON in VBA but they seem to miss a trick or two.
To begin with, I resile from using custom JSON parsing libraries and instead use the ScriptControl's Eval method as the basis of all my JSON code. And also we express a preference from native Microsoft solutions.
In this first question I will show that in Excel VBA one can in fact use the dot syntax to traverse a JSON structure but that unfortunately this is broken by the VBA IDE's "helpfulness" with regards to capitalisation.
Below is some sample code where on the line labelled 1: we can see the text "objJSON.key1" and this code works until one uncomments
'Tools->References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
Option Explicit
Option Private Module
Private Sub TestJSONParsingWithVBACallByName()
Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"
Dim sJsonString As String
sJsonString = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }"
Dim objJSON As Object
Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
1: Debug.Assert objJSON.key1 = "value1"
Debug.Assert objJSON.key2.key3 = "value3"
'**** BUT IF UNCOMMENT NEXT LINE THIS AFFECTS ALL CAPITALISATION INSTANCES OF KEY1 INCLUDING LINE 1 WHICH THENCE BREAKS
2: 'Dim Key1 as Long
End Sub
Here is a screenshot before
And after uncommenting Line 2 then Line1 is rewritten with the symbol 'key1' now given a capital 'K'.
Now after some experimenting it seems that rewriting effect is limited to project scope, so other projects are not affected. This means one could isolate the issue by always using a separate project but then how would one marshall the object to the consuming project and thence access it, surely one hits the same issue again. So, project isolation is not really a solution.
One way is to ensure that the symbols do not clash and give the JSON keys some kind of prefix so here is an example
'Tools->References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
Private Sub TestJSONParsingWithDotSyntaxAndKeyPrefixesToAvoidNameClash()
Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"
Dim sJsonString As String
sJsonString = "{'kKey1': 'value1' ,'kKey2': { 'kKey3': 'value3' } }"
Dim objJSON As Object
Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
1: Debug.Assert objJSON.kKey1 = "value1"
Debug.Assert objJSON.kKey2.kKey3 = "value3"
'**** SAFE TO UNCOMMENT AS SYMBOLS DO NOT CLASH NOW
2: 'Dim Key1 As Long
End Sub
Somehow I do not like this, seems odd to have to change the JSON just so that VBA can access it. Besides, one may not have control of the source JSON.
There are other methods such as adding some javscript to the script engine to allow Javascript to do the accessing. With a hat-tip to user Codo https://stackoverflow.com/users/413337/codo here is a sample based on this approach...
'Tools->References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
Private Sub TestJSONParsingWithMiniScript()
'hat tip to Codo https://stackoverflow.com/users/413337/codo
'Based on https://mcmap.net/q/56983/-excel-vba-parsed-json-object-loop#7300963
Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"
oScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
Dim sJsonString As String
sJsonString = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }"
Dim objJSON As Object
Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
Debug.Assert oScriptEngine.Run("getProperty", objJSON, "key1") = "value1"
Debug.Assert oScriptEngine.Run("getProperty", oScriptEngine.Run("getProperty", objJSON, "key2"), "key3") = "value3"
End Sub
I like the technique of adding a script to the script engine however, I discovered a more native technique and that is to use VBA.CallByName and this is the technique shown in my answer.
I have not selected my own answer as definitive because I think (1) it seems the community can continue to improve our knowledge on JSON parsing in Excel VBA and (2) if someone discovers how to stop the capitalisation then that is an obvious winner.
This is Question 1 of series of 5. Here is the full series
Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?
Q5 In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?