ReDim Statement

The REDIM statement will initialise and resize a dynamic array.
If the array is declared as a Variant data type this statement will actually create the array.
You can resize an array by modifying its upper bound
You cannot resize the lower bound of an array.
The larger and more complex the array, the longer it takes to resize it.
There is no limit to the number of times you can ReDim a dynamic array.
It is possible to declare a dynamic array and specify the initial number of elements at the same time using the ReDim statement.
Instead of the two lines above you could use "ReDim aCellValues(3) As String"
This shorthand is not encouraged and is not best practice.

ReDim - Resizing

This statement can only be used with dynamic arrays (not fixed arrays).
You can define the exact number of elements in a dynamic array by using the ReDim statement.
Each ReDim statement can change the number of elements as well as the lower and upper bounds.
Each time you execute the ReDim statement, all the values currently stored in the array are lost.
Before trying to resize you should check that the array is not static.

Dim sCellValues() As String 

ReDim aCellValues(50)
ReDim aCellValues(50) As String
ReDim aCellValues(1 To 50)
ReDim aCellValues(1 to 50) As String

A compile error will occur if you try and change the data type of the array when you change the dimension.
It is optional if you include the data type (As String) at the end of the statement.

ReDim - Implicit Declarative Statement

The ReDim statement acts as a declarative statement if the variable it uses has not been previously defined.
The following code is not identified as a compilation error:

Public vArrayName As Variant 

Private Sub MyProcedure()
   ReDim vArrayNameSpeltWrong(50)
End Sub

If another variable with the same name is created later, even in a wider scope, ReDim will refer to the later variable and will not cause a compilation error even if Option Explicit is present.


You should always check that the array you are about to resize is dynamic and not static.
If the array is static you will get a run-time error.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext