Dynamic Arrays

Dynamic arrays can be resized at run-time using the "ReDim" statement.
These are also known as Variable Arrays or Variable-Length Arrays.
This type of array is very useful when you don't know the exact size of the array at design time.
Before you can use a dynamic array you must define an initial number of elements.
A dynamic array does not have a pre-defined number of elements
Resizing arrays at run-time can be very inefficient. If adding a lot of data dynamically do not resize the array after every new element but after every 10 or so.

Declaring a Dynamic Array

You can declare a dynamic array by using an empty set of parentheses.
Dynamic arrays are always declared with empty parentheses.
The parentheses must always be attached to the variable, never the data type.
When you need to define the size of the array you can use the ReDim statement.

Dim sCellValues() As String 
ReDim aCellValues(3)

ReDim Statement

ReDim creates a new 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.

Initialising a Dynamic Array

Dim arValues() As String 
ReDim arValues(3)
arValues(0) = "Monday"
arValues(1) = "Tuesday"
arValues(2) = "Wednesday"
arValues(3) = "Thursday"

Dim arMyArray() As Variant

This can be used to create an array of Variant data types.

Dim arValues() As Variant 
ReDim arValues(3)
arValues(0) = 50
arValues(1) = "text"
arValues(2) = false
arValues(3) = -4.6

Can't test if the array is empty !!!

arValues = Empty   'this doesn't work !!! 

Variant - Data Type

This method uses a Variant data type to contain the whole array. This is different to an array of Variant data types.
The following declaration can also be used to declare a dynamic array athough this method should not be used unless there is a good reason.

Dim arValues As Variant 
arValues = Null
If (arValue is Null)

One reason you might use a Variant data type to contain your array is when you want to return an array from a function.
Using this method (without the parentheses) allows you to assign "Null" to the array when you want to represent no data.

Variat - ARRAY Function

If you want to populate an array quickly using a series of comma-delimited values then you must declare your array as a Variant.
When using the ARRAY function the array always starts at zero. Any Option Base statement will be ignored.

Dim myArray As Variant 
myArray = Array(1, 2, 3, 4)
myArray = Array("one", "two", "three", "four")
myArray = Array("50, "text", false, -4.6)

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(10) 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 datatype of the array when you change the dimension.
It is optional if you include the datatype (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.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited