Tip:
Highlight text to annotate it
X
In this lesson, we will discuss For loops
If you want to execute a set of statements a certain number of times, you should use a For loop.
A For loop begins with the For statement, followed by the name of a variable whose value will change by an amount delta each time the
statements in the loop are executed.
Initially this variable, which is called var here, is assigned the value of startval.
startval and endval determine the range of values var must stay within for the loop to proceed.
Specifying the increment delta is optional. If you omit Step delta, the default step size will be +1.
delta may be positive or negative.
At the start of the loop, if delta is positive the loop will proceed if var is less than or equal to endval.
If the initial value of var is less than or equal to endval, the statements inside the loop are executed.
However, if the initial value of var is greater than endval, VBA will skip the loop.
After the statements are executed and the loop reaches the Next var statement, var is incremented by delta and VBA returns to the
top of the loop.
If var is still less than or equal to endval, the statements in the loop will be executed again with the updated value of var.
This looping process is repeated until var is greater than endval, at which time the loop is terminated.
If delta is negative, the loop will proceed if var is greater than or equal to endval.
If the initial value of var is greater than or equal to endval, the statements inside the loop are executed.
However, if var is less than endval VBA will skip the loop.
When the loop reaches the Next var statement, var is decreased by delta and VBA returns to the top of the loop.
If var is still greater than or equal to endval, the statements in the loop will be executed again with the updated value of var.
This looping process is repeated until var is less than endval, at which time the loop is terminated.
It is strongly suggested that you indent the statements inside your loops by three or four spaces to help make your code more readable.
Let’s see an example of how to use a For loop.
This program will calculate the sum and average of all integers 1 through 3 using a For loop.
The sum and average are stored in the variables sum and avg.
In this For loop, the variable i will be the counter variable.
i starts at 1 and will increase its value by 1 each time the Next i statement is executed.
When i becomes greater than 3, the loop will terminate.
Step 1 is optional here because the default increment size is +1.
Inside the For loop, two statements are executed.
The first statement is: sum = sum + i
In order to calculate the summation, we must add the current value of i to a running total which is stored in the variable sum.
Notice that sum is set to 0 initially before the For loop begins.
Whenever you use a For loop to perform a summation, you should assign 0 to the variable that will store the summation before the For loop
in case that variable contains a value other than 0.
The second statement creates a message box that displays the current value of the variables i and sum.
After the loop terminates, the average is calculated and stored in the variable avg.
The average is sum divided by the number of integers, which is 3.
Finally a message box displays the current value of i, sum, and avg.
When we run the program four message boxes appear.
The first message box states: i=1 sum=1
The second message box states: i=2 sum=3
The third message box states: i=3 sum=6
The fourth message box states: i=4 sum=6 average=2
Let’s explore why these values appear.
When the variables i, sum, and avg are declared, they are assigned default initial values of 0.
Next sum is assigned the value 0.
In this particular example, the sum = 0 statement is unnecessary since sum is assigned the value 0 when it was declared.
However, it is good programming practice to set the summation variable to 0 before the loop begins just in case you assigned a non-zero
value to it earlier in the program.
At the start of the For loop, i is assigned the value 1.
Since i is less than or equal to 3, we enter the loop.
sum is assigned the current value of sum + i, which is 0+1.
The new value of sum is 1.
A message box appears that displays the value of i and sum, which are 1 and 1.
We then reach the Next i statement and increase i by the step size, which is +1.
i now has the value 2.
Since i is less than or equal to 3, we go back to the beginning of the loop.
sum is assigned the current value of sum + i, which is 1+2.
The new value of sum is 3.
A message box appears that displays the value of i and sum, which are currently 2 and 3.
We reach the Next i statement again and increase the value of i by 1.
i now has the value 3.
Since i is still less than or equal to 3, we go back to the beginning of the loop.
sum is assigned the current value of sum + i, which is 3+3.
The new value of sum is 6.
A message box appears that displays the value of i and sum, which are 3 and 6.
We arrive at the Next i statement and increase the value of i by 1.
i now has the value 4.
Since i now is greater than 3, we exit the loop.
avg is assigned the value of sum / 3, which is 6 / 3, or 2.
A message box displays the value of i, sum, and avg, which are 4, 6, and 2.
Here is the flowchart representation of this program.
The variable sum is set to 0.
When the loop begins, i is set to 1.
The exit condition for the loop is when i > 3 is True.
Since i > 3 is False at the moment, we enter the loop and execute the two statements inside the loop.
We return to the top of the loop and increase i by 1.
i > 3 is still False, so the loop is repeated.
This looping process continues until i > 3 is True, at which point the loop is broken.
A final message box appears and the program is terminated.
Notice if we want to change our program to add all integers from 1 to 4, we would have to change the number 3 to the number 4 in two
locations.
If we accidentally forgot to change one of the 3s to a 4, our program would not work correctly.
It would be safer and easier if we created another variable and assigned the value of 4 to that variable.
When we run the program, we will calculate the sum and average of all integers from 1 to n. In this case n is 4.
The sum is 10 and the average is 2.5.
If we want to adjust the number of integers that are summed, we only have to adjust the value of n at one location in the program.
Here is how you would write a program that adds all integers between 1 and 4 using a negative step size.
The variable n is assigned the value 4 and sum is set to 0.
The counter variable i starts at n, which is 4.
Since the step size is a negative value, the exit condition for the loop is when i is less than 1.
At the moment, 4 is greater than 1 so we enter the loop.
sum is assigned the current value of sum + i, which is 0+4.
A message box appears that displays the value of i and sum, which are currently 4 and 4.
At the Next i statement, we decrease i by 1 and now i has the value 3.
3 is greater than 1, so we enter the loop again.
This process continues until i is 2, then 1, and then finally 0.
When i has the value 0, the loop is broken since 0 is less than 1.
After the loop is terminated, the average is calculated and assigned to the variable avg.
A message box displays the value of i, sum, and avg.
i = 4 sum = 4 i = 3 sum = 7 i = 2 sum = 9
i = 1 sum = 10 i = 0 sum = 10 average = 2.5
We can terminate For loops early using the Exit For statement.
In this program, the For loop has the potential of undergoing 11 iterations, with the value of x changing from 0 to 1 in increments of 0.1.
However, if the value of x is greater than 0.2, the Exit For statement will be executed, terminating the loop prematurely.
Inside loop, x = 0 Inside loop, x = 0.1 Inside loop, x = 0.2
Outside loop, x = 0.3
When x is 0.3, the Exit For statement is executed and the For loop is terminated.