We're excited to announce that from 14th April we'll be running live online training courses too!
From 14th April we'll be running live online training courses too!
Looping in Visual Basic for Applications macros
Part three of a four-part series of blogs

There are 4 types of loop in Visual Basic for Applications - this blog shows how and when to use each type of VBA loop structure.

  1. Looping in VBA Macros - an introduction to the 4 types of loop
  2. Looping a set number of times or iterations (FOR … NEXT)
  3. Looping with conditions (DO UNTIL / WHILE ... LOOP) (this blog)
  4. Looping over collections (FOR EACH ... NEXT)

This series of blogs is part of our Excel VBA online tutorial.  We also run training courses in Excel and VBA.

Posted by Andy Brown on 15 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Looping with conditions (DO UNTIL / WHILE ... LOOP)

The second type of loop, which is far more used than FOR ... NEXT, can be expressed in English as either:

  • keep doing something until a condition is true; or
  • keep doing something while a condition is true.

These are the most dangerous types of loop, since it's so easy for the condition never to happen!  In this case you'll be looking at an hourglass while your computer's CPU gradually starts warming up with the effort it's putting in ...

The basic syntax

The syntax of the two types of loop is as follows.  Firstly, DO UNTIL:

Sub KeepDoingSomething()

Dim i As Integer

i = 0

'keep going round the loop until the condition is true

Do Until i = 10

i = i + 1

Debug.Print i


End Sub

Whatever you can write using DO UNTIL you can also write using DO WHILE:

Sub KeepDoingSomething()

Dim i As Integer

i = 0

'keep going round the loop until the condition is true

Do While i <>

i = i + 1

Debug.Print i


End Sub

The above example shows that you only need to learn one of DO UNTIL and DO WHILE, as they're interchangeable.

Alternative syntaxes

You can write a DO loop in a number of different ways.  For the example above, you could also use any of the following (shown abbreviated, and without comments). 

Using WHILE ... WEND (a bit old-fashioned):

While i <>

i = i + 1

Debug.Print i


Putting the condition at the end (WHILE):


i = i + 1

Debug.Print i

Loop While i <>

Putting the condition at the end (UNTIL):


i = i + 1

Debug.Print i

Loop Until i = 10

However, I personally don't like any of these alternatives (I've included them here just because you may sometimes come across them).

An Example of Looping Until a Condition is True

Suppose that we want to colour all of the highly-rated Muppets in the spreadsheet below:

A few muppets in a list

You may disagree with the ratings shown, but that's not the point of the exercise!


An algorithm to achieve this would be:

  1. Start at the green cell.
  2. Keep going until you reach a blank cell.
  3. For each time round this loop, if the value of the cell 2 to the right is more than 5, colour the row.

Here's what we want the spreadsheet to look like when we've finished:

The muppets, with some coloured

As it happens, this pale blue colour is number 20.


The code to make this work might look like this:

Sub ColourGoodMuppets()

'go to Kermit (top of list)


'keep going down till we hit a blank cell

Do Until ActiveCell.Value = ""

'if this has rating above 5 in column D ...

If ActiveCell.Offset(0, 2).Value > 5 Then

'... colour entire row

Range( _

ActiveCell, _

ActiveCell.End(xlToRight) _

).Interior.ColorIndex = 20

End If

'go onto next Muppet

ActiveCell.Offset(1, 0).Select


'show finished!

MsgBox "All done!"

End Sub

Here we've set the interior colour of a block of cells (taken from the current cell across to the right) to 20, which happens to be pale blue.

Sorry to keep saying it, but ... this macro would have been much better written by looping over the collection of cells, using FOR EACH ... NEXT (see next part of this blog series).

Another Example - Reading in Records or Lines

Where looping until (or while) a condition is true is invaluable is when you're reading through the lines of a file.  The following code will:

  1. Create a text file containing the first 10 integers; then
  2. Read the file back in until there are no more lines.

My reason for showing this example is to illustrate the fact that sometimes looping until/while a condition is true is the only way to go!

Let's start with what the output of the program will look like:

Immediate window showing numbers from 1 to 10

The program should print out the lines read in from the text file.


The first thing you'll need to do is to reference the Microsoft Scripting Runtime object library (more on this in a later blog on referencing other applications - if you're not show how to do this, just read through the code for now).

The code to produce the output shown above is then:

Option Explicit

'you need this to create and read files

Dim fso As New FileSystemObject

Sub LoopingExample()

'note that you'll need to create a reference to the Microsoft Scripting Runtime

'object library for this to work

'write the text file out


'now read it back in


End Sub

Sub CreateFile()

Dim i As Integer

Dim tsWrite As TextStream

'start writing to a new text file

Set tsWrite = fso.CreateTextFile( _

"c:\training\numbers.txt", True)

'write out the first 10 numbers

For i = 1 To 10

tsWrite.WriteLine ("i = " & i)

Next i

'close the file


End Sub

Sub ReadFile()

Dim tsRead As TextStream

Dim ThisLine As String

Dim n As Integer

'open the text file created for reading

Set tsRead = fso.OpenTextFile( _

"c:\training\numbers.txt", ForReading)

n = 0

'keep reading until there are no more lines

Do Until tsRead.AtEndOfStream

'for each line, read it and show it in immediate window

n = n + 1

ThisLine = tsRead.ReadLine

Debug.Print "Line " & n & ": " & ThisLine


'good housekeeping - close the file


End Sub

The subroutine to run for the above example is the top one, LoopingExample, which will then call the other two routines.


This blog has 0 threads Add post