VBA - Loops


There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on.
Programming languages provide various control structures that allow for more complicated execution paths.
A loop statement allows us to execute a statement or group of statements multiple times and following is the general from of a loop statement in VBA.
Loop Architecture
VBA provides the following types of loops to handle looping requirements. Click the following links to check their detail.
Loop TypeDescription
for loop
A for loop is a repetition control structure that allows a developer to efficiently write a loop that needs to execute a specific number of times.

Syntax:

The syntax of a for loop in VBA is:
For counter = start To end [Step stepcount]
  [statement 1]
  [statement 2]
  ....
  [statement n]
  [Exit For]
  [statement 11]
  [statement 22]
  ....
  [statement n]
Next

Flow Diagram:

VBScript For Loops
Here is the flow of control in a For Loop:
  1. The For step is executed first. This step allows you to initialize any loop control variables and increment the step counter variable.
  2. Secondly, the condition is evaluated. If it is true, the body of the loop is executed. If it is false, the body of the loop does not execute and flow of control jumps to the next statement just after the For Loop.
  3. After the body of the for loop executes, the flow of control jumps to theNext statement. This statement allows you to update any loop control variables. It is updated based on the step counter value.
  4. The condition is now evaluated again. If it is true, the loop executes and the process repeats itself (body of loop, then increment step, and then again condition). After the condition becomes false, the For Loop terminates.

Example:

Add a button and add the below function.
Private Sub Constant_demo_Click()
   Dim a As Integer
   a = 10
   For i = 0 To a Step 2
     MsgBox "The value is i is : " & i
   Next
End Sub
When the above code is compiled and executed, it produces the following result:
The value is i is : 0

The value is i is : 2

The value is i is : 4

The value is i is : 6

The value is i is : 8

The value is i is : 10
for ..each loop
A For Each loop is used when we want to execute a statement or a group of statements for each element in an array or collection.
A For Each loop is similar to For Loop; however, the loop is executed for each element in an array or group. Hence, the step counter won't exist in this type of loop and it is mostly used with arrays or used in context of File system objects in order to operate recursively.

Syntax:

The syntax of a For Each loop in VBA is:
For Each element In Group
  [statement 1]
  [statement 2]
  ....
  [statement n]
  [Exit For]
  [statement 11]
  [statement 22]
Next

Example:

Private Sub Constant_demo_Click()  
 'fruits is an array
 fruits = Array("apple", "orange", "cherries")
 Dim fruitnames As Variant
 

 'iterating using For each loop.
 For Each Item In fruits
    fruitnames = fruitnames & Item & Chr(10)
 Next

 MsgBox fruitnames
End Sub
When the above code is executed, it prints all the fruitnames with one item in each line.
apple
orange
cherries
while..wend loop
In a While..Wend loop, if the condition is True, all statements are executed untilWend keyword is encountered.
If the condition is false, the loop is exited and the control jumps to very next statement after Wend keyword.

Syntax:

The syntax of a While..Wend loop in VBA is:
While condition(s)
   [statements 1]
   [statements 2]
   ...
   [statements n]
Wend

Flow Diagram:

While Loop Architecture

Example :

Private Sub Constant_demo_Click()
  Dim Counter :  Counter = 10   
  While Counter < 15     ' Test value of Counter.
    Counter = Counter + 1   ' Increment Counter.
    msgbox "The Current Value of the Counter is : " & Counter
  Wend   ' While loop exits if Counter Value becomes 15.
End Sub
When the above code is executed, it prints the following in a message box.
The Current Value of the Counter is : 11 

The Current Value of the Counter is : 12 

The Current Value of the Counter is : 13 

The Current Value of the Counter is : 14 

The Current Value of the Counter is : 15
do..while loops
A Do..While loop is used when we want to repeat a set of statements as long as the condition is true. The Condition may be checked at the beginning of the loop or at the end of the loop.

Syntax:

The syntax of a Do..While loop in VBA is:
Do While condition
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop

Flow Diagram

VBA Do..While statement

Example :

The below example uses Do..while loop to check the condition at the beginning of the loop. The statements inside the loop are executed only if the condition becomes True.
Private Sub Constant_demo_Click()
  Do While i < 5
    i = i + 1
    msgbox "The value of i is : " & i
  Loop
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 1

