Contents
In VBA (Visual Basic for Applications), the ReDim statement is used to resize an array dynamically. It’s particularly useful when you don’t know in advance the number of elements that your array will need to store. The ReDim statement can only be used on arrays that are declared as dynamic arrays (i.e., arrays that are not initially allocated with specific dimensions).
Basic Usage of ReDim
Declare a Dynamic Array
First, declare an array without specifying its size.
Dim myArray() As IntegerResize the Array Using ReDim
You can then resize the array at runtime using ReDim.
ReDim myArray(5) ' The array now has 6 elements (0 to 5)Preserving Data with ReDim Preserve
When you use ReDim on an existing array, it normally erases any existing data in the array. If you want to resize the array while keeping its existing data, use ReDim Preserve.
Preserve Data When Resizing
Dim myArray() As Integer
ReDim myArray(5)
' Populate the array
myArray(0) = 10
myArray(1) = 20
' ...
' Resize while keeping existing data
ReDim Preserve myArray(10)Points to Note
Only the Last Dimension Can Change
When using ReDim Preserve, you can only change the size of the last dimension of the array. If you have a multi-dimensional array, you cannot change the size of the first dimension (or any dimension other than the last) while preserving data.
Dim myArray(1, 1) As Integer
ReDim Preserve myArray(1, 3) ' Valid
ReDim Preserve myArray(3, 3) ' Invalid, will cause an error- Performance Considerations: Frequent use of ReDim Preserve can be inefficient, particularly with large arrays, as it involves copying the existing elements to a new array location. Use it judiciously, especially in performance-critical code.
- Initializing Arrays: After resizing an array, any new elements added to the array will be initialized to the default value for the data type of the array (e.g., 0 for integers, an empty string for strings).
- Scope: The ReDim statement can only be used on variables that are explicitly declared as arrays within the procedure or module where it’s used. It doesn’t work on array variables passed as parameters or on arrays declared at the module level unless the procedure is in the same module.
The ReDim statement provides flexibility in managing the size of arrays dynamically, which is particularly useful in scenarios where the data set size varies or is not known until runtime.
Unlock Your Potential
Excel
Access
Power BI
