Dynamic Arrays

You can't always know how big of an array you will need. You could create an array based on how big it could ever need to be, but that's not only a waste of memory. What if it turns out it needs to be bigger? In this case, you can use a dynamic array.

A dynamic array is an array that does not have a set size. Declare the array, but leave the parentheses empty: Dim myArray ()

Later, as the program needs to use the array, use Redim to set the size of the array:

Sub MySheets()

Dim myArray() As String

Dim myCount As Integer, NumShts As Integer

NumShts = ActiveWorkbook.Worksheets.Count

' Size the array

ReDim myArray(1 To NumShts)

For myCount = 1 To NumShts myArray(myCount) = ActiveWorkbook.Sheets(myCount).Name Next myCount

End Sub

Using Redim re-initializes the array, so if you were to use it many times, such as in a loop, you would lose all the data it holds. Use Preserve to prevent that from happening. The following example looks for all the Excel files in a directory and puts the results in an array. Because we don't know how many files there will be until we look at them, we can't size the array before the program is run:

Sub XLFiles() Dim FName As String Dim arNames() As String Dim myCount As Integer

FName = Dir("C:\Contracting Files\Excel VBA 2003 by Jelen & Syrstad\*.xls") Do Until FName = 1111

myCount = myCount + 1 ReDim Preserve arNames(1 To myCount) arNames(myCount) = FName FName = Dir

Loop End Sub

0 0

Post a comment