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
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
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):
Do
i = i + 1
Debug.Print i
Loop While i <>
Putting the condition at the end (UNTIL):
Do
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()
Cells.Find("Kermit").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Offset(0, 2).Value > 5 Then
Range( _
ActiveCell, _
ActiveCell.End(xlToRight) _
).Interior.ColorIndex = 20
End If
ActiveCell.Offset(1, 0).Select
Loop
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:
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
Dim fso As New FileSystemObject
Sub LoopingExample()
CreateFile
ReadFile
End Sub
Sub CreateFile()
Dim i As Integer
Dim tsWrite As TextStream
Set tsWrite = fso.CreateTextFile(
_
"c:\training\numbers.txt", True)
For i = 1 To 10
tsWrite.WriteLine ("i = " & i)
Next i
tsWrite.Close
End Sub
Sub ReadFile()
Dim tsRead As TextStream
Dim ThisLine As String
Dim n As Integer
Set tsRead = fso.OpenTextFile( _
"c:\training\numbers.txt", ForReading)
n = 0
Do Until tsRead.AtEndOfStream
n = n + 1
ThisLine = tsRead.ReadLine
Debug.Print "Line " & n & ": " & ThisLine
Loop
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.
