Excel VBA Loops – For Next, For Each, Do While, Do Until

This VBA Loos tutorial explains the different types of loops in VBA like For Next, For Each, Do While, Do Until with code examples:

Loops are an important concept in a programming language. It allows us to perform certain actions repeatedly just by using fewer lines of code.

In this tutorial, we will understand the different types of loops available in VBA, their advantages, and flowcharts. We will also learn how to exit from a loop whenever necessary.

=> Check Here For ALL VBA Tutorials

Excel VBA Loops

What are Loops

Loops help us in repeating a set of statements as many times as needed until a given criterion is reached.

Suppose you have a requirement to print data 10 times, instead of writing a print statement 10 times you can add a loop and write just one print statement.

Advantages of Loops

• It reduces the length of the code with fewer steps.
• Increases the readability of procedure hence easier to maintain.
• Makes it easier to debug and modify the procedure.

Types of VBA Loops

For Loop

• For…Next Statement
• For Each…Next Statement

While Loop

• While..Wend Statement

Do While Loop

• Do While..Loop Statement
• Do..Loop While Statement

Do Until Loop

• Do Until..Loop Statement
• Do..Loop Until Statement

Adding VBA code

Before we proceed, let’s make ourselves clear on where to add the procedure in Excel.

1. Open the Excel workbook.
2. Go to the Developer tab. If you don’t have the Developer tab. Refer here
3. From the ActiveX control, draw the command button. Right-click -> View code. This will open a new module with the start and end Sub.
4. Copy and paste the code in between them.

Executing the code

Go to the Excel worksheet where you have placed your command button and disable the Design mode from the Developer tab and click on the command button.

#1) For Next Loops

These Loops are implemented using For..Next statements. It allows us to execute a set of statements a specified number of times.

Syntax

For counter = start To end [ Step stepcount ]
[ statements ]
Next [ counter ]

Counter: This field is required. It accepts a numeric value.
Start: This field is also required. It is the value from which the loop will start.
End: Required field. This is the final value of the counter.
Step: This is optional. The counter value will increase based on the number provided in the step. It defaults to 1 if the value is not entered.
Statements: These are the actions that are to be performed multiple times based on the counter value

The flow diagram below shows the way the loop works.

Let’s see how to work with a FOR loop – we will create a single loop and double loop.

Example:

Public Sub forloop1()
Dim a, i As Integer
a = 5
For i = 0 To a
Debug.print "The value of i is : " & i
Next
End Sub

Since we have not mentioned the step counter, i value is incremented by 1 by default. Hence the value of i is 0,1,2,3,4,5 as shown below

Private Sub forloop2()
Dim x, i As Integer
x = 5
For i = 0 To x Step 2
Debug.print "The value of i is : " & i
Next
End Sub

In the above example we have mentioned the step counter as 2, so every time the loop is incremented by 2. Hence the value of I is 0,2,4 as shown below.

Let’s explore for loop in detail by writing 15 tables using the same.

Private Sub CommandButton1_Click()
Dim number, result, i As Integer
number = 15
For i = 1 To 10
result = number * i
Table = number & "*" & i & "=" & result
Debug.Print Table
Range("A" & i).Value = Table
Next
End Sub

In the above example, we have used for loop to execute a set of statements from 1 to 10 and increment each time by 1. The results are placed in the active worksheet cells A1 to A10 as shown below.

So far we have only used single for loop. Now let’s try to use 2 For..Next loops in one procedure.

Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
For row = 1 To 10
For col = 1 To 10
result = row * col
Table = row & " * " & col & " = " & result
Cells(row + 1, col + 1).Value = Table
Next col
Next row
End Sub

In the above code, we have used 2 for loops.

The execution enters the first loop, if the condition is true, then the control will go to the 2^nd loop and if the condition is true, it will execute the statement and control again returns to the 2^nd loop until the condition is false.

Once the 2^nd loop condition is false the control will go back to the 1^st loop. The same procedure will continue until the condition in 1^st loop is false.

The above procedure will generate tables for 1 to 10 and place the results in the active worksheet as shown below.

#2) For Each

For Each Loop is used to loop through a collection of items or arrays. This means it repeats the set of statements for each element in the collection or an array. Collections can be cells, worksheets, pivot tables, etc.

Syntax:

For Each element In group
[ statements ]
Next [ element ]

