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