Introduction to Arrays

An array is a sequence of values in an order that can be referred with indexes where identifying the start index and the end index of the array is important. This is called the lower bound and upper bound of array.

Looping through the values can be done using the “For” loop statement and using “Next” at the end indicating the next value in the series to go through the loop .

The lower bound and the upper bound of the array will be used as a counter to track where the loop starts and stops.

The following is an example of array values and how to declare them before proceeding with a loop.

Declaring The Array Variable

Sub Example_Array()
Dim Array_Ex(5) As Integer
‘Though the declaration has been made for the number 5 , this Array can hold  6 numbers as the indexation starts from ‘0’
Array_Ex(0) = 10
Array_Ex(1) = 20
Array_Ex(2) = 30
Array_Ex(3) = 40
Array_Ex(4) = 50
Array_Ex(5) = 60
End Sub

Identify the Upper Bound and Lower Bound Values

Recognizing the upper bound and lower bound of an array is the first step. Once you do, you can start looping through the values.

The lower bound refers to the lower index number of an array, which in this example is 0.

Dim LowerB As Integer
LowerB = LBound(Array_Ex)

The Upper bound refers to the upper index number of an array which in the Example is 5

 Dim UpperB As Integer
  UpperB = UBound(Array_Ex)

Use the “For” Loop Method Using the Defined Counter

The exercise here is to use the “For” Loop to add up the values of all the array indexes and provide us with the total in a message box. Hence, we declare a variable “Total” and assign it the value “0.”

Dim Total As Integer
Total = 0

With every value that gets looped inside the “For “ activity, the value that the index holds will get added to the “Total.” The message box provides a total after every loop that helps confirm the total up to that particular index.

For i = LowerB To UpperB
 Total = Total + Array_Ex(i)
 MsgBox (Total)
Next i

Using a Nested Loop

To make it more interesting, let’s add an exercise to find the array values that sum up to “70.”

This requires adding a variable that will hold the list of array value combinations that add up to 70.

Dim Combos_Values As String
Combos_Values = "Combination of arrays that give Total= '70' : "

Another “For” loop is placed between the existing for each loop, to make sure all the possible combinations of values are looped through, like:

Array_Ex(0) + Array_Ex(0) 
Array_Ex(0) + Array_Ex(1)
Array_Ex(0) + Array_Ex(2)
Array_Ex(0) + Array_Ex(3)
Array_Ex(0) + Array_Ex(4)
Array_Ex(0) + Array_Ex(5)
Array_Ex(1) + Array_Ex(0)
Array_Ex(1) + Array_Ex(1)
Array_Ex(1) + Array_Ex(2)
Array_Ex(1) + Array_Ex(3)
Array_Ex(1) + Array_Ex(4)
Array_Ex(1) + Array_Ex(5)

And so on…

The piece of code used to achieve this is:

For j = LowerB To UpperB
    If Array_Ex(i) + Array_Ex(j) = 70 And i <> j Then
      Combos_Values = Combos_Values + "," + CStr(Array_Ex(i)) + "+" + CStr(Array_Ex(j))
    End If
 Next j

The “I <> j” will make sure that the same array numbers are not added to the result.

The complete code looks like this:

When the above code is run the output will be:

“Combination of arrays that give Total= '70' : ,10+60,20+50,30+40,40+30,50+20,60+10”

Multi-dimensional Arrays

An array variable can hold data with up to 60 dimensions of values. The most commonly used array is the 2-dimensional data table or Excel worksheet where the 2 dimensions refer to the rows and columns. They are declared in a code as array (row, column) indexing.

Let’s take an example of the below table names and ages of four people and how it is represented as an array variable:

Rob	8
Paul	15
Amy	13
Ben	16

Sub Example_Array3()
Dim Array_Ex(3, 1) As Variant

Array_Ex(0, 0) = "Rob"
Array_Ex(0, 1) = 8
Array_Ex(1, 0) = "Paul"
Array_Ex(1, 1) = 15
Array_Ex(2, 0) = "Amy"
Array_Ex(2, 1) = 13
Array_Ex(3, 0) = "Ben"
Array_Ex(3, 1) = 16

End Sub

The exercise is now to categorize the list of children here based on their age to send them to a tournament. The categories are under 14  (10-13 years of age) and under 18 (14 to 17 years of age).

‘ Create the variable to hold the list of names for under 14 
Dim Under14 As String
Under14 = "Under 14 category participants : "

‘ Create the variable to hold the list of names for under 18
Dim under18 As String
under18 = "Under 18 category participants : "

Start the looping by using the Lbound to Ubound commands for the row number in the array. The column number is fixed as  “1” here as we need only ages for the condition to work. If the condition is fulfilled, the name of the person gets added to the respective category string.

For i = LBound(Array_Ex, 1) To UBound(Array_Ex, 1)
    If Array_Ex(i, 1) > 9 And Array_Ex(i, 1) < 14 The
        Under14 = Under14 + CStr(Array_Ex(i, 0)) + ",       
    ElseIf Array_Ex(i, 1) > 13 And Array_Ex(i, 1) < 19 Then
        under18 = under18 + CStr(Array_Ex(i, 0)) + ",     
    End If
Next i

When the above code gets executed, the below results appear:

“Under 14 category participants: Amy,”

“Under 18 category participants: Paul, Ben,”

Conclusion

Arrays are a series of values stored in a single variable, wherein every value from this series needs to be worked on separately. Each of these values under the variable can be referred to using its index number. This also reduces the use of multiple variables.

Arrays can be used to store a series of string values, series of data rows from a table, series of cells from a table using row-column reference, series of lists, etc.

There are various methods used to declare an array depending on whether the values that go into an array are available at hand or to be extracted from a source.

I have quoted here a couple of examples for better understanding. This is one of the widely used activities for playing with numbers, thus helping us with complex logic creation with fewer lines of code.