Microsoft Office Tutorials and References
In Depth Information
Declaring Dynamic Arrays with ReDim and Preserve
To attack the problem of an unknown count of elements, you can change the size of an array on the
fly with a pair of keywords called ReDim and Preserve . The ReDim statement is short for redimension,
a fancy term for resizing the array. When ReDim is used by itself to place an element in the array, it
releases whatever data was in the array at the time, and simply adds the element to a new empty array.
The Preserve statement is necessary to keep (preserve) the data that was in the array, and have the
incoming element be added to the existing data. In VBA terms, ReDim Preserve raises the array’s
upper boundary, while keeping the array elements you’ve accumulated.
The following macro named SelectedWorksheets demonstrates ReDim Preserve in action. The
purpose of the array in this example is to collect the names of all worksheets that are concurrently
selected, such as when you press the Ctrl key and select a few worksheet tabs.
The comments in the code explain what each line of code is doing, so you can get a feel for how to
populate a dynamic array and display its elements (the worksheet names) in a Message Box.
‘Declare the array variable for an unknown count of elements.
Dim WhatSelected() As Variant
‘Declare a variable for the Worksheet data type.
Dim wks As Worksheet
‘Declare an Integer variable to handle the unknown count of selected worksheets.
Dim intSheet As Integer
‘Start to loop through each selected worksheet.
For Each wks In ActiveWindow.SelectedSheets
‘An index array element is assigned to each selected worksheet.
intSheet = intSheet + 1
‘This macro is building an array as each selected worksheet is encountered.
‘The Redim statement adds the newest selected worksheet to the growing array.
‘The Preserve statement keeps (preserves) the existing array data,
‘allowing the array to be resized with the addition of the next element.
ReDim Preserve WhatSelected(intSheet)
‘The corresponding worksheet’s tab name is identified with each selected sheet,
‘and placed in the “WhatSelected” array for later retrieval.
WhatSelected(intSheet) = wks.Name
‘The loop is continued to completion.
‘Looping through each element in the “WhatSelected” array that was just built,
‘a message box displays the name of each corresponding selected worksheet.
For intSheet = 1 To UBound(WhatSelected)
‘End of the macro.