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
545 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 ...
A Do While loop is very similar to the Do Until loop that you encountered in the previous part of this lesson. You can use a Do While loop to repeat a set of instructions based on the result of a logical test, as this part of the lesson will show you.
You can click here to download the file for this page.
You can click here to download a file containing the sample code.
Extract and open the file you downloaded from the Files Needed section above. You'll find a version of the workbook that you've used in the earlier parts of this lesson:
Each worksheet contains a list of group games for the 2018 World Cup.
In the VBE you'll find a subroutine which generates random scores for every match in each worksheet:
The procedure uses two Do - Loop statements to achieve this task.
In the existing code, the nested loop uses the Do Until statement. We'd like to change this to use the Do While statement instead.
To convert the Do Until statement into a Do While statement, start by removing the word Until:
Select and delete this keyword.
Write the word While in its place:
Replace Until with While.
The Do Until statement continues looping until its logical test returns True. The Do While statement continues looping until its logical test returns False. Currently, the logical test is checking if the selected cell is empty. We've selected cell A3, which is not empty, before starting the loop. This means that the logical test returns False and the Do While loop will stop immediately. We need to change the logical test as shown below:
Remove the = operator.
Replace the = (equal to) operator with the <> (not equal to) operator:
This tests if the value of the active cell is not equal to an empty string.
You can now run the subroutine to check that it still works:
Each match on each worksheet will have a random score.
In this example, using Until or While makes no difference to the end result. Depending on the logical test you're evaluating, you may find it easier to use Until in some cases and While in others. In many cases, it will simply come down to personal preference.
If you prefer, you can place the logical test on the Loop line, rather than the Do line:
Select and cut the code shown highlighted here.
You can paste the condition onto the Loop line:
Add the condition to the Loop line.
The only difference this makes is that the instructions in the loop will always be executed once before the condition is evaluated. When the condition is on the Do line, it's possible that the instructions in the loop will never be carried out.
Run the procedure to make sure that it still works, then save and close the workbook.
To practise writing Do While loops:
The workbook contains a list of all World Cup 2018 matches. When the button is clicked, we want to highlight every match played by the team selected in cell B2.
Pick any team from the list.
The drop down list was created using Excel's Data Validation feature. You can select cell B2 and choose Data | Data Validation from the ribbon to see how this is configured.
Sub Highlight_Matches()
'Clear colour from list
Range("A5:E52").Interior.ColorIndex = xlNone
Range("A5").Select
End Sub
Sub Highlight_Matches()
'Clear colour from list
Range("A5:E52").Interior.ColorIndex = xlNone
Range("A5").Select
Do While ActiveCell.Value <> ""
Loop
End Sub
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 1).Value = Range("B2").Value _
Or ActiveCell.Offset(0, 2).Value = Range("B2").Value Then
End If
Loop
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 1).Value = Range("B2").Value _
Or ActiveCell.Offset(0, 2).Value = Range("B2").Value Then
ActiveCell.Resize(1, 5).Interior.Color = rgbPaleGreen
End If
Loop
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 1).Value = Range("B2").Value _
Or ActiveCell.Offset(0, 2).Value = Range("B2").Value Then
ActiveCell.Resize(1, 5).Interior.Color = rgbPaleGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 1).Value = Range("B2").Value _
Or ActiveCell.Offset(0, 2).Value = Range("B2").Value Then
ActiveCell.Resize(1, 5).Interior.Color = rgbPaleGreen
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("B2").Select
Each match involving the selected team will be highlighted in the specified colour.
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.