DECLARING ARRAYS

Before you use an array, you must declare it.

An array is declared with a Dim or a Public statement, just like a regular variable. However you also must specify the number of elements in the array by specifying the first index number, the keyword to, and the last index number, all within the parentheses.

For example, if a certain lecture has a total of 65 students 

Dim Score(1 to 65) As Integer
This sets aside 65 subscripted variables, each is capable of storing an integer representing the score for that student.

Each of these array elements are indexed by an integer varying from 1 to 65.

The score for the first student is stored in Score(1), and the second student is stored in Score(2), etc., so we can assign values to them by

Score(1) = 67
Score(2) = 83
...
Assuming that we have filled the array Score with the score of each of the 65 students in the class, we can then compute the average score using a loop as follows:

Dim average As Double, sum As Double 
Dim count As Integer
Dim n As Integer
sum = 0.0
count = 65
For n = 1 To count
    sum = sum + Score(n)
Next n
average = sum / count
MsgBox "The class average is: " & average
If only the upper index number is specified in declaring an array, then VBA assumes the default lower index number of 0, that is the first element of the array has an index of 0. Many modern languages like C and C++ actually require that the first element of an array is indexed by 0.

For example the following declarations are equivalent and gives a 101-element array of doubles.

Dim MyArray(100) As Double
Dim MyArray(0 to 100) As Double
In languages like Fortran, the first element of an array is indexed by 1 rather than by 0 (an index of 0 is not allowed). In VBA, the default lower index number can be changed to 1 globally by including the following statement in the Declarations section of your module 
Option Base 1
In that case the following declaration are equivalent and gives a 100-element array of doubles. 
Dim MyArray(100) As Double
Dim MyArray(1 to 100) As Double
In VBA, indices can even be negative integers.

An array declared in this section here is called a fixed-size array (as oppose to dynamic array to be discussed later) since the lower and upper indices must be specified as constants and cannot be variables.

In case we do not know exactly the total number of elements in an array, the typical strategy is to declare an array sufficiently large enough for the purpose under consideration.

For example if we do not know the total number of students enrolled in a class, since we do not have any class that has an enrollment of more than 500, we can declare an array of size 500 to store the scores of an examination (of course in order to work with the array, we need to know the exact size of it, but that knowledge typically comes after the array has been declared).