title: VBA - Looping with Dir() when Dir() is called in a sub
body: <p>I'm looping through a folder using :</p>
  
  <pre><code>Dim file As Variant
  file = Dir(myFolder)
  
  While (file &lt;&gt; "" )
      mySub file       '&lt;= this sub use Dir() hundreds of times  !!!
      file = Dir()
  Wend
  </code></pre>
  
  <p><code>mySub</code> breaks the <code>Dir loop</code> since it give me the next file of a different folder.</p>
  
  <p><strong>Is there a easy way to work around that limitation ?</strong> If not, how would you proceed ?</p>
  
  <h2>Current solution</h2>
  
  <p>I'm currently using running a first loop storing the filename in an <code>Array</code>, then running a second loop processing mySub from the <code>Array</code> : </p>
  
  <pre><code>Dim file As Variant
  file = Dir(myFolder)
  
  Dim myArray() as String
  Redim myArray(0)
  
  While (file &lt;&gt; "" )
      Redim Preserve myArray(Ubound(myArray) + 1)
      myArray(Ubound(myArray)) = file
      file = Dir()
  Wend
  
  Dim n as Integer
  For n = 1 to Ubound(myArray)
      mySub myArray(n)
  Next
  </code></pre>