Module 3 - Conditions and Loops
Lesson 3.5 - Conditional Loops
Topic 3.5.3 - Do While Loops

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:

Example

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:

Code

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:

Until

Select and delete this keyword.

 

Write the word While in its place:

While

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:

Equals

Remove the = operator.

 

Replace the = (equal to) operator with the <> (not equal to) operator:

Not equal

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:

Results

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:

Move condition

Select and cut the code shown highlighted here.

 

You can paste the condition onto the Loop line:

Paste

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:

  1. Click here to download the file you need for this section.  Extract and open the file.
List

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.

  1. Select a team from the drop down list in cell B2:
Pcik team

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.

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. Click the button on the worksheet:
Result

Each match involving the selected team will be highlighted in the specified colour.

  1. Save and close the workbook.  You can click here to download a working version of the file.
This page has 0 threads Add post