Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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

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.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Writing a Do While Statement

Using the Loop While Statement

Head office

London

Manchester