Microsoft Office Tutorials and References
In Depth Information
Declaring Dynamic Arrays with ReDim and Preserve
The ultimate objective of that declaration was to build an array
containing the seven days of the week, and to transfer that list into
range A1:A7, as depicted in Figure 10-3.
The macro to do that could look like the following one named
ArrayWeekdays . Characteristics of a fixed array include a set of
elements that remain constant, such as days of the week, where there will
always be seven and their names will never change. The WEEKDAY
function returns an integer from 1 to 7 that represents a day of the week.
For example, 1 represents Sunday, 2 represents Monday, and so on. If
you enter the function =WEEKDAY(5) in a cell, and custom format the
cell as DDDD , the cell will display Thursday.
The comments in the code explain what is happening, and why:
‘Declare the array variable for seven elements (from 0 to 6).
Dim myDays(6) As String
‘Declare an Integer type variable to handle the seven indexed elements.
Dim intDay As Integer
‘Start to loop through each array element starting at the default 0 lower boundary.
For intDay = 0 To 6
‘For each array element, define the myDays String variable
‘with its corresponding day of the week.
‘There is no such thing as “Weekday 0”, because Excel’s Weekday function
‘is numbered from 1 to 7,so the “+ 1” notation adds 1 to the intDays Integer
‘variable which started at the lower bound of 0.
myDays(intDay) = Format(Weekday(intDay + 1), “DDDD”)
‘Cells in range A1:A7 are populated in turn with the weekday.
Range(“A” & intDay + 1).Value = myDays(intDay)
‘The loop is continued through to conclusion.
‘End of the macro.
dEclAring dynAMic ArrAys WiTH rEdiM And prEsErVE
Unlike an array with a known fixed set of elements, some arrays are built programmatically during the
macro. These arrays are called “dynamic.” Earlier you read about populating an array with the count
of Excel workbook files that exist in a folder. In that case you’d have a “dynamic” array because the
file count is subject to change; you would not know ahead of time what the array’s size will be. With a
dynamic array, you can create an array that is as large or as small as you need to make it.