LOOPS IN VBA

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 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 FOR-NEXT loop.

The FOR-NEXT loop works and have a controlled number of iteration mentioned by a variable.

Let us see the format first.

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.

variable is any variable which we want to handle the iterations. It can be any variable which is predefined or it can be defined directly here also.

starting_value is the starting value given to the variable

TO is the keyword which comes after the initial value

final_value is the final value , the variable will take, upto which the iterations will occur.

INSTRUCTIONS/STATEMENTS are the coding portions which we want to do within the loop.

EXIT FOR is the keyword for instantly break the loop if any condition is there which 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. variable is same variable in first step which is increasing.

SIMPLE EXAMPLE FOR-NEXT LOOP

Let us create a very simple FOR NEXT loop which will sum the digits upto 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 the simple understanding. Here is a brief description.

The example starts by defining i as 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 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

Message box will show the result as 78. Look below at the running program.

PUTTIN 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 its 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 the simple understanding. Here is a brief description.

The example is same as previous one. The normal explanation can be read there.

The difference here is the use of 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 increment of 2, it’ll give the sum of even numbers easily.

The running of 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 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 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 the simple understanding. Here is a brief description.

The two variables i and temp (for holding the value) are declared in 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 NEXT statement.

The result comes out to be 12 as after that the code didn’t let the loop to 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 work 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 occur 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 which is used to start the loop. It is put at the initiation of the loop.

WHILE is a keyword which controls the condition part of the loop. After the while we need to put the condition, which should be satisfied to keep the loop running.

INSTRUCTIONS/STATEMENTS are the coding statements which 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 of the program.

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 condition is met.

SIMPLE EXAMPLE FOR-NEXT 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 the simple understanding. Here is a brief description.

The example starts with 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 increment of i so that it goes and check the next character.

After the loop is completed we take the output using the msgbox and giving the expression. We have deducted 1 from the value of i because the i which is being test 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 which lie inside the loop.

It is just similar to DO-WHILE LOOP or to be specific 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, Do While loop would stop when the condition becomes false but 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 which is used to start the loop. It is put at the initiation of the loop.

UNTIL is a keyword which 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 which 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 of the program.

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 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 Exmple

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 a has been declared. It is the string , we’ll be counting the characters of.

Two more variables i and j has been taken. * We haven’t declared them so VBA will take them as Variants which can be used for anything.

The two variables are needed, one for the characters and one for the counting the characters.

The loop starts, the condition is that the loop should be more than 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 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.

LEARN

EXCEL
HTML
JAVASCRIPT
MORE TO COME…

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]