Different types of looping in VBA (Visual Basic for Applications)

Different types of looping in VBA (Visual Basic for Applications)

Loop

A code block which allows code to execute repeatedly within based on certain conditions. VBA (Visual Basic for Applications) offers various types of loops which we will understand in this article.

For… Next syntax:

For <Start> To <Expression> Step <steps to be jumped>
	//Operation
Next i
  • Start: a value from where the loop starts
  • Expression: loop executes until given condition is satisfied
  • Step: value should be added or subtracted upon each iteration
  • Next: represents end of loop

Code example

Public Sub looping()
    Dim i As Integer
    
    'Fore loop
    For i = 1 To 10 Step 2
        Debug.Print i
    Next i
    
End Sub

Output

1
3
5
7
9

While… Wend syntax:

While <Expression>
	'Loop body
Wend
  • Expression: a logical express where while block will execute until expression is satisfied

Code example

Public Sub looping()
    Dim i As Integer
    
    'While loop
    i = 1
    While i <= 10
        Debug.Print i
        i = i + 1
    Wend

End Sub

Output

1
2
3
4
5
6
7
8
9
10

Do… While Loop syntax:

Do While <Expression>
	'Do while loop body
Loop

Code example

Public Sub looping()
    Dim i As Integer
    'do while
    i = 1
    Do While i <= 10
        Debug.Print
        i = i + i
    Loop    
End Sub

Output

1
2
3
4
5
6
7
8
9
10

Do… Until Loop syntax:

Do Until <Expression>
	'Loop body
Loop

Code example

Public Sub looping()
    Dim i As Integer
    
    'Do Until
    i = 1
    Do Until i >= 10
        Debug.Print i
        i = i + 1
    Loop
End Sub

Output

1
2
3
4
5
6
7
8
9
10

Do… Loop: execution of loop body happens once default as expression check happens in next iteration

Syntax:

Do
	'Loop body
Loop While <Expression>

Code example

Public Sub looping()
    Dim i As Integer
    
    Do
        Debug.Print i
        i = i + 1
    Loop While i <= 5
    
End Sub

Output

0
1
2
3
4
5

If you notice the output the condition is given upto 5 but we got 0 as first iteration since variable “i” is no initialized and set default to 0 hence in first iteration the initial value is printed then increment happened.

For… Each Next: this loop is very useful when you want to iterate over objects or collection in excel like you wish to iterate each sheet in a workbook or each cell in a range.

Syntax:

For Each <Object Variable> In <Object Collection>
	'Loop body
Next <Object Variable>

Example: in this example we will iterate each sheet form active workbook and print their name:

Public Sub looping()
    'For each
    Dim oSheet As Worksheet
    
    For Each oSheet In ActiveWorkbook.Worksheets
		'Print sheet name
        Debug.Print oSheet.Name
    Next oSheet
End Sub

Output

Sheet1
Sheet2
Sheet4
Sheet3
Sheet5
Sheet6

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.