VBA ReDim Statement

The Excel VBA ReDim statement initializes and resizes a dynamic VBA Array. Be sure to check the difference between Fixed VBA Arrays and Dynamic VBA Arrays.

VBA ReDim Statement Syntax

ReDim [Preserve] varname ( subscripts ) 

Parameters

Preserve
Optional. Keyword to be used if you want to resize your array and keep all items intact.
varname
The name of the array variable.
subscripts
The subscripts of the array defining either it’s upper bounds or lower and upper bounds. See examples below.

Using ReDim with single dimension arrays

Below an example of sizing and resizing a VBA Array:

Dim arr() As Variant 'declaration of variant array
ReDim arr(2) 'Sizing array to upper bound 2. Array size is 3 -> 0 to 2
arr(2) = 10
Debug.Print arr(2) 'Result: 10

ReDim arr(1 to 2) 'Resizing array to size 2 -> 1 to 2. All items will be empty again.
Debug.Print array(2) 'Result: Empty

Using ReDim to resize an array but preserving its contents

Below an example of sizing and resizing a VBA Array, but keeping its contents preserved:

Dim arr() As Variant 'declaration of variant array
ReDim arr(2) 'Sizing array to upper bound 2. Array size is 3 -> 0 to 2
arr(2) = 10
Debug.Print arr(2) 'Result: 10

ReDim Preserve arr(1 to 2) 'Resizing array to size 2 -> 1 to 2. All items will be Preserved as keyword was used.
Debug.Print array(2) 'Result: 10. Hurray!

Using ReDim with multidimensional arrays

Below an example of sizing and resizing a multidimensional array:

Dim arr() As Variant 'declaration of variant array
ReDim arr(2, 2)
arr(1, 1) = 10
    
ReDim Preserve arr(2, 10)
Debug.Print arr(1, 1) 'Result: 10