VBA Loops

A loop statement allows the execution of a statement or group of statements multiple times. There are three types of loop statement in VBA.

  • ‘For’ loop – Useful when it is known in advance the number of times the statements within the loop need to be executed.
  • ‘Do While’ loop – Repeats a statement or group of statements while an expression evaluates to true. There is a condition to get into the loop, so if the condition evaluates to true at the beginning then the statements inside the loop will never execute.
  • ‘Do Until’ loop – This is similar to the ‘Do While’ loop except that instead of executing whilst the condition is true, it executes whilst the condition is false.

‘For’ Loop

The ‘For’ loop takes the following form.

For counter = start To end
   ' Statement(s) to execute.
Next counter

The ‘counter’ is like a variable, except that it doesn’t need to be defined first. It can have any name, as long as the same name is used in both the ‘for’ and ‘next’ parts of the loop. The ‘start’ and ‘end’ values refer to the initial value of the ‘counter’ and the point at which the loop stops, so, for example, ‘start’ and ‘end’ values of 1 and 10 would mean that the statements within the loop would be executed ten times.

The following example displays the value of the ‘counter’ in the corresponding cell in column ‘A’ of a spreadsheet. Notice that the cell reference is formed by concatenating the letter ‘A’ with the ‘counter’. As the ‘start’ and ‘end’ values for the ‘counter’ are 1 and 10, cells ‘A1’ through to ‘A10’ will be populated with the numbers 1 to 10, ‘A1’ will contain 1, ‘A2’ will contain 2 and so on.

For i = 1 To 10
   Range("A" & i).Value = i
Next i

The ‘step’ keyword can also be included in a ‘For’ loop. This has the effect of increasing the ‘counter’ by the specified value, instead of incrementing by one each time through the loop as in the above example.

For i = 1 To 10 Step 2
   Range("A" & i).Value = i
Next i

Here the ‘step’ has been set to 2 so only cells ‘A1’, ‘A3’, ‘A5’, ‘A7’ and ‘A9’ will be set with their corresponding ‘counter’ value.

As well as being able to reference a column by its letter, it is also possible to use a numeric value. Here the ‘counter’ is used to specify the column, whilst the row is fixed at 1. The cells ‘A1’ through to ‘J1’ are populated with the numbers 1 to 10 from the ‘counter’. Note that when using this method, the row is specified before the column, which is the opposite of the above examples.

For i = 1 To 10
   Cells(1, i).Value = i
Next i

‘Do While’ Loop

As mentioned above, the ‘Do While’ loop has a condition, which must evaluate to true before the statements within it can be executed.

Do While condition
   ' Statement(s) to execute.
Loop

In the below example a variable, ‘i’, is initialised to 1, then the ‘Do While’ loop checks to see that the variable is less than or equal to 10 before displaying the value of the variable in the corresponding cell of column ‘A’, for example, cell ‘A1’ will contain 1, ‘A2’ will contain 2, right up to A10, which will contain 10. After a cell is populated, the value of the variable is incremented by 1 and the loop starts again.

Dim i As Integer
i = 1

Do While i <= 10
   Range("A" & i).Value = i
   i = i + 1
Loop

Note that if the variable ‘i’ had been declared and initialised to eleven or above, then the statements within the loop would not be executed at all.

‘Do Until’ Loop

The ‘Do Until’ loop is similar to a ‘Do While’ loop except that the statements within the loop continue to execute whilst the condition is false, instead of true.

Do Until condition
   ' Statement(s) to execute.
Loop

Here a variable ‘i’ is declared and initialised to 1, then the ‘Do Until’ loop checks to see if the variable equals 11. If the variable is not equal to 11 then the value is displayed in the corresponding cell of column ‘A’, for example, cell ‘A1’ will contain 1, ‘A2’ will contain 2, right up to A10, which will contain 10. After a cell is populated, the value of the variable is incremented by 1 and the loop starts again. This produces the same results as the ‘Do While’ example above.

Dim i As Integer
i = 1

Do Until i = 11
   Range("A" & i).Value = i
   i = i + 1
Loop

Note that if the variable had been declared and initialised with 11, then the statements within the loop would not be executed at all. If the variable had been initialised with anything above 11, an infinite, or never-ending loop would be created because the variable will never equal 11, so the statements within the loop would continue to execute indefinitely.