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