Module 3 - Conditions and Loops
Lesson 3.5 - Conditional Loops
Topic 3.5.1 - Basic Do Loops

Looping is an important concept in programming.  A loop allows you to tell a group of instructions to execute repeatedly.  VBA has several different types of loop; this lesson introduces you to the Do Loop structure, which allows you to continue executing instructions based on the result of a condition.

The condition that you use in a Do Loop can be based on any valid logical test.  You might continue looping based on the value of a cell, the result of a calculation, or even the time of day.

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 workbook containing lists of matches for the 2018 World Cup:

List

Each worksheet contains a list of six matches for a single group.

 

We'd like to create a procedure which predicts the score for each match in each group.  In the VBE you'll find a subroutine which predicts the result of a single match:

Predict score

The code selects the first match on the first worksheet and creates a random score for each team.

 

Run the subroutine and check the result in Excel:

Result

You'll see a result appear for the first match of Group A.

 

Now all we have to do is make this code repeat for every match on every worksheet!

Making Code Repeat

One of the simplest ways to make code repeat in VBA is to enclose a set of instructions in a Do - Loop statement.  Using the Predict_Scores subroutine, add the word Do above the two instructions which generate the random scores and add the word Loop below those two instructions:

Do Loop

It's conventional to indent the instructions within the loop by one tab space.

 

Now try stepping through the code by pressing the F8 key until you reach the Loop line:

Step through

Continue pressing F8 until you reach the line shown highlighted in yellow.

 

Press F8 again to see which line will be executed next:

Return

Pressing F8 again returns to the first instruction within the Do - Loop statement.

 

Congratulations, you've successfully started your code looping!  The major problem at this point is that it doesn't know when to stop.  You can stop the subroutine manually by choosing Run | Reset from the menu, or by clicking the Reset tool:

Reset

Click this tool to stop the subroutine.

 

Infinite Loops

When you begin writing code which uses loops, it's very easy to create loops that will never end, or infinite loops.  Just in case you accidentally run a procedure which contains an infinite loop, you should know that you can attempt to stop it by pressing either Esc or Ctrl + Break on the keyboard:

Message

When you break into the running subroutine, you'll see this dialog box appear. Click End to stop the code.

 

Exiting a Do Loop

We'd like our loop to stop automatically once it reaches the bottom of the current table.  In order to achieve this, we need to move down to the next cell once we've created a result for the first match.  Add a line to move down one cell before the Loop line:

Move down

Use the Offset property and Select method to do this.

 

We can now test if the selected cell is empty and, if so, exit from the Do - Loop statement:

Exit Do

Use the Exit Do statement to make the code jump out of the Do - Loop.

 

Save the workbook and then try running the subroutine:

Complete table

Each match will have a result (regardless of how unrealistic it is!) and the selected cell will be the next empty cell in column A.

 

Now we simply need to make our code move to the next worksheet and repeat.

Nesting Do Loops

To create a set of scores for each group we need to move to the next worksheet and repeat the instructions we've already written.  We can do this by nesting our existing loop within another.  Start by adding an instruction to move to the next worksheet after the existing loop has finished:

Move next

This instruction will select the worksheet to the right of the currently selected one.

 

After moving to the next worksheet, we need to repeat the list of instructions starting with selecting cell A3.  To do this, add a new Do - Loop statement which encloses the instructions:

Second loop

The first loop is nested within the second.

 

The new loop will continue attempting to select the next worksheet, even when there isn't one to select.  This will cause an error once you reach the Group H worksheet:

Error

If there is no worksheet to the right of the currently selected one, you'll see this error message.

 

We can use this to create an exit point for the loop.  Add an If statement above the instruction which selects the next worksheet:

Exit

It's important to exit from the Do - Loop before we attempt to select the next worksheet.

 

Run the subroutine and check the result in Excel:

Result

After running the subroutine, each group will have a full set of scores and the Group H worksheet will be selected.

 

