This page has 0 threads | Add post

DO ... UNTIL Loops in VBA

Written by Andy Brown

In this tutorial

The basic syntax
Alternative syntaxes
An Example of Looping Until a Condition is True
Another Example - Reading in Records or Lines

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

Loop

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

Loop

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

Wend

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:

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

An algorithm to achieve this would be:

Start at the green cell.
Keep going until you reach a blank cell.
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:

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)

Cells.Find("Kermit").Select

'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

Loop

'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, or either of our online or classroom VBA courses).

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:

Create a text file containing the first 10 integers; then
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

CreateFile

'now read it back in

ReadFile

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

tsWrite.Close

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

Loop

'good housekeeping - close the file

tsRead.Close

End Sub

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

Previous
tutorial

Testing conditions

Previous
part

FOR ... NEXT loops in VBA

Top of page

Next
part

FOR EACH ... NEXT loops (looping over collections)

Next
tutorial

Events

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on VBA

Loops

DO ... UNTIL Loops in VBA

The basic syntax

Alternative syntaxes

An Example of Looping Until a Condition is True

Another Example - Reading in Records or Lines

Head office

London

Manchester