Example
Dynamic Arrays
Adding and reducing variables on an array dynamically is a huge advantage for when the information you are treating does not have a set number of variables.
Adding Values Dynamically
You can simply resize the Array with the ReDim Statement, this will resize the array but to if you which to retain the information already stored in the array you'll need the part Preserve.
In the example below we create an array and increase it by one more variable in each iteration while preserving the values already in the array.
Dim Dynamic_array As Variant
' first we set Dynamic_array as variant
For n = 1 To 100
If IsEmpty(Dynamic_array) Then
'isempty() will check if we need to add the first value to the array or subsequent ones
ReDim Dynamic_array(0)
'ReDim Dynamic_array(0) will resize the array to one variable only
Dynamic_array(0) = n
Else
ReDim Preserve Dynamic_array(0 To UBound(Dynamic_array) + 1)
'in the line above we resize the array from variable 0 to the UBound() = last variable, plus one effectivelly increeasing the size of the array by one
Dynamic_array(UBound(Dynamic_array)) = n
'attribute a value to the last variable of Dynamic_array
End If
Next
Removing Values Dynamically
We can utilise the same logic to to decrease the the array. In the example the value "last" will be removed from the array.
Dim Dynamic_array As Variant
Dynamic_array = Array("first", "middle", "last")
ReDim Preserve Dynamic_array(0 To UBound(Dynamic_array) - 1)
' Resize Preserve while dropping the last value
Resetting an Array and Reusing Dynamically
We can as well re-utilise the arrays we create as not to have many on memory, which would make the run time slower. This is useful for arrays of various sizes.
One snippet you could use to re-utilise the array is to ReDim the array back to (0), attribute one variable to to the array and freely increase the array again.
In the snippet below I construct an array with the values 1 to 40, empty the array, and refill the array with values 40 to 100, all this done dynamically.
Dim Dynamic_array As Variant
For n = 1 To 100
If IsEmpty(Dynamic_array) Then
ReDim Dynamic_array(0)
Dynamic_array(0) = n
ElseIf Dynamic_array(0) = "" Then
'if first variant is empty ( = "") then give it the value of n
Dynamic_array(0) = n
Else
ReDim Preserve Dynamic_array(0 To UBound(Dynamic_array) + 1)
Dynamic_array(UBound(Dynamic_array)) = n
End If
If n = 40 Then
ReDim Dynamic_array(0)
'Resizing the array back to one variable without Preserving,
'leaving the first value of the array empty
End If
Next
