2

I'm looping through a folder using :

Dim file As Variant
file = Dir(myFolder)

While (file <> "" )
    mySub file       '<= this sub use Dir() hundreds of times  !!!
    file = Dir()
Wend

mySub breaks the Dir loop since it give me the next file of a different folder.

Is there a easy way to work around that limitation ? If not, how would you proceed ?

Current solution

I'm currently using running a first loop storing the filename in an Array, then running a second loop processing mySub from the Array : 

Dim file As Variant
file = Dir(myFolder)

Dim myArray() as String
Redim myArray(0)

While (file <> "" )
    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
Tibo
  • 383
  • 5
  • 27

1 Answers1

1

The function Dir uses a static iterator. Thus if you call it in a sub function, it will initialize the one from the main function.

To use Dir again in a sub function, you need to consume all the results from the main function before calling the sub function, which is your current solution.

However, calling Redim Preserve to append a new item is rather inefficient. Instead define an initial size for the array, and increase the size by 2 when required:

Dim file As String, n As Long, i As Long
ReDim myArray(0 To 25)

file = Dir("c:\")

Do While Len(file)
  If n = UBound(myArray) Then ReDim Preserve myArray(n * 2)
  myArray(n) = file
  n = n + 1
  file = Dir()
Loop

For i = 0 To n - 1
  mySub myArray(i)
Next

Note that you could also use an instance of Scripting.FileSystemObject, but it's less efficient that Dir() and it works on Windows only.

Florent B.
  • 41,537
  • 7
  • 86
  • 101
  • 1
    Thanks. (PS : while you're technically right about using `Redim` at each file being not very efficient, it's not even going to be noticeable here since I'll probably never have to process more than 1000 files, and especially since `mySub` takes many orders of magnitude longer to run than the `Redim Preserve`.) – Tibo Jul 31 '17 at 23:25