| ReDim Statement |
Named Arguments
No
Syntax
ReDim [Preserve] varname(subscripts) [As type] _
[, varname(subscripts) [As type]] . . .Preserve
Use: Optional
Type: Keyword
Preserves the data within an array when changing the only or last dimension.
varname
Use: Required
Data Type: Any
Name of the variable.
subscripts
Use: Required
Number of elements and dimensions of the array, using the syntax:
[lower To] upper [,[lower To] upper] . . .
type
Use: Optional
Data type of the array.
Description
Used within a procedure to resize and reallocate storage space for a dynamic array.
Rules at a Glance
A dynamic array is created using a Private, Public, or Dim statement with empty parentheses. Only dynamic arrays created in this manner can be resized using the ReDim statement. There is no limit to the number of times you can redimension a dynamic array.
Use of the Preserve keyword allows you to retain the current values within the array, but it also places several limitations on how the Redim statement can be used:
The data subtype of elements of an array held within a variant can't be changed.
Only the last dimension of an array can be resized.
The number of dimensions can't be changed.
Only the upper bound of the array can be changed.
If you reduce either the number of elements of the array or the number of dimensions in the array, data in the removed elements is permanently lost, irrespective of the use of the Preserve keyword.
If the lower argument isn't used within the subscripts syntax, the lower bound of the dimension is determined by the Option Base statement. If Option Base isn't used, the lower bound defaults to zero.
type may be Byte, Boolean, Currency, Date, Double, Integer, Long, Object, Single, String, Variant, a user-defined type, or an object type.
When the array is held within a variant, type refers to the underlying data subtype of the elements.
The following table shows the values held by each data type when an array is initialized.
Data Type Initial Value Numeric 0 Variable-length string Zero-length string ("") Fixed-length string Filled with zeros Variant Empty Object Nothing Date Saturday 30 December 1899 12:00:00
Programming Tips and Gotchas
Microsoft's documentation for ReDim states that if the array has been passed by reference to a procedure, you can't redimension it within the procedure and return the modified array to the calling procedure. This doesn't appear to be the case, as the following example shows:
Private Sub Command1_Click() Dim strArray() As String Dim strElement As String Dim intCtr As Integer ReDim strArray(9) For intCtr = 0 To UBound(strArray) strArray(intCtr) = "Original element" Next Call ExpandArray(strArray) For intCtr = 0 To UBound(strArray) Debug.Print strArray(intCtr) Next End Sub Private Sub ExpandArray(ByRef arrDynamic() As String) Dim intBound As Integer, intCtr As Integer intBound = UBound(arrDynamic) ReDim Preserve arrDynamic(UBound(arrDynamic) * 2) For intCtr = intBound + 1 To UBound(arrDynamic) arrDynamic(intCtr) = "New element" Next End Sub
When you run this example, both the original elements and new elements are printed to the immediate window, proving that in fact the array was successfully expanded in the ExpandArray procedure.
It's possible to create a new dynamic array within a procedure using the ReDim statement if the array to which it refers doesn't already exist at either module or level. Typically, this results from an error of omission; the programmer forgets to explicitly define the array using Dim, Public, or Private. Since this method of creating an array can cause conflicts if a variable or array of the same name is subsequently defined explicitly, ReDim should be used only to redimension an existing array, not to define a new one.
An array contained within a variant can only be resized if the variable has been explicitly declared as a variant.