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