Arrays are a set of indexed elements for the same data type variable. Each element is independent but belongs to the same group variable and is better than have several single variables of the same type.

These are declared like a standard variable with the option of setting a data type and its scope.

Arrays are either declared in design time with the number of elements defined or at run time making it dynamic.

Example of Fixed Array:

'Declaring a fixed array
Sub FixedArray()
    Dim strWeek(7) As String
    strWeek(0) = "Sunday"
    strWeek(1) = "Monday"
    strWeek(2) = "Tuesday"
    strWeek(3) = "Wednesday"
    strWeek(4) = "Thursday"
    strWeek(5) = "Friday"
    strWeek(6) = "Saturday"
   ...................
End Sub

The value entered between the parentheses defines the number of elements for the array variable starting at point zero. Therefore, for an array variable of seven elements, the starting element number will be zero and the last will be six.

Example of Dynamic Array:

'Declaring a fixed array
Sub DynamicArray()
Dim strWeek() As String
...................

   ReDim strWeek(7)

   strWeek(0) = "Sunday"
   strWeek(1) = "Monday"
   strWeek(2) = "Tuesday"
   strWeek(3) = "Wednesday"
   strWeek(4) = "Thursday"
   strWeek(5) = "Friday"
   strWeek(6) = "Saturday"

...................
End Sub

Dynamic arrays allow array variables to grow during the run time of the procedure. This may be required, as the process may not know the full size of the intended variable.

The keyword ReDim allows array variables to be re-declared to a new size. The above example declares an array variable of unknown size and then uses the ReDim command to redefine the size.

When using the ReDim command, any previous sizes and values that may be present are lost and set to nothing.

In the event of preserving the previous size array and wanting to extend the size, users can use Preserve keyword.

ReDim Preserve strWeek(14)

Multi-Dimension Array

Arrays can also be multi-dimensioned and can store up to 60 sets of elements.

Sub MulitArray()
    Dim intMulti(1 To 5, 1 To 10) As Integer
    intMulti(1, 1) = 10
    intMulti(2, 1) = 20
    intMulti(3, 1) = 30
    intMulti(4, 1) = 40
    intMulti(5, 1) = 50
    intMulti(1, 2) = 60
    intMulti(2, 2) = 70
    .......................
    intMulti(5, 10) = 500
    .......................
End Sub

Each dimension group has been set to start at 1 (instead of the default 0). In fact users can start at any integer value providing the stop value is greater than the start value.

Array Function

Another way to set values to an array variable is to use the VBA Array function:

Sub ArrayFunctionExample()
    Dim strWeek As Variant
    Dim strDay As String
    strWeek = Array("Sunday", "Monday", "Tuesday", _
             "Wednesday", "Thursday", "Friday", "Saturday")
    strDay = strWeek(2) ' strDay now contains "Tuesday".
    .......................
End Sub

The Array function returns a Variant data type and therefore must be declared as a variant. Each element can be converted into a string type as required. The first item in the Array function equals element zero and so on.

Setting Option Base

In some programming languages, it is not uncommon to have the first element of an array to be equal to one instead of zero. In situations that the procedure needs to simulate this environment, users can use the following statement:

Option Base 1

This is declared at the top of the module before the first procedure and affects the entire module. The value 1 changes the base element to one.

IsArray Function

This function can be used to test if a variable is an array and return either True or False.

Sub ArrayFunctionExample()
    Dim strWeek As Variant
    Dim strDay As String
    strWeek = Array("Sunday", "Monday", "Tuesday", _
            "Wednesday", "Thursday", "Friday", "Saturday")
    MsgBox IsArray(strWeek) 'True.
    MsgBox IsArray(strDay) 'False.
    .......................
End Sub

UBound and LBound Functions

Both bound functions return a Long value of the highest (upper) and the lowest (lower) element number for an array variable.

Two arguments, one optional:

Variable = UBound( ArrayName [, Dimension] )

Variable = LBound( ArrayName [, Dimension] )

The optional second argument only applies if the array is a multi-dimensioned array variable.

Sub UpperArray()
    Dim intMulti(1 To 5, 1 To 10) As Integer
    Dim intUpper As Integer
    intMulti(1, 1) = 10
    intMulti(2, 1) = 20
    intMulti(3, 1) = 30
    intMulti(4, 1) = 40
    intMulti(5, 1) = 50
    intMulti(1, 2) = 60
    intMulti(2, 2) = 70
    .......................
    intMulti(5, 10) = 500
    .......................
    MsgBox UBound(intMulti, 1) 'shows 5
    MsgBox UBound(intMulti, 2) 'shows 10
    .......................
End Sub

Next Topic: Collections

Want to teach yourself Access? Free online guide at About Access Databases

Note: When you work with arrays (variables), users like to use the distinction between groups and single variables where a single variable is also known as a scalar variable.

VBA Keywords: MsgBox, IsArray, UBound, LBound, Array, Option Base 1.