Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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 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.
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).
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).
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
'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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.