How to Loop in Excel VBA

To get the best out of Excel VBA, you need to know essential programming techniques. One of the most important techniques is looping. In this blog, we are going to discuss loops in Excel VBA.

The Concept of the Loop

First, let’s see what a loop is. A loop is a way to repeatedly perform an action for a specific or unspecific number of times. You can give a specific number or condition that determines the times that a code runs in a loop. In VBA, there are three types of loops that are discussed in the rest of the blog.

VBA For Loop

To run a For loop, you must define a counter that determines how many times you want to run your code block.  The syntax of a For loop is as bellows:

For [counter] = start To end

[code block]

Next [counter]

The code block between For and Next will execute until the counter reaches the end. In this form, the counter takes the values of (start, start+1, …, end). You can also determine a step size for the counter. To do so, you must use the syntax below:

For [counter] = start to end step [step size]

And the rest is the same.

Example1: Single Loop

Look at the example below:

In this example, the value of “value” is set to zero before entering the loop. In the loop, its value changes with “i” each time, and it enters the cells (row i, column 1).

Example 2: Nested Loop

When you use a loop inside another one, you are using a nested loop. Look at the example below:

In this example, we created a For loop inside another For loop. When the code enters the first loop, i is set to “1,” and we enter the second loop. At this point, j changes from 1 to 3, and when the second loop is over, i takes the value of 2. Again the second loop runs, and this repeats until the value of i is 10, and the value of j is 3.

For Each Loop

Sometimes, you need to run a loop through a set of objects instead of a set of values. These objects can be open Workbooks, Worksheets in a Workbook, cells in a range of selected cells, arrays, etc. this can be done using a For Each loop. Here’s the syntax to use this loop:

For Each [element] In [collection][code block]

Next [element]

“For Each loop” goes through each element in the collection and performs the code block. Look at the example below:

In this example, the “For Each” loop goes through a collection of cells in a range and highlights the cells with single-digit values. We used an IF statement to identify single-digit numbers.

VBA Do While Loop

A Do While loop runs a code block while a specific condition is still valid. The syntax to use a Do While loop is as below:

Do While [condition][code block]

Loop

You can also put the condition at the end of the loop. To do so, use the syntax below:

Do

[code block]

Loop While [condition]

The code block keeps running while the condition is true. Once the condition is not valid, the loop ends.

Example: Look at the example below:

The condition for this loop is (i<=10). In the loop, “i” takes the values of (1, 2, …), and when “i” equals 11, the loop ends.

VBA Do Until Loop

Do Until loop runs a code block until a specific condition is met. So it’s much similar to the Do While loop. The syntax to use a Do Until loop is as below:

Do Until [condition][code block]

Loop

You can also put the condition at the end of the loop by using the following syntax:

Do

[code clock]

Loop Until [condition]

The code block keeps running until the condition is met.

Example: Look at the example below:

As you can see, the “Do Until” loop is very similar to the “Do While” loop. In this example, “i” takes the values of (1, 2, …), and when it takes 11, the loop ends.

Note: The difference between these two is that the “Do Until” checks the condition after running the code, but “Do While” checks the condition first and then runs the code. Also, “Do Until” runs a loop as long as the condition is false, but “Do While” runs a loop as long as the condition is true.

How to Exit a Loop

Sometimes while running a loop, you want to exit the loop if a specific condition is met. For this purpose, you must use the right command for every loop. You must use the exit command after the IF statement. In the following table, you can see the exit command for every loop.

Loop	Command
For	Exit For
For Each	Exit For
Do While	Exit Do
Do Until	Exit Do

Example: In this example, we want to know how many consecutive numbers starting from one we can add and still have a two-digit result.

To do so, we used a “for loop” and an “IF” statement to find out when the result gets 100 or more. When the summation result reaches 100, the code exits the loop and highlights the last number, which is 13. So we can add 13 consecutive numbers starting from one and still have a two-digit value.

Note: We could also do this example using Do While and Do Until loops without exit command.

 

You can connect with us and ask our experts for your inquiries and get more Excel Support Service.

Reduce cost, accelerate tasks, and improve quality with Excel Consulting Services.