The ReDim statement in VBA stands for Re-Dimension and is used to dynamically change the size of an array during runtime. This statement is a powerful tool that allows for more flexibility in programming compared to static arrays, as the size and shape of the array can be modified according to the needs of the program. In this blog post, we will dive into the purpose, syntax, and examples of using the ReDim statement in VBA.
VBA ReDim Statement
Purpose of the ReDim Statement
Arrays are a set of variables that can hold multiple values of the same data type. In VBA, arrays can be either static or dynamic. Static arrays have a fixed size and cannot be changed once they are declared, while dynamic arrays can be resized during runtime. The ReDim statement is used to resize or re-dimension dynamic arrays, allowing for more efficient memory usage and better performance. It is a key tool in handling large sets of data and is commonly used in loops to store data or perform calculations.
Syntax of the ReDim Statement
The general syntax of the ReDim statement is as follows:
ReDim [Preserve] arrayname(subscripts)
The ‘Preserve’ keyword is optional, and when included, it retains the values of the existing array when resizing. The ‘arrayname’ is the name of the array you want to resize, and the ‘subscripts’ determines the new size and dimension of the array.
VBA ReDim Statement Examples
Now let’s take a look at some common examples of using the ReDim statement in VBA.
Example 1: Resizing an Array
In this example, we will resize an array of integers from 10 elements to 20 elements using the ReDim statement.
Sub resizeArray()
Dim arr() As Integer
ReDim arr(1 To 10)
'perform some operations
ReDim arr(1 To 20)
End Sub
In the above code, the initial size of the array is set to 10, but as the program progresses, the need for more elements arises. Therefore, the ReDim statement is used to resize the array to 20 elements, allowing the program to continue running without any errors.
Example 2: Resizing an Array with Preserve
In this example, we will use the ‘Preserve’ keyword to retain the values of an array while resizing it.
Sub resizeArray_Preserve()
Dim arr() As Integer
ReDim arr(1 To 10)
'populate the array with values
For i = 1 To 10
arr(i) = i * 10
Next i
ReDim Preserve arr(1 To 20)
'values from the existing array are kept
For i = 11 To 20
arr(i) = i * 10
Next i
End Sub
In the above code, the ‘Preserve’ keyword is used to keep the values of the existing array while resizing. This allows for efficient memory usage and prevents the loss of any data.
Example 3: Resizing a Multi-Dimensional Array
The ReDim statement can also be used to resize multi-dimensional arrays.
Sub resizeMultiDimArray()
Dim arr(1 To 5, 1 To 5) As Integer
ReDim arr(1 To 10, 1 To 10)
End Sub
In this example, the multi-dimensional array is resized from a 5×5 to a 10×10 array using the ReDim statement.
Example 4: Resizing an Array Based on User Input
The ReDim statement can be particularly useful when dealing with user input, as the size of the array may vary.
Sub resizeArray_UserInput()
Dim size As Integer
Dim arr() As String
size = InputBox("Enter the size of the array:")
ReDim arr(1 To size)
'perform operations based on user input
End Sub
In this example, the user is prompted to enter the size of the array, and the ReDim statement is used to resize the array accordingly.
Example 5: Using the ‘Preserve’ Keyword with Multiple Redimensions
The ‘Preserve’ keyword can be used with multiple redimensions, allowing for the preservation of values even when the size and shape of the array change.
Sub multiRedimPreserve()
Dim arr() As Integer
ReDim arr(1 To 2, 1 To 2)
'populate the array with values
For i = 1 To 2
For j = 1 To 2
arr(i, j) = i + j
Next j
Next i
ReDim Preserve arr(1 To 4, 1 To 3)
'values from the existing array are kept
For i = 3 To 4
For j = 2 To 3
arr(i, j) = i + j
Next j
Next i
End Sub
In this example, the ‘Preserve’ keyword is used to retain the values of the original 2×2 array while resizing it to a 4×3 array.
Important Notes & Remarks
- The ReDim statement can only be used for dynamic arrays; it cannot be used to resize static arrays.
- Using the ‘Preserve’ keyword can result in slower performance, so it is best to use it only when necessary.
- When using the ‘Preserve’ keyword, the new size of the array must be within the bounds of the existing array. For example, if the original array is 10 elements, the new size cannot be less than or equal to 10.
- The ‘Preserve’ keyword cannot be used with array variables declared as Variant, or with arrays that are passed as arguments to a procedure.
- If the ‘Preserve’ keyword is omitted, all the elements of the array will be reset to their default values when resizing.
Concluding Thoughts
The ReDim statement is an essential tool in VBA for working with dynamic arrays. It provides added flexibility in handling large sets of data without compromising performance. By allowing arrays to be resized during runtime, it opens up a whole new range of possibilities in programming. Understanding the purpose, syntax, and examples of using the ReDim statement is crucial in making the most out of this powerful tool.
I hope you found this blog post informative and helpful in understanding the ReDim statement in VBA. Have you used the ReDim statement in your code? What challenges have you faced while using it? I would love to hear your views and feedback in the comments below.
