What Is a Loop in VBA?

Reading time: 3 minutes

In the article What Is a Variable in VBA?, we wrote the following code. It writes the value of the variable i in three different cells:

VBA code that writes the same line three times with the variable i, and the result 1, 2, 3 in cells A1 to A3

This code works. But to write in 100 cells, you would have to write the same line 100 times. This is far too much.

This is exactly the case where we create a loop: the program goes back several times to the same line of code, and only the value of the variable changes. In this way, at each iteration of the loop, the value of the variable is incremented, and the program writes in a different cell.

There are two types of loops:

  • For … Next: when you know the number of iterations to make
  • Do While … Loop: when the loop must stop as soon as a condition is met

1. How to write a For … Next loop

We want to make a loop on the first 10 cells of our worksheet. To do this, we start with the instruction For, followed by the variable i. This variable changes the row number. Its value starts at 1 and goes up to 10.

Inside the For … Next loop, we write the same instruction as before: we write the value of the variable in the cell of the matching row.

Sub WriteTenCells()
 For i = 1 To 10
 Cells(i, 1) = i
 Next i
End Sub
VBA For Next loop that writes the numbers 1 to 10 in column A

When VBA runs this loop, here is what happens:

  1. For i = 1 To 10: the variable i receives the start value, 1.
  2. Cells(i, 1) = i: VBA writes the value of i in row i. The first time, it writes 1 in A1.
  3. Next i: VBA adds 1 to i, and goes back to the line For. This is the line i = i + 1 of our first code, but VBA does it for you.
  4. As long as i is not greater than 10, the loop continues. After 10, it stops automatically, and VBA reads the line after Next.

To fill 1,000 cells, you only change one number: For i = 1 To 1000. The code stays the same size.

2. When you don’t know the number of rows: Do While

The Do While loop is mostly used to scan all the values of a column, and to stop when the last cell is reached. This is a very common task in VBA, because you cannot know in advance if you will have 50, 100 or 1,000 rows to process.

With a Do While loop, you must give the condition that stops the loop. Very often, this condition tests if the content of the cell is empty or not.

⚠️ Be careful: in a For … Next loop, the increment is automatic. In a Do While loop, it is not. You must write a line of code that increments the variable. If you forget it, the program stays on the first cell of your list forever, and the only way to stop it is to press Ctrl + Break. And today, many laptops no longer have a Break key. So be very careful with this line.

In our example, column A is already filled with the values 1 to 10, typed before we run the macro. This is not like the For … Next example, where the loop writes the values while it runs. Here, the loop only reads the values of column A, and writes the double of each value in column B.

You must also give the variable its start value before the Do While loop: this is the row of the first cell to read.

Sub DoubleValues()
 i = 1
 Do While Cells(i, 1) <> ""
 Cells(i, 2) = Cells(i, 1) * 2
 i = i + 1
 Loop
End Sub

In this code:

  • i = 1: the loop starts in row 1
  • Do While Cells(i, 1) <> “”: the loop continues as long as the cell in column A is not empty
  • i = i + 1: the line that increments the variable, to go to the next row
VBA Do While loop that reads the values of column A and writes their double in column B

3. The condition at the start or at the end

The condition can be written on the first line of the loop, or on the last line. Both are allowed:

Do While Cells(i, 1) <> ""
 ...
Loop

Do
 ...
Loop While Cells(i, 1) <> ""

It all depends on when you want to test the condition. With Do While … Loop, the condition is tested before each turn: if the first cell is empty, the loop never runs. With Do … Loop While, the condition is tested after each turn: the loop always runs at least once.

What’s next

Ask an AI to write a loop, and you will often see the line Dim i As Integer at the top of the code. It declares the type of the variable i. But Integer is an old type, and it can make your macro crash on a large worksheet. This is explained in VBA Variables: Why the AI Still Uses Integer and Single.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

What Is a Loop in VBA?

Reading time: 3 minutes

In the article What Is a Variable in VBA?, we wrote the following code. It writes the value of the variable i in three different cells:

VBA code that writes the same line three times with the variable i, and the result 1, 2, 3 in cells A1 to A3

This code works. But to write in 100 cells, you would have to write the same line 100 times. This is far too much.

This is exactly the case where we create a loop: the program goes back several times to the same line of code, and only the value of the variable changes. In this way, at each iteration of the loop, the value of the variable is incremented, and the program writes in a different cell.

There are two types of loops:

  • For … Next: when you know the number of iterations to make
  • Do While … Loop: when the loop must stop as soon as a condition is met

1. How to write a For … Next loop

We want to make a loop on the first 10 cells of our worksheet. To do this, we start with the instruction For, followed by the variable i. This variable changes the row number. Its value starts at 1 and goes up to 10.

Inside the For … Next loop, we write the same instruction as before: we write the value of the variable in the cell of the matching row.

Sub WriteTenCells()
 For i = 1 To 10
 Cells(i, 1) = i
 Next i
End Sub
VBA For Next loop that writes the numbers 1 to 10 in column A

When VBA runs this loop, here is what happens:

  1. For i = 1 To 10: the variable i receives the start value, 1.
  2. Cells(i, 1) = i: VBA writes the value of i in row i. The first time, it writes 1 in A1.
  3. Next i: VBA adds 1 to i, and goes back to the line For. This is the line i = i + 1 of our first code, but VBA does it for you.
  4. As long as i is not greater than 10, the loop continues. After 10, it stops automatically, and VBA reads the line after Next.

To fill 1,000 cells, you only change one number: For i = 1 To 1000. The code stays the same size.

2. When you don’t know the number of rows: Do While

The Do While loop is mostly used to scan all the values of a column, and to stop when the last cell is reached. This is a very common task in VBA, because you cannot know in advance if you will have 50, 100 or 1,000 rows to process.

With a Do While loop, you must give the condition that stops the loop. Very often, this condition tests if the content of the cell is empty or not.

⚠️ Be careful: in a For … Next loop, the increment is automatic. In a Do While loop, it is not. You must write a line of code that increments the variable. If you forget it, the program stays on the first cell of your list forever, and the only way to stop it is to press Ctrl + Break. And today, many laptops no longer have a Break key. So be very careful with this line.

In our example, column A is already filled with the values 1 to 10, typed before we run the macro. This is not like the For … Next example, where the loop writes the values while it runs. Here, the loop only reads the values of column A, and writes the double of each value in column B.

You must also give the variable its start value before the Do While loop: this is the row of the first cell to read.

Sub DoubleValues()
 i = 1
 Do While Cells(i, 1) <> ""
 Cells(i, 2) = Cells(i, 1) * 2
 i = i + 1
 Loop
End Sub

In this code:

  • i = 1: the loop starts in row 1
  • Do While Cells(i, 1) <> “”: the loop continues as long as the cell in column A is not empty
  • i = i + 1: the line that increments the variable, to go to the next row
VBA Do While loop that reads the values of column A and writes their double in column B

3. The condition at the start or at the end

The condition can be written on the first line of the loop, or on the last line. Both are allowed:

Do While Cells(i, 1) <> ""
 ...
Loop

Do
 ...
Loop While Cells(i, 1) <> ""

It all depends on when you want to test the condition. With Do While … Loop, the condition is tested before each turn: if the first cell is empty, the loop never runs. With Do … Loop While, the condition is tested after each turn: the loop always runs at least once.

What’s next

Ask an AI to write a loop, and you will often see the line Dim i As Integer at the top of the code. It declares the type of the variable i. But Integer is an old type, and it can make your macro crash on a large worksheet. This is explained in VBA Variables: Why the AI Still Uses Integer and Single.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *