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.
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
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
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
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