How to Loop in Excel VBA

5 min read|Last Updated: December 24th, 2023|Categories: excel|
table of content

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:

How to use a For loop in Excel VBA

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:

Nested For loop in Excel VBA

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:

For Each loop in Excel VBA

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]