title: Code optimisation: Arrays vs collections
body: <p>In terms of memory consumption/ execution time, what's a more expensive way of adding items to a group</p>
  <pre><code>Redim Preserve myArray(1 To Ubound(myArray) + 1)
  myArray(Ubound(myArray)) = myVal
  </code></pre>
  <p>Or</p>
  <pre><code>myCollection.Add myVal
  </code></pre>
  <p>Does the fastest one depend on <code>myVal</code>, or vary with the size of the groups? Is there a faster method still?</p>
  <p>I have the array/collection declared Privately in the declarations portion of a class if that makes a difference, but <strong>I'd like to know what is happening behind the scenes</strong> and which approach is generally faster (not in terms of readability or maintainability, just execution time)</p>
  <h3>Tests</h3>
  <p>OK, having run some tests adding many instances of 1 to groups and collections, my results are:</p>
  <ul>
  <li>collection method 10 x faster than a variant array</li>
  <li>collection method 5 x faster than a long array</li>
  <li>collection method 1.5 x faster than a byte array</li>
  </ul>
  <p>Results were all for approx 5 seconds of looping with this code:</p>
  <pre><code>Sub testtime()
  Dim sttime As Double
  Dim endtime As Double
  Dim i As Long
  Dim u As Long
  i = 0
  ReDim a(1 To 1) 'or Set c = New Collection
  sttime = Timer
  endtime = Timer + 5
  Do Until Timer &gt; endtime
      u = UBound(a) + 1
      ReDim Preserve a(1 To u)
      a(u) = 1
      'or c.Add 1
      i = i + 1
  Loop
  endtime = Timer
  Debug.Print (endtime - sttime) / i; &quot;s&quot;, i; &quot;iterations&quot;, Round(endtime - sttime, 3); &quot;(ish) s&quot;
  End Sub
  </code></pre>
  <p>So it looks like for adding that item, with relatively large groups; adding to a collection is faster, but I'd like to know why?</p>