To practise writing Do - Until loops, we'll add a procedure which formats the winner and loser of each match in different colours:

  1. Create a new subroutine called Format_Results and add an instruction which selects the first worksheet:

Sub Format_Results()

 

Worksheets(1).Select

 

End Sub

  1. Create a Do - Loop statement and add an instruction which selects cell A3:

Sub Format_Results()

 

Worksheets(1).Select

 

Do

 

Range("A3").Select

 

Loop

 

End Sub

  1. Add an If statement to check if there is a worksheet to the right of the selected one.  If not, exit from the loop, otherwise, select the next sheet:

Sub Format_Results()

 

Worksheets(1).Select

 

Do

 

Range("A3").Select

 

If ActiveSheet.Next Is Nothing Then

Exit Do

Else

ActiveSheet.Next.Select

End If

 

Loop

 

End Sub

  1. Run or step through the subroutine to check that it works without causing an error:
Result

You should end up on the Group H worksheet with cell A3 selected.

 
  1. Add a nested Do - Loop statement above the If statement with an instruction to move down one cell:

Sub Format_Results()

 

Worksheets(1).Select

 

Do

 

Range("A3").Select

 

Do

 

ActiveCell.Offset(1, 0).Select

 

Loop

 

If ActiveSheet.Next Is Nothing Then

Exit Do

Else

ActiveSheet.Next.Select

End If

 

Loop

 

End Sub

  1. Add an instruction which exits from the nested loop if the active cell is empty:

Do

 

ActiveCell.Offset(1, 0).Select

 

If ActiveCell.Value = "" Then Exit Do

Loop

  1. Run or step through the procedure to make sure that it runs without causing an error:
Result

You will end up on the Group H worksheet with cell A9 selected.

 
  1. Add an If statement to the nested Do - Loop which checks if the score for Team1 is higher than for Team2:

Do

 

If ActiveCell.Offset(0, 2).Value > _

ActiveCell.Offset(0, 4).Value Then

 

ActiveCell.Offset(1, 0).Select

 

If ActiveCell.Value = "" Then Exit Do

Loop

  1. Add instructions to highlight the winning score in one colour and the losing score in a different colour:

Do

 

If ActiveCell.Offset(0, 2).Value > _

ActiveCell.Offset(0, 4).Value Then

 

ActiveCell.Offset(0, 2).Interior.Color = rgbGreen

ActiveCell.Offset(0, 4).Interior.Color = rgbRed

  1. Add an ElseIf statement to check if Team2 is the winner and colour the cells accordingly:

Do

 

If ActiveCell.Offset(0, 2).Value > _

ActiveCell.Offset(0, 4).Value Then

 

ActiveCell.Offset(0, 2).Interior.Color = rgbGreen

ActiveCell.Offset(0, 4).Interior.Color = rgbRed

 

ElseIf ActiveCell.Offset(0, 4).Value > _

ActiveCell.Offset(0, 2).Value Then

 

ActiveCell.Offset(0, 2).Interior.Color = rgbRed

ActiveCell.Offset(0, 4).Interior.Color = rgbGreen

  1. Add an Else clause which colours both scores the same colour in the event of a draw:

Do

 

If ActiveCell.Offset(0, 2).Value > _

ActiveCell.Offset(0, 4).Value Then

 

ActiveCell.Offset(0, 2).Interior.Color = rgbGreen

ActiveCell.Offset(0, 4).Interior.Color = rgbRed

 

ElseIf ActiveCell.Offset(0, 4).Value > _

ActiveCell.Offset(0, 2).Value Then

 

ActiveCell.Offset(0, 2).Interior.Color = rgbRed

ActiveCell.Offset(0, 4).Interior.Color = rgbGreen

 

Else

 

ActiveCell.Offset(0, 2).Interior.Color = rgbOrange

ActiveCell.Offset(0, 4).Interior.Color = rgbOrange

 

End If

  1. Save the workbook then run the subroutine to check that it works as intended:
Result

We could have chosen nicer colours.

 
  1. Save and close the workbook.
This page has 0 threads Add post