An array is a collection of items of the same data type. All items have the same name and they are identified by a subscript or index. When you need to work with several similar data values, you can use array to eliminate the difficulties of declaring so many variables.
For example, if you want to compute the daily sales and sum the sales amount after 30 days, you don’t need to have 30 variables. Just simply declare an array of size 30 and get your work done !
Declaring an array
Syntax: Dim Variable_Name(index) As [Type]
Example
Dim month(10) As Integer '11 elements
or
Dim month(1 to 12) as Integer '12 elements
In the first line, month(10) is a collection of 11 integer values or items. month(0) is the 1st item and month(10) is the 10th & last item of the array. So 0 and 10 are respectively the lower bound and upper bound of the array.
In the other line, month(1 to 12) is a collection of 12 integer values or elements or items where month(1) is the 1st item and month(12) is the last. So 1 and 12 are respectively the lower bound and upper bound of the array.
Types of arrays
The array used in the example is a one-dimensional and fixed-size array. An array can have more than one dimension. The other types of arrays are multi-dimensional arrays, Dynamic arrays and Control arrays.
Fixed-Size Array: We know the total number of items the array in the above example holds. So that is a Fixed-Size array.
LBound and UBound
The LBound and Ubound functions return the lower bound and upper bound of an array respectively.
Example
Private Sub cmdDisplay_Click()
Dim arr(10) As Integer
a = LBound(arr)
b = UBound(arr)
MsgBox "Lower bound = " & a & " Upper bound = " & b
End Sub
Initializing an array
You can use For Loop to initialize an array.
Example
Dim day(10) As Integer, i As Integer
For i = 0 To 10
day(i) = InputBox("Enter day value")
Next i
You can also initialize each array item separately in the way a variable is initialized.
Example
This program inputs the Sale amount of each day and sums the total amount of 5 days.
Private Sub cmdStart_Click()
Dim SaleDay(1 To 5) 'Sale in a particular day
Dim i As Integer, Sale As Long
Sale = 0
For i = 1 To 5
SaleDay(i) = InputBox("Enter Sale amount of Day " & i)
Sale = Sale + SaleDay(i)
Next i
MsgBox "Total Sale of 5 days = $" & Sale
End Sub