Sub TestStaticArray()
' stores 10 names in the workbook in the array variable MyNames()
Dim MyNames(1 to 10) As String ' declares a static array variable
Dim iCount As Integer
For iCount = 1 To 10
MyNames(iCount) = ThisWorkbook.Names(iCount)
Next iCount
Erase MyNames() ' deletes the varible contents, free some memory
End Sub

Sub TestDynamicArray()
' stores all names in the workbook in the array variable MyNames()
Dim MyNames() As String ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer
Max = ThisWorkbook.Names.Count ' finds the maximum array size
ReDim MyNames(1 to Max) ' declares the array variable with the necessary size
For iCount = 1 To Max
MyNames(iCount) = ThisWorkbook.Names(iCount)
Next iCount
Erase MyNames() ' deletes the varible contents, free some memory
End Sub

Sub GetFileNameList()
' stores all the filenames in the current folder
Dim FolderFiles() As String ' declares a dynamic array variable
Dim tmp As String, fCount As Integer
fCount = 0
tmp = Dir("*.*")
While tmp <> Empty
fCount = fCount + 1
ReDim Preserve FolderFiles(1 to fCount)
' declares the array variable again (size+1)
FolderFiles(fCount) = tmp
tmp = Dir
Wend
MsgBox fCount & " filenames are found in the folder " & CurDir
Erase FolderFiles ' deletes the varible contents, free some memory
End Sub