Element – This is a required field. It is used to iterate the elements in the collection or array.
Group – This is the name of the collection or array.
Statements – One or more actions to be executed on the item in the group.

Let’s take a simple worksheet with a credit card number as shown below.

For better readability, your senior asks you to add a hyphen (-) instead of space in between the numbers. This can be easily achieved with the help of For Each Loop as shown below.

Private Sub CommandButton1_Click()
Dim CellData As Variant
For Each CellData In Range("A2:A10")
CellData.Value = Replace(CellData.Value, " ", "-")
Next CellData
End Sub

In the above example, Range(“A2:A10”) is a collection of objects. CellData is the element. This element variable stores individual cells. The type of the element variable in a for each loop has to be a variant or object. To go to the next cell in the loop, use Next CellData. The replace function is used to replace all blank spaces with a hyphen.

The result is shown below.

#3) While Loops

To create a while loop we use While..Wend Statements.

While loops are used when you are unsure about the number of times the loop body has to be repeated.

Syntax

While condition
[Statements]
Wend

Condition – This is evaluated every time the control reaches the loop. If the condition value is TRUE, the body of the loop is executed. If the condition is FALSE then the control goes out of the loop.

Statements – The set of actions to be executed every time a condition is evaluated as True. It should also have a statement to increment the counter, else you will end up in an infinite loop.

Example:

Private Sub WhileLoop1()
Dim count As Integer
count = 1
While count < 5
Debug.Print "The Value of the count is : " & count
count = count + 1
Wend
End Sub

In the above example, we have defined the count as 1, and the while condition checks if the count is less than 5 and each time the condition is true it enters the While body and prints the count value and also increases the count by 1

Note: If you don’t increment the count value then it will lead to an infinite loop.

Let’s try to build 13 tables using a while loop.

Private Sub CommandButton1_Click()
Dim number, result, i As Integer
number = 13
i = 1
While (i <= 10)
result = number * i
Table = number & "*" & i & "=" & result
Debug.Print Table
Range("B" & i).Value = Table
i = i + 1
Wend
End Sub

In the above code, the loop body is executed until the i value is more than 10.

Results are as shown below

#4) Do While

This loop checks for a condition and executes the loop body while that condition is True.

There are two types of syntax.

Syntax 1

Do While condition
[Statements]
Loop

Example:

Let’s build a procedure to find even and odd numbers in the given range.

Private Sub CommandButton4_Click()
number = 10
i = 1
Do While i <= number
If (i Mod 2 = 0) Then
Debug.Print i & " number is even"
Else
Debug.Print i & " number is odd"
End If
i = i + 1
Loop
End Sub

In the above example, I have mentioned the number as 10 in the procedure itself, you can add an input box and enter the number at runtime as well.

Results are shown below

Syntax 2

Do
[Statements]
Loop While condition

Example:

Private Sub dowhileexample()
Dim i As Integer
i = 1
Do
i = i + 1
Debug.Print "The value of i is : " & i
Loop While i < 3
End Sub

In the above example, the condition is checked at the end of the loop, hence the value of i is 3 and is printed even though the condition is given for i values less than 3.

Result is shown below

Private Sub dowhileexample2()
Dim i As Integer
i = 2
Do
i = i + 1
Debug.Print "The value of i is : " & i
Loop While i < 3
End Sub

Result is shown below.

The difference between the 2 syntaxes is that in the first, the Do While condition is evaluated first before any code is executed, but in the second, the control goes inside the loop and it is executed and then the condition is checked.

This means that the statements inside the loop will run at least once in the second case even if the condition is False.

#5) Do Until

This loop is the opposite of the Do-While loop. In Do Until we define the criteria to end the loop. So if the condition is FALSE the statement inside the loop will be executed but if the condition is TRUE, the loop is terminated.

It has 2 Syntaxes like Do While.

Syntax 1

Do Until condition
[Statements]
Loop

Condition is checked at the beginning of the loop. Statements are executed if the condition evaluates to False, else the loop will be terminated.

Example

Private Sub dountil1()
Dim i As Integer
i = 1
Do Until i > 1 'Condition False. Code inside loop is executed
i = i + 1
Debug.Print "The value of i is : " & i
Loop
End Sub

In the above example, i is not greater than 1 so the condition is false, hence the control goes inside the loop and increments the i value.

Result is shown below

Syntax 2

