title: runtime error 7 Out of Memory using vba collection
body: <p>I'm new to vba in excel, infact its the first time for me. Along the way i've managed to work most things out and find answers in the net. However the latest issue im having i cant find an answer, solution to my problem.</p>
  
  <p>this function, downloads a json string, and is usually 5+mb big. and this loop finds the opening and closing {} for each component and adds it to a collection. All is well until I get to around the 1600/35k entry and the runtime error happens.</p>
  
  <p>There is a 2nd error, with a out of string message, but assigning vbNullString to the string stops this from happening</p>
  
  <pre><code>Dim cnt As Long
  Dim json As String, idKey As String
  Dim strStart As Long, strFinish As Long
  
  cnt = 0
  strStart = 1
  strFinish = 0
  On Error GoTo HandleIt
  Do While strFinish &lt;= Len(dataStr) - 5
      json = vbNullString
      strStart = InStr(strStart, dataStr, "{""Id"":", vbBinaryCompare)
      strFinish = InStr(strStart, dataStr, "}", vbBinaryCompare) - 1
      json = Mid$(dataStr, strStart, strFinish)
  
      ' Get the itemID for the key
      'strStart = InStr(6, json, ",", vbBinaryCompare)
      'idKey = Mid$(json, 7, strStart - 7)
  
      TSMDataBulk.add json
      strStart = strFinish
      cnt = cnt + 1
  Loop
  </code></pre>
  
  <p>tltd: Everything runs fine until TSMDataBulk.add causes a out of memory error. Can anyone help?</p>