在Windows中的Excel VBA中，如何循环解析JSON数组？ [英] In Excel VBA on Windows, how to loop through a JSON array parsed?

问题描述

在这里回答我自己的问题。

我已经在Excel VBA中完成了一些JSON工作，还有很多结果可以在Q&格式
https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-要求和答案 - 你自己的问题/

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/

所以在stackoverflow的其他地方，可以看到有关在VBA中解析JSON的问题，但他们似乎错过一两招。

So elsewhere on stackoverflow one can see questions about parsing JSON in VBA but they seem to miss a trick or two.

首先，我使用自定义JSON解析库，而不是使用ScriptControl的Eval方法作为所有JSON代码的基础。
我们还从本地Microsoft解决方案中表达了偏好。

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.

这是一个先前的问题在Windows中的Excel VBA中，如何减轻分析JSON的点语法遍历问题由IDE的大小写行为破坏，这个问题构建在这个问题上。它显示了如何使用VBA.CallByName比使用点语法来遍历解析的JSON对象更强壮
。

Here is a prior question In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour? upon which this question builds. It shows how using VBA.CallByName is more robust than using the dot syntax to traverse a parsed JSON object.

在这个问题中，被问及如何遍历一个解析的JSON数组。首先，这是一个miniscript的方法
与帽子提示ozmike https://stackoverflow.com/users/334106/ozmike

In this question it is asked how to traverse a parsed JSON array. Firstly here is a miniscript approach with a hat tip to ozmike https://stackoverflow.com/users/334106/ozmike

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

Private Sub TestJSONParsingArrayWithMiniScript()
    'Hat tip to ozmike  https://stackoverflow.com/users/334106/ozmike
    'Based on https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop#19359035
    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"
    oScriptEngine.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; "

    Dim sJsonString As String
    sJsonString = "[ 1234, 2345 ]"

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

    Debug.Assert objJSON.myitem(0) = 1234
    Debug.Assert objJSON.myitem(1) = 2345


End Sub

但是相信它不是VBA.CallByName可以使用本来不只是获得一个数组的长度，而且访问元素。

But believe it or not VBA.CallByName can be used natively not just to get an array's length but also access elements. See answer.

这是系列5的问题2。这是完整的系列

This is Question 2 of series of 5. Here is the full series

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

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

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

Q3

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

Q4 在Windows Excel VBA中，如何获取JSON键以预先运行运行时错误438'：Object不支持此属性或方法？

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

推荐答案

所以VBA.CallByName还访问数组中的元素以及查找数组的长度

So VBA.CallByName also accesses elements in an array as well as find the array's length

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

Private Sub TestJSONParsingArrayWithCallByName()

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

    Dim sJsonString As String
    sJsonString = "[ 1234, 2345 ]"

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

    '* Using VBA.CallByName we get the length of the array

    Dim lLength As Long
    lLength = VBA.CallByName(objJSON, "length", VbGet)

    Debug.Assert lLength = 2

    '* Believe or not one uses "0","1",.... with callbyname to get an element
    Debug.Assert VBA.CallByName(objJSON, "0", VbGet) = 1234
    Debug.Assert VBA.CallByName(objJSON, "1", VbGet) = 2345


End Sub

这篇关于在Windows中的Excel VBA中，如何循环解析JSON数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！