How to Loop Through an Array of Values in VBA
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”
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:
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,”
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.