MS Access 2010 VBA tutorial- 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.
To 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. Example: Dim i,sum, r As Integer sum=0 For i=0 to 4 ‘assign values to the array x(i)=i*i Next
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.
-Two-dimensional array Two-dimensional array stores values in rows and columns.
Example: To declare a two-dimensional array(2 rows and 3 columns) called arr, you can write:
| Index || 0 || 1 || 2 |
| 0 || 10 || 20 || 3 |
| 1 || 41 || 15 || 25 |
Dim arr(2,3) As Integer
Dim i,j As Integer Dim val As String ‘Assign values to the array without using a loop
arr(1,1)=15 arr(1,2)=25 ‘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