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

Question

answering my own question here. 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.

Here's the previous question In Excel VBA on Windows, how can I mitigate the JSON parsing issue broken by the IDE capitalization behavior? This question is built on. This shows how the use of VBA.CallByName is more reliable than using the dotted syntax to traverse the parsed JSON object.

This question asks how to traverse the parsed JSON array. Firstly, this is a mini hat tip approach 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 

But believe it or not. VBA.CallByName can be used initially not only to obtain the length of the array, but also for access elements. See the answer.

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

Answer 1

So VBA.CallByName also accesses the elements in the array, and also finds the length of the array

 '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