MODULES
LESSONS
TOPICS
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.
Files Needed
You can click here to download the file for this page.
Completed Code
You can click here to download a file containing the sample code.
The Example Workbook
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.
Writing a Do While Statement
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.
Using the Loop While Statement
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:
- Click here to download the file you need for this section. Extract and open the file.
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.
- Select a team from the drop down list 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.
- In the VBE, find the subroutine called Highlight_Matches and add an instruction to select cell A5:
Sub Highlight_Matches()
'Clear colour from list
Range("A5:E52").Interior.ColorIndex = xlNone
Range("A5").Select
End Sub
- Begin a Do While loop which continues wihle the active cell is not empty:
Sub Highlight_Matches()
'Clear colour from list
Range("A5:E52").Interior.ColorIndex = xlNone
Range("A5").Select
Do While ActiveCell.Value <> ""
Loop
End Sub
- Add an If statement to check if either team name matches the one in cell B2:
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
- Add an instruction within the If statement to highlight the row in any colour:
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
- Add an instruction below the End If line to move down one cell:
Do Wihle 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
- Add an instruction below the Loop line which returns the user to the cell containing their selected team:
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
- Click the button on the worksheet:
Each match involving the selected team will be highlighted in the specified colour.
- Save and close the workbook. You can click here to download a working version of the file.