In this article, you will be learning about loops in VBA and how to use ith with-in your Excel workbook with its example.
Loops in Excel VBA
VBA excel supports the following loops
- For each
- For next
- Do while loop
- Nested loops
Let’s see them in detail.
For Each
It provides an alternative approach to traverse the array or collection.
Syntax
Following is the syntax of a For Each loop in VBA.
For Each element In Group
[statement 1]
[statement 2]
….
[statement n]
[Exit For]
[statement 11]
[statement 22]
Next
Sample program using for each loop in VBA
Input
Private Sub Constant_demo_Click()
‘vegetable’ is an array.
vegetable = Array(“onion”, “tomato”, “potato”)
Dim vegetablenames As Variant
‘iterating using For each loop.
For Each Item In vegetable
vegetablenames = vegetablenames & Item & Chr(10)
Next
MsgBox vegetablenames
End Sub
Output
![Loops in Excel VBA with Examples](https://developerpublish.com/wp-content/uploads/2021/04/Screenshot-119-1024x375.png)
For Next
‘For Next’ Loop works by running the loop the specified number of times.
Syntax for For….next loop
For counter = start To end [ Step step ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]
Sample program using for next loop in VBA.
Input
Sub AddNumbers()
Dim Total As Integer
Dim Count As Integer
Total = 0
For Count = 111 To 120
Total = Total + Count
Next Count
MsgBox Total
End Sub
Output
Do While
A Do…While loop is used to repeat a set of statements until a certain condition becomes true. The condition may be checked at the beginning of the loop or at the end of the loop.
Syntax
Do While condition
[statement 1]
[statement 2]
…
[statement n]
[Exit Do]
[statement 1]
[statement 2]
…
[statement n]
Loop
Sample program using do while loop in VBA
Input
Private Sub Constant_demo_Click()
Do While i < 3
i = i + 1
msgbox “The value of i is : ” & i
Loop
End Sub
Output
![Loops in Excel VBA with Examples](https://developerpublish.com/wp-content/uploads/2021/04/Screenshot-123.png)
Nested loops
In nested loops, two or more types of loops are present inside another loop.
Sample program using nested loops in VBA
Input
Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 111 To 120
For j = 111 To 115
Next
Next
MsgBox “The value of i is : ” & i
MsgBox “The value of i is : ” & j
End Sub
Output
![Loops in Excel VBA with Examples](https://developerpublish.com/wp-content/uploads/2021/04/Screenshot-126-1.png)
![Loops in Excel VBA with Examples](https://developerpublish.com/wp-content/uploads/2021/04/Screenshot-127.png)