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

Question

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.

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.

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://mcmap.net/q/56983/-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

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.

This is Question 2 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?

Marseillaise

Answer 1

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

Marseillaise