VBA for MS Excel 2010 tutorial- Array
An Array can be said as a collection of values of the same type. If you want to store many values of the same type temporarily in memory, it is better to store them in an array then you can access each element by its index that is from 0 to the array size subtracted by 1.The array can have multiple dimensions. Most programmers use one-dimensional array and two-dimensional arrays.
-One-dimensional arrayTo use an array, you must declare it. You can declare a one-dimensional array as shown below:
Dim array_name(size) As Data type
Example: To declare an array called x to store 5 values of (elements) integral numbers, you can write:
Dim x(4) As Integer
To assess the array elements, you can use a loop to do that.
Dim i,sum, r As Integer
For i=0 to 4 ‘assign values to the array
For each v in x ‘read the elements of the array in to Excel cells
Note: the start index of the array is 0 and its size is equal to last index added by 1.
-dimensional array stores values in rows and columns.
Example: To declare a two-dimensional array(2 rows and 3 columns) called arr, you
Dim arr(2,3) As Integer
Dim i,j As Integer
Dim val As String
‘Assign values to the array without using a loop
‘Accessing the elements of the two-dimensional array using for next loops
for i=0 to 1
for j=0 to 2
The arrays discussed above are fixed-size array. You can also create an array that dynamically expands its size as needed. This type of array is called dynamic array. To create a dynamic array, you don't need to specify the size of the array when it is declared. And later, the keywords ReDim Preserve are used to change the size of the array.
Dim arr() As Integer
ReDim Preserve arr(UBound(darr)+10) 'Increase the array size by 10