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

Topic 3.1.5 - Nested If Statements

By this point in the lesson you should be comfortable with writing If statements to test a condition and perform different sets of actions depending on whether or not the condition has been met. In this part of the lesson you'll learn how to write an If statement inside another one to create a nested If 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 workbook you downloaded from the Files Needed section above. You'll find a version of the simple game you've been using in the previous parts of this lesson:

Currently, the game colours the cell with highest score in green and the other in red. If the scores are equal, the cells are coloured in orange. Try clicking the button a few times to test this.

You can find the code in the VBE in a subroutine called Roll_Again:

The code generates a number between 1 and 10 for each player then uses an If statement to determine the colours of the cells.

For this example, we'd also like to indicate to the winner if they have received the maximum possible score of 10:

We'd like this message to appear in cell B3 whenever player 1 wins and also receives the top score.

Creating a Nested If Statement

You can create a nested If statement simply by writing an If statement within another one. In our example, we want to do this within the part of the existing If statement which runs when player 1 wins:

Our new If statement needs to appear below the first one, but before the ElseIf.

You can begin writing the new If statement in the usual way. We want to test if the value of cell B2 is equal to 10:

It's conventional to indent the new If statement one tab space within the original one.

You can now write the instructions you'd like to perform when the condition is met:

We'll change the text and the colour in cell B3.

If your nested If statement is a block If it will also need to have an End If as shown below:

You now have a complete nested If statement.

Regardless of whether player 1 has scored the maximum, we should also indicate that player 2 is the loser. We can do this by adding a new instruction below the nested If statement:

Add the new instruction between the End If and ElseIf lines.

Testing the Code

It's worth stepping through the subroutine to understand how the nested If statement works. Firstly, edit the first two lines of the subroutine to make sure that player 1 always scores 10 and that player 2 will only score between 1 and 9:

This will ensure that we get the result we want for testing purposes.

Click the button on the worksheet to play the game and check that you see the correct words appear in the relevant cells:

Each player should receive the correct description.

You may find it useful to step through the procedure by pressing the F8 key so that you can see which instructions VBA decides to execute.

When you're happy that the procedure is working, edit the first two lines of the subroutine to make the game fair again!

Make sure that both players can score between 1 and 10.

Extending a Nested If

A nested if statement can also have ElseIf conditions and an Else clause. To demonstrate this, let's add an Else clause to label player 1 as the winner if they don't receive the maximum score:

Find your nested If statement and add an Else clause before the End If.

Add an instruction to change the value of cell B3:

You can add as many instructions to the Else clause as you like but we'll stick to just one for now.

Test that your code works by clicking the button on the worksheet a few times:

When player 1 wins but doesn't score 10, the word WINNER! appears in cell B3.

You can now either practise writing more nested If statements in the section below, or continue to the next lesson.

To practise writing nested If statements:

Using the same subroutine, add a nested If statement to the ElseIf section of your existing code to test if player 2 has scored 10:

ElseIf Range("D2").Value > Range("B2").Value Then

Range("B2").Interior.Color = rgbRed

Range("D2").Interior.Color = rgbGreen

If Range("D2").Value = 10 Then

Else

Range("B2, D2").Interior.Color = rgbOrange

End If

Add two instructions to indicate that player 2 has received the top score:

ElseIf Range("D2").Value > Range("B2").Value Then

Range("B2").Interior.Color = rgbRed

Range("D2").Interior.Color = rgbGreen

If Range("D2").Value = 10 Then

Range("D3").Value = "TOP SCORE!"

Range("D3").Interior.Color = rgbGold

Else

Range("B2, D2").Interior.Color = rgbOrange

End If

Add an Else clause to your nested If to describe player 2 as the winner if they don't score 10:

ElseIf Range("D2").Value > Range("B2").Value Then

Range("B2").Interior.Color = rgbRed

Range("D2").Interior.Color = rgbGreen

If Range("D2").Value = 10 Then

Range("D3").Value = "TOP SCORE!"

Range("D3").Interior.Color = rgbGold

Else

Range("D3").Value = "WINNER!"

Else

Range("B2, D2").Interior.Color = rgbOrange

End If

Complete the nested If by adding the End If statement:

ElseIf Range("D2").Value > Range("B2").Value Then

Range("B2").Interior.Color = rgbRed

Range("D2").Interior.Color = rgbGreen

If Range("D2").Value = 10 Then

Range("D3").Value = "TOP SCORE!"

Range("D3").Interior.Color = rgbGold

Else

Range("D3").Value = "WINNER!"

End If

Else

Range("B2, D2").Interior.Color = rgbOrange

End If

Add an instruction below your nested If - End If block which labels player 1 as the loser, regardless of whether player 2 has scored 10:

ElseIf Range("D2").Value > Range("B2").Value Then

Range("B2").Interior.Color = rgbRed

Range("D2").Interior.Color = rgbGreen

If Range("D2").Value = 10 Then

Range("D3").Value = "TOP SCORE!"

Range("D3").Interior.Color = rgbGold

Else

Range("D3").Value = "WINNER!"

End If

Range("B3").Value = "LOSER!"

Else

Range("B2, D2").Interior.Color = rgbOrange

End If

Test that your code works when player 2 receives the top score:

When player 2 scores 10 and player 1 has a lower score, you should see this.

Verify that the code works when player 2 wins but doesn't receive the top score:

When player 2 wins but scores less than 10, you should see this.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Sample Workbook

Creating a Nested If Statement

Testing the Code

Extending a Nested If

Head office

London

Manchester