Loops in VBA
There may be various situations when you want to execute a block of code several number of times. In fact, all statement executed sequentially first, followed by the second, and so on.
Every programming language equipped with various control structures that allow for more complicated execution.
A loop statement lets us to execute a statement or block of statements multiple times. Given below are loops statement which are used in VBA programming.
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
Example:
Add a button and add the below function.
Sub For_Loop()
Dim num As Integer
num = 10
For i = 0 To num Step 2
MsgBox "The value is i is : " & i
Next
End Sub
When the above code is compiled and executed, it produces below 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
A For Each loop is used when we want to execute a statement or a block 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 block. Thus, the step counter not 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:
Sub For_Each_Loop()
'Books is an array
Books = Array("English", "Hindi", "Mrathi")
Dim bookname As Variant
'iterating using For each loop.
For Each Item In Books
bookname = bookname & Item & Chr(10)
Next
MsgBox bookname
End Sub
When the above code is executed, it prints name of each book in each line.
English
Hindi
Marathi
In a While…Wend loop, if the condition is True, all statements are executed until Wend keyword is met.
If the condition is false, the loop is left 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
Example :
Sub while_Wend_Loop()
Dim Counter : Counter = 10
While Counter < 12 ' 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 12.
End Sub
When the above code is executed, it generates the following text in a message box.
The Current Value of the Counter is : 11
The Current Value of the Counter is : 12
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
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.
Sub Do_While_Loop()
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.
Sub Do_While_Loop()
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
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
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.
Sub Do_Until()
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
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 at least 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 change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed.
It's possible to exit a For loop early by using the following instruction :
Exit For 'Exit a For loop
Here is an example of this :
Sub for_loop()
Dim max_loops As Integer
max_loops = Range("A1") 'In A1 : we have defined a limit to the number of repetitions
For i = 1 To 7 'Number of loops expected : 7
If i > max_loops Then 'If A1 is empty or contains a number < 7, decrease the number of loops
Exit For 'If the condition is true, we exit the For loop
End If
MsgBox i
Next
End Sub
The other Exit instructions :
Exit Do 'Exit a Do Loop
Exit Sub 'Exit a procedure
Exit Function 'Exit a function
Every programming language equipped with various control structures that allow for more complicated execution.
A loop statement lets us to execute a statement or block of statements multiple times. Given below are loops statement which are used in VBA programming.
• For Loop:
A for loop is a reiteration control structure that allows a programmer to efficiently write a loop that needs to execute a specific number of intervals.
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
Example:
Add a button and add the below function.
Sub For_Loop()
Dim num As Integer
num = 10
For i = 0 To num Step 2
MsgBox "The value is i is : " & i
Next
End Sub
When the above code is compiled and executed, it produces below 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:
A For Each loop is used when we want to execute a statement or a block 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 block. Thus, the step counter not 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:
Sub For_Each_Loop()
'Books is an array
Books = Array("English", "Hindi", "Mrathi")
Dim bookname As Variant
'iterating using For each loop.
For Each Item In Books
bookname = bookname & Item & Chr(10)
Next
MsgBox bookname
End Sub
When the above code is executed, it prints name of each book in each line.
English
Hindi
Marathi
• While…Wend Loop:
In a While…Wend loop, if the condition is True, all statements are executed until Wend keyword is met.
If the condition is false, the loop is left 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
Example :
Sub while_Wend_Loop()
Dim Counter : Counter = 10
While Counter < 12 ' 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 12.
End Sub
When the above code is executed, it generates the following text in a message box.
The Current Value of the Counter is : 11
The Current Value of the Counter is : 12
• Do…While:
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
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.
Sub Do_While_Loop()
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.
Sub Do_While_Loop()
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:
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
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.
Sub Do_Until()
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
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 at least 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
Early exit from a loop:
Loop control statements change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed.
It's possible to exit a For loop early by using the following instruction :
Exit For 'Exit a For loop
Here is an example of this :
Sub for_loop()
Dim max_loops As Integer
max_loops = Range("A1") 'In A1 : we have defined a limit to the number of repetitions
For i = 1 To 7 'Number of loops expected : 7
If i > max_loops Then 'If A1 is empty or contains a number < 7, decrease the number of loops
Exit For 'If the condition is true, we exit the For loop
End If
MsgBox i
Next
End Sub
The other Exit instructions :
Exit Do 'Exit a Do Loop
Exit Sub 'Exit a procedure
Exit Function 'Exit a function