title: In Excel VBA on Windows, how to loop through a JSON array parsed?
body: <p>answering my own question here.<br />
  I have done some work with JSON in Excel VBA and lots of findings to post which I will do so in Q &amp; A format
  <a href="https://stackoverflow.com/help/self-answer">https://stackoverflow.com/help/self-answer</a> <a href="https://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/">https://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/</a></p>
  <p>So elsewhere on stackoverflow one can see questions about parsing JSON in VBA but they seem to miss a trick or two.</p>
  <p>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.</p>
  <p>Here is a prior question <a href="https://stackoverflow.com/questions/37710995/in-excel-vba-on-windows-how-to-mitigate-issue-of-dot-syntax-traversal-of-parsed">In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?</a> 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.</p>
  <p>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  <a href="https://stackoverflow.com/users/334106/ozmike">https://stackoverflow.com/users/334106/ozmike</a></p>
  <pre><code>'Tools-&gt;References-&gt;
  '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 = &quot;JScript&quot;
      oScriptEngine.AddCode &quot;Object.prototype.myitem=function( i ) { return this[i] } ; &quot;
      
      Dim sJsonString As String
      sJsonString = &quot;[ 1234, 2345 ]&quot;
      
      Dim objJSON As Object
      Set objJSON = oScriptEngine.Eval(&quot;(&quot; + sJsonString + &quot;)&quot;)
      
      Debug.Assert objJSON.myitem(0) = 1234
      Debug.Assert objJSON.myitem(1) = 2345
      
  
  End Sub
  </code></pre>
  <p>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.</p>
  <p>This is Question 2 of series of 5.  Here is the full series</p>
  <p>Q1 <a href="https://stackoverflow.com/questions/37710995/in-excel-vba-on-windows-how-to-mitigate-issue-of-dot-syntax-traversal-of-parsed">In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?</a></p>
  <p>Q2 <a href="https://stackoverflow.com/questions/37711034/in-excel-vba-on-windows-how-to-loop-through-a-json-array-parsed">In Excel VBA on Windows, how to loop through a JSON array parsed?</a></p>
  <p>Q3 <a href="https://stackoverflow.com/questions/37711073/in-excel-vba-on-windows-how-to-get-stringified-json-respresentation-instead-of">In Excel VBA on Windows, how to get stringified JSON respresentation instead of “[object Object]” for parsed JSON variables?</a></p>
  <p>Q4 <a href="https://stackoverflow.com/questions/37711100/in-windows-excel-vba-how-to-get-json-keys-to-pre-empt-run-time-error-438-obj">In Windows Excel VBA,how to get JSON keys to pre-empt “Run-time error '438': Object doesn't support this property or method”?</a></p>
  <p>Q5 <a href="https://stackoverflow.com/questions/37711112/in-excel-vba-on-windows-for-parsed-json-variables-what-is-this-jscripttypeinfo">In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?</a></p>