In this VBA excel automation, we shall see some basic concepts related to array. We shall see how to declare an array, how to reset the array dimensions and some other operations.
An array is a variable which can contain multiple items of same data type.
Types of Array
We have 2 types of arrays in VBA –
- Fixed Size Array
- Dynamic Array
Fixed Size Array ( Static Array)
In fixed size array, we mention the size of the array when we declare the array. We can not change the size of the fixed size array during run time. To declare a fixed size array, we use Dim statement with array name.
Code( Static Array)
Sub SampleFixedSizeArray() 'Declare the fixed size or static array Dim SampleArray(10) As Integer 'Print the lower and upper bound of fixed size array Debug.Print "Lower bound of array = " & LBound(SampleArray) Debug.Print "Upper bound of array = " & UBound(SampleArray) End Sub
We cannot resize the static array, if we try to use ReDim statement with static array, it will throw an error as shown below.
In this statement shown below , we have an array named as “SampleArray”, it can contain 11 values of integer type. Subscript of array can start at 0 or 1. It depends on how we have declared Option Base, if we have explicitly set Option Base 1, then array subscript will start at 1. By default, Option Base is 0, so array subscript will start at zero.
Dim SampleArray (10) as Integer
In dynamic array we do not initiate the size of the array when we declare the array, rather size is calculated or found during run time.To declare array dynamically, we shall write a statement like Dim SampleArray () as Integer.In this case the size of array will not be declared initially, rather it will be declared with the help of ReDim statement. The parenthesis around the array name will be kept blank initially. Dynamic array is used when we do not know the size of the array, we only get to know about the array size during run time.
For example, we want to store multiple cell values in an array, and the source data files keeps on changing, due to this, number of cells to be stored also changes, so to handle such cases we shall use ReDim statement.
Code [ Dynamic Array ]
Sub SampleDynamicArray() 'Declare the dynamic array Dim SampleArray() As Integer 'Set the size of an array ReDim SampleArray(5) 'Print the lower and upper bound of dynamic array Debug.Print "Lower bound of array = " & LBound(SampleArray) Debug.Print "Upper bound of array = " & UBound(SampleArray) End Sub
If we have already declared the size of a dynamic array and still we change the size of dynamic array using ReDim statement, then in that case all the data stored in array will be lost. So, to keep the data which already exists in the dynamic array, we use Preserve modifier with ReDim statement.We can see that in the code below.
Sub SampleRedimStatment() 'Declare the dynamic array Dim SampleArray() As String 'Change the array size ReDim SampleArray(2) SampleArray(0) = "NYC" SampleArray(1) = "LA" SampleArray(2) = "SF" 'Change the array size ReDim Preserve SampleArray(5) 'Print the lower and upper bound of fixed size array Debug.Print "Lower bound of this array = " & LBound(SampleArray) Debug.Print "Upper bound of this array = " & UBound(SampleArray) 'Loop to access array elements For i = LBound(SampleArray) To UBound(SampleArray) Debug.Print "Array value for subscript " & i; " is " & SampleArray(i) Next i End Sub
We can see in the code above that we had an array “SampleArray” which has size as 3 which contains the name of 3 cities “NY”,”LA”,” SF”. If we do not use Preserve modifier and run the code above to increase the size using ReDim statement, all the existing data will be lost, we can see the expected output in the image below.
To avoid such situation we use Preserve modifier, we can see in the image below if we use Preserve modifier then all the existing values will be preserved.
Constraint with ReDim
We can use ReDim with the maximum dimension, which means if we have 2-dimension array, we can only change the size of 2nd dimension, if we have 3-dimension array then we can only resize the 3rd dimension.In the code below we can see that we had a 3-dimensional array and when we try to change the 2nd dimension, it will throw an error . We can increase or decrease the size of an array dimension.
Sub RedimMultiDimensionArray() Dim SampleArray() As Integer 'We had a sample 3 dimension array ReDim SampleArray(10, 12, 14) 'We can increase the last dimension ReDim Preserve SampleArray(10, 12, 20) 'We can also decrease the last dimension ReDim Preserve SampleArray(10, 12, 8) 'We can not change 1st or 2nd dimension 'ReDim Preserve SampleArray(10, 15, 14) -Will Throw Error 'ReDim Preserve SampleArray(11, 12, 14) -Will Throw Error End Sub
Erase or Delete Array Elements
Erase statement will remove all the array elements and release the space allocated to array elements.
Sub EraseElement() Dim SampleArray() As String ReDim SampleArray(2) SampleArray(0) = "NY" SampleArray(1) = "LA" SampleArray(2) = "SF" Erase SampleArray End Sub
Post you may like