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:

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:

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:

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:

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:

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

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

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:

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:

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:

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:

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

Save the workbook and then try running the subroutine:

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:

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:

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:

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:

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:

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:

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

3. 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:

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

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

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

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

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

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

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

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

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

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

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

We could have chosen nicer colours.

13. Save and close the workbook.