REDIM

REDIM [Preserve] varname

Initialises and resizes a dynamic array.

varname

The name of the array variable.

REMARKS

* This is used to size or resize a Dynamic Array.
* When an array is re-initialized any values in the array will be lost.
* It is possible to resize an array without losing the existing values by using the Preserve keyword.
* You can change the data type when you resize the array but only if the array has a Variant data type.
* You can only change the data type of a Variant when you are not using the Preserve keyword.
* You can use this to resize the last dimension of multi-dimensional arrays.
* You can use the ERASE to reinitialise the elements of an array.
* For the Microsoft documentation refer to learn.microsoft.com

Dim myArray1() As Integer       'Dynamic array  
ReDim myArray1(2) 
Debug.Print UBound(myArray1)    '= 2  
myArray1(0) = 10 
myArray1(1) = 20 
myArray1(2) = 30 
ReDim Preserve myArray1(4) 
Debug.Print myArray1(3)         '= 0 - initial value  
Debug.Print UBound(myArray1)    '= 4  
myArray1(3) = 40 
myArray1(4) = 50 

Dim myArray2 As Variant         'Variant can contain an array  
ReDim myArray2(2) As Integer 
Debug.Print myArray2(0)         '= 0 - initial value  
myArray2(0) = 10 
myArray2(1) = 20 
myArray2(2) = 30 
Debug.Print myArray2(0)         '= 10  

ReDim myArray3(2) As String 
myArray3(0) = "Pizza" 
myArray3(1) = "Chips" 
myArray3(2) = "Biscuits" 
ReDim Preserve myArray3(50) 
Debug.Print UBound(myArray3)    '= 50  
Debug.Print myArray3(0)         '= Pizza