ReDim

Used at the procedure level to reallocate storage space for dynamic array variables.

Syntax:

ReDim [ Preserve ] varname ( subscripts ) [ As type ] [ , varname ( subscripts ) [ As type ] ] . . .

Preserve: optional Keyword used to preserve the data in an existing array when the size of the last dimension changes.
varname: Name of the variable; follows standard variable naming conventions.
subscripts: Dimensions of an array variable; up to 60 multiple dimensions may be declared. The subscripts argument uses the following syntax: [ lower To ] upper [ , [ lower To ] upper ] . . .. When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.
type: optional Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal, Date, String (for variable-length strings), String length (for fixed-length strings), Object, Variant, a user-defined type, or an object type.
Use a separate As type clause for each variable being defined. For a Variant containing an array, type describes the type of each element of the array, but doesn’t change the Variant to some other type.

The ReDim statement is used to size or resize a dynamic array that has already been formally declared by using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).

Use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, an array declared with one data type cannot later be changed to another data type by ReDim, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed by using an As type clause, unless the Preserve keyword is used, in which case no changes of data type are permitted.

With the Preserve keyword, only the last array dimension can be resized, and the number of dimensions cannot change. For example, when the array has only one dimension, that dimension can be resized because it is the last and only dimension. However, when the array has two or more dimensions, only the size of the last dimension can change while still preserving the contents of the array.

The following example shows how to increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(10, 10, 10)
. . .
ReDim Preserve X(10, 10, 15)

Similarly, with Preserve, the size of the array can only change by changing the upper bound; changing the lower bound causes an error.

When an array becomes smaller than it was, data in the eliminated elements will be lost.

When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (“”), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable.

A variable that refers to an object must be assigned an existing object by using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing, which indicates that it doesn’t refer to any particular instance of an object.

The ReDim statement acts as a declarative statement if the variable it declares doesn’t exist at the module level or procedure level. If another variable with the same name is created later, even in a wider scope, ReDim will refer to the later variable and won’t necessarily cause a compilation error, even if Option Explicit is in effect. To avoid such conflicts, ReDim should not be used as a declarative statement, but simply for redimensioning arrays.

Note

To resize an array contained in a Variant, the Variant variable must be explicitly declared before attempting to resize its array.

Example

This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1.

Dim MyArray() As Integer ' Declare dynamic array.
ReDim MyArray(5) ' Allocate 5 elements.
For I = 1 To 5 ' Loop 5 times.
    MyArray(I) = I ' Initialize array.
Next I

The next statement resizes the array and erases the elements.

ReDim MyArray(10) ' Resize to 10 elements.
For I = 1 To 10 ' Loop 10 times.
    MyArray(I) = I ' Initialize array.
Next I

The following statement resizes the array but does not erase elements.

ReDim Preserve MyArray(15) ' Resize to 15 elements.

ReDim

Example

See Also