ReDim Statement

Declares dynamic-array variables, and allocates or reallocates storage space at procedure level.

Syntax

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

Arguments:

• Preserve: Preserves the data in an existing array when you change the size of the last dimension.

• Varname: Name of the variable; follows standard variable naming conventions.

• Subscripts: Dimensions of an array variable.

Remarks

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array.

If you use the Preserve keyword, you can resize only the last array dimension, and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

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

Example 1: Basic ReDim Usage (Without Preserve)

Dim arr()
' Initially, create an array with 3 elements
ReDim arr(2)  ' Array can hold 3 elements (0, 1, 2)
' Assign values to the array
arr(0) = "Apple"
arr(1) = "Banana"
arr(2) = "Cherry"
' Now, resize the array to hold 5 elements
ReDim arr(4)  ' Array can hold 5 elements (0, 1, 2, 3, 4)
'If you try to access indexes 0, 1, 2 there will be no values.
' The previous values are lost because we did not use Preserve
arr(3) = "Date"
arr(4) = "Elderberry"

Example 2: Using ReDim Preserve to Keep Existing Data

Dim arr()
' Initially, create an array with 3 elements
ReDim arr(2)  ' Array can hold 3 elements (0, 1, 2)
' Assign values to the array
arr(0) = "Apple"
arr(1) = "Banana"
arr(2) = "Cherry"
' Now, resize the array to hold 5 elements, keeping the existing data
ReDim Preserve arr(4)  ' Array can hold 5 elements (0, 1, 2, 3, 4)
' The previous values are preserved, so we can add more elements
arr(3) = "Date"
arr(4) = "Elderberry"

Caution: If you make an array smaller than it was originally, data in the eliminated elements is lost.

When variables are initialized, a numeric variable is initialized to 0 and a string variable is initialized to a zero-length string (""). A variable that refers to an object must be assigned an existing object using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing.