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.2 - Do Until Loops

In the previous part of the lesson you saw how to use a Do - Loop statement to make instructions repeat. In order to stop the loop you tested a condition and used the Exit Do statement if the condition was met. This part of the lesson shows you an alternative way to stop the loop, using the Do Until statement.

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

Extract and open the file linked to in the Files Needed section above. You'll find a version of the workbook you used in the previous part of this lesson:

Each worksheet contains a list of matches in a single group in the 2018 World Cup.

In the VBE you'll find a subroutine which generates a set of random scores for every match in every group:

The procedure uses two Do - Loop statements to complete its task.

Currently, the subroutine uses If statements to determine when to end each loop:

The two If statements shown here exit from the loops when the conditions are met.

When your loop includes a condition and an exit as either the first or last instruction within the loop, you can write the code in a more elegant way by including the logical test in the Do - Loop statement.

Writing a Do Until Statement

We'll start by altering the code for the nested Do - Loop statement. Select and delete the If statement which checks if the active cell is empty:

Select and delete the If statement highlighted here.

After removing the If statement, change the Do statement into a Do Until statement as shown below:

The Do Until statement allows you to include a logical test within the definition of the loop.

The logical test you include in the Do Until statement is evaluated each time the loop returns to the start:

The condition is checked each time the loop executes this line of code.

When the logical test returns True, the code will automatically exit from the loop and run the first instruction it finds below the Loop line. Run the subroutine to test that it works:

The nested loop with automatically stop when it reaches an empty cell.

Testing the Condition at the End of the Loop

Rather than testing the condition at the start of the loop, you can test it at the end instead. Try moving the condition from its current position:

Select and cut the highlighted piece of code.

Add the condition to the Loop line:

Paste the code at the end of the Loop line.

Run the subroutine to check that the code still works.

The only difference this makes is that the instructions inside the loop will also be executed at least once before the condition is evaluated. When the condition is part of the Do line, it is evaluated before the instructions are executed. This makes it possible for the instructions in the loop to never be carried out. For our example this doesn't affect the result as cell A3 always contains a value.

When Do Until Loops Don't Work

Do Until or Loop Until loops work when the condition you're using to determine when to exit the loop needs to be evaluated at the start or end of the loop. In the other Do - Loop statement in our code, the condition needs to be evaluated before the end of the loop:

In the outer loop, the condition we're testing to determine when to exit the loop is neither the first nor the last instruction within the loop.

In this case, using a Do Until or Loop Until statement means that that code wouldn't work properly:

Statement	Problem
Do Until	The loop would exit once it reaches the last worksheet but before it generates a set of scores for that worksheet.
Loop Until	The code will generate a set of scores on the last worksheet and then cause a run-time error. This error will be caused by attempting to move to the next worksheet before testing if it is already on the last worksheet.

To practise creating Do Until 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 Until loop which continues until the active cell is empty:

Sub Highlight_Matches()

'Clear colour from list

Range("A5:E52").Interior.ColorIndex = xlNone

Range("A5").Select

Do Until ActiveCell.Value = ""

Loop

End Sub

Add an If statement to check if either team name matches the one in cell B2:

Do Until 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 Until 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 Until 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 Until 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 Sample Workbook

Writing a Do Until Statement

Testing the Condition at the End of the Loop

When Do Until Loops Don't Work

Head office

London

Manchester