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.
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:
See More: VBA for 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] NextOR 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:
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.
Exit do Statement
Exit Do is used when you wish to come out of the Do Loop after meeting specific criteria.
