MS Access 2010 VBA tutorial- Array

VBA 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 array

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

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.

Index  0 1 2
0 10 20 3
1 41 15 25
Example: To declare a two-dimensional array(2 rows and 3 columns) called arr, you can write:
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,2)=25 ‘Accessing the elements of the two-dimensional array using for next loops
for i=0 to 1
   for j=0 to 2
       MsgBox arr(i,j)


-Dynamic array

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


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.