在Windows中的Excel VBA中，如何减轻由IDE的大写行为破坏的解析JSON的点语法遍历问题？ [英] In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?

问题描述

在Windows中的Excel VBA中，如何缓解由IDE的大写行为破坏的解析JSON的点语法遍历问题？

在这里回答我自己的问题。我已经在Excel VBA中完成了一些JSON工作，并且在Q&格式

在取消注释第2行之后，然后Line1用符号'key1'重写，现在给出了大写K。

现在经过一些实验，似乎重写效应仅限于项目范围，所以其他项目不受影响。
这意味着可以通过总是使用单独的项目来隔离问题，但是如何将对象编组到消费项目，然后
就可以访问它，肯定会再次遇到同样的问题。所以，项目隔离不是一个真正的解决方案。

一种方法是确保符号不会冲突并给予JSON键一些前缀，所以这里是一个例子

 '工具 - >参考 - > 
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C：\Windows\SysWOW64\msscript.ocx 
 
 Private Sub TestJSONParsingWithDotSyntaxAndKeyPrefixesToAvoidNameClash（）
 
 Dim oScriptEngine As ScriptControl 
设置oScriptEngine = New ScriptControl 
 oScriptEngine.Language =JScript
 
 Dim sJsonString As String 
 sJsonString ={'kKey1'：'value1'，'kKey2'：{'kKey3'：'value3'}} 
 
 
 Dim objJSON As Object 
设置objJSON = oScriptEngine.Eval（（+ sJsonString +））
 1：Debug.Assert objJSON.kKey1 =value1
 Debug.Assert objJSON.kKey2.kKey3 =value3
 
'****安全到符号作为符号不要立即关闭
 2：'Dim Key1 As Long 
 End Sub 
  

不知怎的，我不喜欢这个，更改JSON只是为了使VBA可以访问它。此外，可能没有控制源JSON。

还有其他方法，例如在脚本引擎中添加一些javscript，以允许Javascript进行访问。使用Codo https://stackoverflow.com/users/413337/codo 的帽子提示是一个基于示例的在这种方法...

 '工具 - >参考 - > 
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C：\Windows\SysWOW64\msscript.ocx 
 
私有Sub TestJSONParsingWithMiniScript（）
'帽子提示到Codo https://stackoverflow.com/users/413337/codo 
'根据https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop#7300963 
 Dim oScriptEngine As ScriptControl 
设置oScriptEngine =新ScriptControl 
 oScriptEngine.Language =JScript
 oScriptEngine.AddCodefunction 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
 
结束Sub 
  

我喜欢将脚本添加到脚本引擎的技术，但是我发现了一种更本地的技术那就是使用VBA.CallByName
，这是我的答案中显示的技巧。

我没有选择我自己的答案作为确定因为我认为
（1）似乎社区可以继续改进我们对Excel VBA中JSON解析的知识，如果有人发现如何阻止大写，那么这是一个明显的赢家，那么
（2）。

这是系列的问题1。这是完整的系列

Q1 在Windows中的Excel VBA中，如何减轻点的问题由IDE的大写行为破坏的解析的JSON的语法遍历？

Q2 在Windows中的Excel VBA中，如何解析JSON数组？

Q3 在Windows中的Excel VBA中，如何获取JSON格式的代码替代[object Object]用于解析的JSON变量？

Q4

Q5 在Windows中的Excel VBA中，对于解析的JSON变量，这个JScriptTypeInfo是什么？

解决方案

最后我用th e以下使用本机VBA.CallByName

 'Tools-> References-> 
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C：\Windows\SysWOW64\msscript.ocx 
 
 Private Sub TestJSONParsingWithCallByName（）
 
 Dim oScriptEngine As ScriptControl 
设置oScriptEngine = New ScriptControl 
 oScriptEngine.Language =JScript
 
 Dim sJsonString As String 
 sJsonString ={'key1'：'value1'，'key2'：{'key3'：'value3'}} 
 
 
 Dim objJSON As Object 
设置objJSON = oScriptEngine.Eval（（+ sJsonString +））
 Debug.Assert VBA.CallByName（objJSON ，key1，VbGet）=value1
 Debug.Assert VBA.CallByName（VBA.CallByName（objJSON，key2，VbGet），key3，VbGet）=value3
 
 End Sub 
  

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 http://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://stackoverflow.com/questions/5773683/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

Q1 In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?

Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?

Q3 In Excel VBA on Windows, how to get stringified JSON respresentation instead of "[object Object]" for parsed JSON variables?

Q4 In Windows Excel VBA,how to get JSON keys to pre-empt "Run-time error '438': Object doesn't support this property or method"?

Q5 In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?

解决方案

In the end I progressed with the following which uses the native VBA.CallByName

'Tools->References->
'Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx

Private Sub TestJSONParsingWithCallByName()

    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 + ")")
    Debug.Assert VBA.CallByName(objJSON, "key1", VbGet) = "value1"
    Debug.Assert VBA.CallByName(VBA.CallByName(objJSON, "key2", VbGet), "key3", VbGet) = "value3"

End Sub

这篇关于在Windows中的Excel VBA中，如何减轻由IDE的大写行为破坏的解析JSON的点语法遍历问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！