The value of i is : 2

The value of i is : 3

The value of i is : 4

The value of i is : 5

Alternate Syntax :

There is also an alternate Syntax for Do..while loop which checks the condition at the end of the loop. The Major difference between these two syntax is explained below with an example.
Do 
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop While condition

Example :

The below example uses Do..while loop to check the condition at the end of the loop. The Statements inside the loop are executed atleast once even if the condition is False.
Private Sub Constant_demo_Click() 
 i = 10
 Do
  i = i + 1
  MsgBox "The value of i is : " & i
 Loop While i < 3 'Condition is false.Hence loop is executed once.
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 11
do..until loops
A Do..Until loop is used when we want to repeat a set of statements as long as the condition is false. The Condition may be checked at the beginning of the loop or at the end of loop.

Syntax:

The syntax of a Do..Until loop in VBA is:
Do Until condition
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop

Flow Diagram

vba Do..Until statement

Example :

The below example uses Do..Until loop to check the condition at the beginning of the loop. The Statements inside the loop are executed only if the condition is false. It exits out of the loop when the condition becomes true.
Private Sub Constant_demo_Click() 
  i=10
  Do Until i>15  'Condition is False.Hence loop will be executed
    i = i + 1
    msgbox ("The value of i is : " & i)
  Loop 
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 11

The value of i is : 12

The value of i is : 13

The value of i is : 14

The value of i is : 15

The value of i is : 16

Alternate Syntax :

There is also an alternate Syntax for Do..Until loop which checks the condition at the end of the loop. The Major difference between these two syntax is explained below with an example.
Do 
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop Until condition

Flow Diagram

VBScript Do..Until statement

Example :

The below example uses Do..Until loop to check the condition at the end of the loop. The Statements inside the loop are executed atleast once even if the condition is True.
Private Sub Constant_demo_Click()  
  i=10
  Do 
    i = i + 1
    msgbox "The value of i is : " & i
  Loop Until i<15 'Condition is True.Hence loop is executed once.
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 11

Loop Control Statements:

Loop control statements change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed.
VBA supports the following control statements. Click the following links to check their detail.
Control StatementDescription
Exit For statement
A Exit For Statement is used when we want to Exit the For Loop based on certain criteria. When Exit For is executed, the control jumps to next statement immediately after the For Loop.

Syntax:

The syntax for Exit For Statement in VBA is:
 Exit For

Flow Diagram

VBA Exit For statement

Example :

The below example uses Exit For. If the value of the Counter reaches 4, the For Loop is Exited and control jumps to the next statement immediately after the For Loop.
Private Sub Constant_demo_Click()
  Dim a As Integer
  a = 10
  For i = 0 To a Step 2 'i is the counter variable and it is incremented by 2
    MsgBox ("The value is i is : " & i)
    If i = 4 Then
      i = i * 10 'This is executed only if i=4
      MsgBox ("The value is i is : " & i)
      Exit For 'Exited when i=4
    End If
  Next
End Sub
When the above code is executed, it prints the following output in a message Box.
The value is i is : 0

The value is i is : 2

The value is i is : 4

The value is i is : 40
Exit Do statement
An Exit Do Statement is used when we want to Exit the Do Loops based on certain criteria. It can be used within both Do..While and Do..Until Loops.
When Exit Do is executed, the control jumps to next statement immediately after the Do Loop.

Syntax:

The syntax for Exit Do Statement in VBA is:
 Exit Do

Example :

The below example uses Exit Do. If the value of the Counter reaches 10, the Do Loop is Exited and control jumps to the next statement immediately after the For Loop.
Private Sub Constant_demo_Click()
  
i = 0
Do While i <= 100
  If i > 10 Then
    Exit Do   ' Loop Exits if i>10
  End If
  MsgBox ("The Value of i is : " & i)
  i = i + 2
Loop

End Sub
When the above code is executed, it prints the following output in a message box.
The Value of i is : 0

The Value of i is : 2

The Value of i is : 4

The Value of i is : 6

The Value of i is : 8

The Value of i is : 10







← Previous Page
Next Page →
Subscribe to: Posts (Atom)