Table of Contents
INTRODUCTION
If you have programmed ever, you must know the importance of the loops.
If you are new to programming, this information is for you. Looping is the process of repeating a particular action to a fixed number of times.
This is a way of controlling the execution. In programming the code is compiled line by line but if we want to control it such as, leading the control to some specific part of the program, or executing any statement multiple times. VBA provide us with many such options to control our execution as per our need.
Loops are one of the component which is going to be used a lot while doing any check or filling any values.
Whenever we check or compare any value, we need to use the loop so that a Checking statement compares a particular value with the other present values. After finding the correct value, we need to put the value through the function.
Now if we need only one value, we can end the loop or if we need other values or all the values fulfilling the conditions, we can again start the loop. This particular operation we can’t do in Excel directly.
Thanks to VBA for that.
Let us have a look at the different types of loops available for us in VBA.
TYPES OF LOOPS IN VBA
Mainly VBA supports many types of loops. Here is the list
- For-Next Loop
- Do-While Loop
- Do-Until Loop
FOR-NEXT LOOP
The first type of loop is the FOR-NEXT loop. The FOR-NEXT loop works and has a controlled number of iterations mentioned by a variable. Let us see the format first.
The following format shows the way FOR-NEXT LOOP is used in VBA for Excel.
FOR variable=starting_value TO final_value
INSTRUCTIONS/STATEMENTS
EXIT FOR 'If some condition is given for exit
INSTRUCTIONS/STATEMENTS
NEXT variable
EXPLANATION OF THE SYNTAX
FOR is the keyword which will start the formation of the loop.
The variable is any variable that we want to handle the iterations.
It can be any variable that is predefined or it can be defined directly here also.
starting_value is the starting value given to the variable
TO is the keyword that comes after the initial value
final_value is the final value, the variable will take, up to which the iterations will occur.
INSTRUCTIONS/STATEMENTS are the coding portions that we want to do within the loop.
EXIT FOR is the keyword for instantly breaking the loop if any condition is there that we have defined and need to break the loop.
It’ll immediately break the loop and control would come after the EXIT FOR.
NEXT variable NEXT is the keyword for the increment. the variable is the same variable in the first step which is increasing.
SIMPLE EXAMPLE OF FOR-NEXT LOOP IN VBA
Let us create a very simple FOR NEXT loop which will sum the digits up to the given number of terms.
WE’LL FIND THE SUM OF SERIES 1+2+…+12 USING THE LOOPS
CODE:
'gyankosh.net
'EXAMPLE 1 SIMPLE LOOP
' PROCEDURE TO FIND THE SUM OF 1+2+....+12
Sub Example1()
Dim i As Integer 'variable for counter
Dim temp As Integer 'temp variable for holding cumulative value
temp = 0 'counter started
For i = 1 To 12
temp = temp + i
Next i 'incrementing the counter
MsgBox ("The sum of the series 1+2+...+12 is " & temp) 'output
End Sub
EXPLANATION-FOR NEXT LOOP EXAMPLE 1
The comments are already given for a simple understanding. Here is a brief description.
The example starts by defining i as an integer and one more integer TEMP. We need temp for keeping the cumulative sum of the loop.
The loop starts with i=1 value, goes inside, transfers its value to temp, and increments to 2.
Again goes inside the loop and transfers its value to temp. Now, remember that temp already had 1 last time.
So this time it’ll add 2 to 1 i.e. temp at this time is 3 and similarly, the loop will work up to i=12
A message box will show the result as 78. Look below at the running program.
PUTTING AN CUSTOM INCREMENT IN FOR NEXT LOOP
We saw in the last example that when we typed NEXT, the counter went up by 1.
But it’s not that we can’t change the increment level.
A keyword named STEP can be used to fix the increment in the FOR LOOP. This example will describe the FOR LOOP WITH STEP.
Let us create a FOR LOOP WITH STEP to find the sum of this series.
WE’LL FIND THE SUM OF SERIES 2+…+14 USING THE LOOP.
CODE:
'gyankosh.net
'EXAMPLE 2 SIMPLE LOOP WITH CUSTOM INCREMENT
' PROCEDURE TO FIND THE SUM OF 2+4+....+14
Sub Example2()
Dim i As Integer 'variable for counter
Dim temp As Integer 'temp variable for holding cumulative value
temp = 0 'counter started
For i = 2 To 14 Step 2 'increment has been set to 2. It can be negative also.
temp = temp + i
Next i 'incrementing the counter
MsgBox ("The sum of the series 2+...+14 is " & temp) 'output
End Sub
EXPLANATION-FOR NEXT LOOP EXAMPLE 2
The comments are already given for a simple understanding. Here is a brief description.
The example is the same as the previous one. The normal explanation can be read there.
The difference here is the use of the STEP keyword.
This word sets the increment to 2 and not the standard increment of 1. As we start with value 2 and after that with an increment of 2, it’ll give the sum of even numbers easily.
The running of the program is shown below.
USE OF EXIT DURING FOR NEXT LOOP
You must remember that in the standard format of FOR NEXT loop, we had mentioned the EXIT statement too.
So now let us test the exit statement. We’ll apply this to the same series of Example 2.
The condition is if the value of temp comes out to be 12 at any instant, it should break the loop immediately.
Let us create a loop and put an IF STATEMENT in between for the loop to exit.
WE’LL FIND THE SUM OF SERIES 2+…+14 USING THE LOOP BUT THE LOOP SHOULD EXIT IF THE SUM OF SERIES COMES TO BE 14.
CODE:
'gyankosh.net
'EXAMPLE 2 SIMPLE LOOP WITH CUSTOM INCREMENT
' PROCEDURE TO FIND THE SUM OF 2+4+....+14
Sub Example2()
Dim i As Integer 'variable for counter
Dim temp As Integer 'temp variable for holding cumulative value
temp = 0 'counter started
For i = 2 To 14 Step 2 'increment has been set to 2. It can be negative also.
temp = temp + i
Next i 'incrementing the counter
MsgBox ("The sum of the series 2+...+14 is " & temp) 'output
End Sub
EXPLANATION-FOR NEXT LOOP EXAMPLE 3
The comments are already given for a simple understanding. Here is a brief description.
The two variables i and temp (for holding the value) are declared in the program.
The loop starts with a STEP of 2 to increment the value of i at the interval of 2.
An instruction has been put inside the loop
IF TEMP=12 THEN EXIT FOR
This instruction will compare the value of temp with 12 and if it comes out to be 12, it’ll go for EXIT FOR and immediately terminates the loop and go to the statement after the NEXT statement.
The result comes out to be 12 as after that the code didn’t let the loop play.
DO-WHILE LOOP
The second type of loop to be discussed is a DO-WHILE LOOP.
The loop again controls the execution and helps in iterations of the same statements as per our requirement.
The main difference in this type of loop from the FOR -NEXT Loop is that For loop always works for a limited number of iterations which we fix by giving the limit, whereas in a do-while loop we put a condition and the looping occurs till that particular condition is met.
The standard format of a DO WHILE LOOP is given below
Do [While condition]
[instructions]
[Exit Do]
[instructions]
Loop
-OR-
Do
[instructions]
[Exit Do]
[instructions]
Loop [While condition]
*Exit loop is only needed if there is any condition .
EXPLANATION OF THE SYNTAX
DO is the keyword that is used to start the loop. It is put at the initiation of the loop.
WHILE is a keyword that controls the condition part of the loop.
After a while, we need to put the condition, which should be satisfied to keep the loop running.
INSTRUCTIONS/STATEMENTS are the coding statements that have to be iterated and which are repeated.
LOOP sends the control to the DO keyword to again start the loop.
The WHILE CONDITION can be used after the LOOP keyword too as shown in the format above.
The difference between both is the first time running the program.
The second format will let the instructions execute for the first time after which the conditions will be tested whereas, in the first one, it’ll run only if the condition is met.
SIMPLE EXAMPLE DO-WHILE LOOP
Let us create an example to find out the previous character of the first space.
Suppose we take the text “what else”.
We need the information till which letter, the loop did perform and when did it come out of the loop. Simply the previous character of the first space.
'gyankosh.net
'Do While Exmple
Sub DoWhileExample1()
Dim a As String
a = "what else"
i = 1
Do While Mid(a, i, 1) <> " "
i = i + 1
Loop
MsgBox ("The loop executed till " & Mid(a, i - 1, 1))
End Sub
EXPLANATION-DO-WHILE LOOP EXAMPLE 1
The comments are already given for a simple understanding.
Here is a brief description.
The example starts with a declaration of a string and an integer which is used for iterations.
The i is given the first value as 1.
We apply the loop with the condition that ith character is not equal to a space.
Inside the loop, there is an increment of i so that it goes and checks the next character.
After the loop is completed we take the output using the msgbox and give the expression.
We have deducted 1 from the value of i, because the i which is being tested, is space and not the letter.
WHILE CONDITION CAN BE PUT NEAR THE LOOP IN THE SAME WAY.
ONLY DIFFERENT LIES IN THE FACT THAT ONCE ALL THE STATEMENTS WILL EXECUTE AND THEN DECIDE WHETHER NEXT LOOP WILL TAKE PLACE OR NOT.
DO-UNTIL LOOP
The THIRD type of loop to be discussed is a DO-UNTIL loop.
It again iterates a particular block of instructions that lie inside the loop. It is just similar to DO-WHILE LOOP or to be specific, the opposite of DO-WHILE LOOP.
Whereas Do While loop runs till a condition is true, Do until loop runs till a particular condition is reached.
That means, the Do While loop would stop when the condition becomes false but the Do Until loop would exit when the condition becomes true.
Let us see the format of DO UNTIL LOOP FIRST.
Do [UNTIL condition]
[instructions]
[Exit Do]
[instructions]
Loop
-OR-
Do
[instructions]
[Exit Do]
[instructions]
Loop [UNTIL condition]
*Exit loop is only needed if there is any condition .
EXPLANATION OF THE SYNTAX
As in the case of DO WHILE, DO is the keyword that is used to start the loop.
It is put at the initiation of the loop.
UNTIL is a keyword that controls the condition part of the loop.
This condition will be checked and IF NOT TRUE, the loop will be executed.INSTRUCTIONS/STATEMENTS are the coding statements that have to be iterated and which are repeated.LOOP sends the control to the DO keyword to again start the loop.
The UNTIL CONDITION can be used after the LOOP keyword too as shown in the format above.
The difference between both is the first time running the program. The second format will let the instructions execute for the first time after which the conditions will be tested whereas, in the first one, it’ll run only if the condition is met.
SIMPLE EXAMPLE DO-UNTIL LOOP
Let us pick a string and find out the total number of characters in this sentence. *Just the characters and not spaces.
'gyankosh.net
'Do Until Example
Sub DoUntilExample1()
Dim a As String 'variable declarations
a = "Hi Welcome to Gyankosh"
i = 1
j = 0
Do Until i > Len(a)
If Mid(a, i, 1) <> " " Then
j = j + 1
End If
i = i + 1
Loop
MsgBox (" The exact number of characters in the given text is " & j)
End Sub
EXPLANATION-DO-UNTIL LOOP EXAMPLE 1
The comments can be checked to understand the code.
A string variable a has been declared. It is the string, we’ll be counting the characters of. Two more variables i and j have been taken.
* We haven’t declared them so VBA will take them as Variants that can be used for anything.
The two variables are needed, one for holding the characters and one for counting the characters.
The loop starts, the condition is that the loop should be more than the length of the string, which is false and so control moves inside the loop.
Another condition, IF is put to check for the spaces. If space comes, the character counter doesn’t work.
Similarly, all the characters are taken. After finishing the loop the output is displayed using a message box.
WHILE CONDITION CAN BE PUT NEAR THE LOOP IN THE SAME WAY.
ONLY DIFFERENT LIES IN THE FACT THAT ONCE ALL THE STATEMENTS WILL EXECUTE AND THEN DECIDE WHETHER NEXT LOOP WILL TAKE PLACE OR NOT.
In this article, we discussed about the different type of LOOPS in VBA for Excel.