VBA Loops

This VBA Loops tutorial shows you how to use the different types of loops in VBA such as For Next, For Each, Do While, and Do Until, with code examples.

Loops are an essential feature of programming languages since they enable us to repeat certain actions by using fewer lines of code.

In this tutorial, we will learn the different loop types available in VBA, their advantages, and flowcharts, as well as some examples.

VBA Loops

What are Loops

Loops are used to repeat a set of statements as many times as necessary until a given condition is met. A loop statement allows you to repeatedly execute a statement or group of statements.

First, you must understand how to use loops to get the most out of Excel and VBA. Following is the general form of a loop statement in VBA.

Types of VBA Loops

VBA provides the following types of loops to handle looping requirements. Below is the analysis of the different types of each loop:

For Loop

It executed a sequence of statements and compressed the code that controls the loop variable.

For loop, a variable runs through a sequence of values within a specified range on each cycle. Then, for each value in VBA, the code is executed inside the loop.

For counter = start To end [Step count]
[statement 1]
[statement 2]
….
[statement n]
[Exit For]
[statement 11]
[statement 22]
….
[statement n]
Next

OR

For counter = start To end
[statement 1]
[statement 2]
….
[statement n]
[Exit For]
[statement 11]
[statement 22]
….
[statement n]
Next

The For loop above sets the variable i to the numbers 1, 2, 3,…, 10 and then runs the VBA code inside the loop for each of these numbers. The loop thus adds each element of the Integer i to the variable.

For Each Loop

For Each Loop is used to loop through a collection of items or arrays, repeating the same set of statements for each item in the collection or array. Cells, worksheets, pivot tables, etc., can be grouped together to make a collection.

For example, let’s list down every worksheet in the current excel workbook.

For Each element In Group
[statement 1]
[statement 2]
….
[statement n]
[Exit For]
[statement 11]
[statement 22]
Next

Do While Loop

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

The do…While loop statement

Do While condition
[statement 1]
[statement 2]

[statement n]
[Exit Do]
[statement 1]
[statement 2]

[statement n]
Loop

Do Until Loop

This loop is the opposite of the Do While loop, in Do Until, the rules for ending the loop are specified. Therefore, if the condition is FALSE, the statement inside the loop will be executed, but if the condition is TRUE, the loop will be closed.

The Do…Until loop statement

Do
[statement 1]
[statement 2]

[statement n]
[Exit Do]
[statement 1]
[statement 2]

[statement n]
Loop Until Condition

Loop Control Statements

Loop control statements change execution from its normal sequence so that all remaining statements in the loop are not executed when execution leaves a scope. VBA supports the following control statements:

1. Exit for Statement

This is useful when you want to exit the For Loop after satisfying a specified set of conditions. Execution continues immediately after the Next statement with the statement.

2. Exit do Statement

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


Notice: Trying to access array offset on value of type int in /home2/bestrev2/public_html/xlautomation/wp-includes/class-wp-list-util.php on line 170

Notice: Trying to access array offset on value of type int in /home2/bestrev2/public_html/xlautomation/wp-includes/class-wp-list-util.php on line 170

Notice: Trying to access array offset on value of type int in /home2/bestrev2/public_html/xlautomation/wp-includes/class-wp-list-util.php on line 170