I remember seeing Myrna Larson's code a long time ago.

She would redim in groups of 100 keeping track of how many were used:

I found one sample where she used 20, but the theory would still hold:

Function GetFileList(FileSpec As String, FileNames() As String) As Integer
Dim Max As Integer
Dim NumFiles As Integer
Dim AFile As String

Max = 0
NumFiles = 0

AFile = Dir$(FileSpec)
Do While Len(AFile)
NumFiles = NumFiles + 1
If NumFiles Max Then
Max = Max + 20
ReDim Preserve FileNames(1 To Max) As String
End If
FileNames(NumFiles) = AFile
AFile = Dir$
Loop
GetFileList = NumFiles
If NumFiles Then ReDim Preserve FileNames(1 To NumFiles)

End Function 'GetFileList

From:
http://groups.google.co.uk/group/mic...7ff238 229169

or
http://tinyurl.com/hkowm



Bob Phillips wrote:

Yeah, makes sense, but Sod's law will say that one time you don't dim it big
enough.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Alan Beban" <unavailable wrote in message
...
Bob Phillips wrote:
Where is the Redim statement Arne? I would have expected to see it
within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

I think it's probably much more efficient to ReDim Preserve once at the
end, a la

Dim ary()
reDim ary(n)
Do Until . . .
ary(r) = some_value
r=r+1
Loop
ReDim Preserve ary(r-1)

where n is a number big enough to accommodate all possible iterations,
rather than ReDim Preserve in each iteration of the loop.

Alan Beban

--

Dave Peterson