In this, the condition is evaluated at the end. So the statements are executed at least once. If the condition is false then the loop is executed again, else the loop terminates.

Do
[Statements]
Loop Until condition

Example

Private Sub dountil2()
Dim i As Integer
i = 100
Do
i = i + 1
Debug.Print "The value of i is : " & i
Loop Until i < 102 'Condition True. loop is terminated after executing once.
End Sub

In the above example, the condition is checked at the end. So the code will be executed at least once, no what, when the condition is true or false.

Here the control goes inside the loop and increments the i value and prints it. But the condition evaluates to true so the control comes out of the loop block.

Result is shown below

#6) Exit For

This is used when you wish to come out of the For Loop after meeting specific criteria. Execution continues with the statement immediately after the Next statement.

Exit For is used only inside a For…Next or For Each…Next loop.

If you have multiple for loops inside a procedure then Exit For will quit the innermost loop and the control goes to the loop one level above that For.

You can place any number of Exit For statements anywhere you want in a procedure.

Example

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 0 To 10 Step 2
If i = 3 Then
MsgBox " Value of I is " & i
Exit For
End If
If i = 8 Then
MsgBox " Value of I is " & i
Exit For
End If
Next i
MsgBox "Exit For executed after value of i became " & i
End Sub

First, the Exit For statement can only be reached when the value of i is 3 but since we are incrementing by Step 2 , control won’t go inside the first IF statement.

When the value of i becomes 8 the control will go inside the IF statement prints the value of i and executes Exit For which makes the control to come out of the For loop and goes to the line after Next which is MsgBox “Exit For executed after ” & i &”th iteration”.

Hence the result is as shown below

#7) Exit Do

Exit Do is used when you wish to come out of the Do Loop after meeting specific criteria.

It is used inside a Do…Loop statement. Exit Do transfers control to the statement immediately after the loop. If you have nested Do…Loop then Exit Do transfers control to loop which is one level above that loop.

Syntax:

Do [{ While | Until } condition ]
[ statements ]
[ Exit Do ]
[ statements ]
Loop

Or

Do
[ statements ]
[ Exit Do ]
[ statements ]
Loop [{ While | Until } condition ]

Example

Private Sub CommandButton1_Click()
i = 100
Do While i >= 2
i = i / 2
If i < 25 Then
MsgBox " Value of i is " & i
Exit Do
End If
If i < 55 Then
MsgBox ("The Value of i is : " & i)
Exit Do
End If
i = i + 1
Loop
MsgBox "Exit Do is executed when value of i is " & i
End Sub

In the above example, i value is initialized as 100, and the loop is continued till i>=2 .

It is divided by 2. If i value is less than 25 then the first IF statement is executed else if i value is less than 55 then 2nd IF statement is executed and both IF has Exit Do which will make control go out of the Do loop.

Result is as shown below

Frequently Asked Questions

Q #1) How many kinds of loops are in VBA?

Answer: There are 4 board types of loops in VBA as given below

#1) For Loop

• For … Next Statements
• For Each … Next Statements

#2) While Loop

• While… Wend Statement

#3) Do While Loop

• Do While…Loop Statements
• Do…Loop While Statements

#4) Do Until Loop

• Do Until…Loop Statements
• Do…Loop Until Statements

Q #2) How do you do for each loop in VBA?

Answer: For Each Loop is used to loop through a collection of items or arrays. Collections can be cells, worksheets, pivot tables, etc.

Syntax:

For Each element In group
[ statements ]
Next [ element ]

Q #3) Give an example of Do while loop Excel VBA.

Answer:  Do While loop allows us to repeat a set of actions or statements if the condition is TRUE. VBA lets you decide whether to check the condition at the beginning of the loop or at the end.

Example

Public Sub Dowhile1()
Dim i As Integer
i=0
Do While i < 5
i = i + 1
Debug.Print "The value of i is : " & i
Loop
End Sub

In the above example, until the value of i becomes greater or equal to 5, the loop will be executed and the i value is incremented.

Q #4) How do you stop a for loop?

Answer: You can use the Exit For Statement to stop a For Loop. When you use Exit For, the control quits the loop and execution continues with the statement immediately after the Next statement.

Conclusion

In this VBA Loops tutorial, we have learned about Looping inside the procedure, which will help us to execute a set of statements multiple times.

We have seen various types of loops available in VBA with their syntax and examples.

=> Read Through ALL the VBA Tutorials Here