Static array variables
Insted of using several unique variables to store information, you can use an array variable.
When you know how many elements you need to store in the array, you can use a static array variable like this :
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
Dynamic array variables
Dynamic array variables are useful when you in advance don't know how many elements that you need to store information about.
You declare dynamic array variables just like a static rray variable, except that you don't give any information about the
array size :
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
If you know that you will need an array variable with 1000 items, use a static variable.
The downside is that you will use memory for a 1000 items every time, also in the cases that
you only stores information about 10 items. If you use a dynamic array variable you will use the memory more efficient..
Sometimes it's not possible to calculate how large tha array variable will need to be.
In these cases the size of the array variable need to be increased as necessary. When
you use a ReDim-statement to change the array variable size, the variable contents is also erased.
to avoid deleting the variable contents when you redim the array variable you will nedd to use the ReDim Preserve-statement :
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
