VB & VBA in a Nutshell: The Language

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, ...