In Excel VBA on Windows, how to mitigate the problem of parsing JSON parsing broken by IDE capitalization behavior?
Hi, answering my own question. I did some work with JSON in Excel VBA and a lot of publishing results, which I will do in Q and A format https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/ its-ok-to-ask-and-answer-your-own-questions /
So, somewhere in stackoverflow, you can see questions about parsing JSON in VBA, but they seem to have missed a trick or two.
First, I refuse to use custom JSON parsing libraries and instead use the ScriptControl Eval method as the basis for all JSON code. And also we express preference from our own Microsoft solutions.
In this first question, I will show that in Excel VBA, you can actually use point syntax to intersect the JSON structure, but unfortunately this is a broken VBA IDE “utility” in relation to capitalization.
Below is an example of code where in the line with the inscription 1: we can see the text "objJSON.key1", and this code works until it can be executed
'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 the termination of line 2, then Line1 is rewritten with the symbol "key1", now taking into account the capital "K".
Now, after some experimentation, it seems that the rewriting effect is limited to the project area, so other projects are not affected. This means that you can isolate the problem, always using a separate project, but then, like one marshal of an object for a consumption project and then access to it, of course, repeats the same problem once. Thus, isolating a project is not really a solution.
One way is to ensure that characters do not collide and provide some kind of prefix to the JSON keys, 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 it, it seems strange to change JSON so that VBA can access it. In addition, you cannot control the source JSON.
Other methods exist, such as adding javscript to the script engine to allow Javascript to access. With a tip for the Codo user https://stackoverflow.com/users/413337/codo here is an example 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://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop
I like the method of adding a script to the script engine, however I discovered a more native technique and I have to use VBA.CallByName and this is the method shown in my answer.
I did not choose my own answer as final, because I think (1) it seems that the community can continue to improve our knowledge of JSON parsing in Excel VBA and (2) if someone finds out how to stop capitalization, then this is an obvious winner.
This is question 1 from series 5. Here is the complete series.
Q1 In Excel VBA on Windows, how can I mitigate the JSON parsing issue broken by the IDE capitalization behavior?
Q2 In Excel VBA on Windows, how to handle a JSON array?
Q3 In Excel VBA on Windows, how to get a compressed JSON representation instead of "[object Object]" for parsed JSON variables?
Q4 In Windows Excel VBA, how do I get JSON keys to pre-check a "Runtime Error" 438: Does the object not support this property or method ??
Q5 In Excel VBA for Windows, for parsed JSON variables, what is JScriptTypeInfo anyway?