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.3 - The Else Clause

So far in this lesson, you've learnt how to perform a set of instructions when a condition you've tested for returns True. In practice, you'll often want to perform a different set of instructions when your condition returns False. This part of the lesson explains how to use the Else clause of the If statement to do exactly that.

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 linked to in the Files Needed section above. You'll find a worksheet which contains a very simple game based on generating random numbers:

You can click the button in cell C2 to create a new set of random numbers.

Clicking the button in cell C2 runs a subroutine which creates a new set of random numbers:

You can find the subroutine in the VBE.

If we assume that the player with the highest number is the winner, we'd like to add some code which will indicate who has won and lost:

We'll use different colours and text to indicate the winner and loser.

Creating a Basic If Statement

To begin, we'll add some code which changes the fill colour of the two cells if player 1 is the winner. Add an If statement below the existing code in the Roll_Again subroutine:

Test if the value of cell B2 is greater than that of cell D2. You can use the > operator to do this.

The > (greater than) symbol is an example of a comparison operator. You can see a list of other comparison operators in the Reference section of this module.

Add instructions to change the fill colour of the two cells, as shown below:

Here we're using green for the winner and red for the loser. Feel free to use any combination of colours you prefer.

Add the End If statement:

As this is a block If statement we'll need an End If statement.

In Excel, try clicking the Play! button until cell B2 contains a larger number than D2:

When player 1 wins, the cells will change colour.

Unfortunately for player 2 at this stage, there is no way for them to win:

When cell B2 doesn't contain a higher number than D2, the cells remain grey in colour.

Adding the Else Clause

To allow player 2 to win, add an Else clause to the If statement:

The Else keyword should occupy its own, separate line of code between the If and End If statements. Conventionally, it should be placed at the same indent level as the If statement.

You can now add instructions below the Else clause:

These instructions will flip the red and green colours when player 2 wins.

Return to Excel and try playing the game again:

This time, player 2 can win!

Player 2 has a slightly higher chance of winning than player 1 because we haven't added any code to handle a draw. Player 1 will win only if the value in B2 is greater than D2. If this is not the case, player 2 will win automatically.

Now that you've seen the basic concept of the Else clause you can either complete the Extra Practice section below, or move on to the next part of the lesson to learn about the ElseIf statement.

To practise using the Else clause:

In the same subroutine, add instructions to the If statement to write the words Winner and Loser into the worksheet when player 1 wins:

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

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

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

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

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

Else

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

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

End If

Add two instructions below the Else clause to indicate when player 2 is the winner:

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

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

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

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

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

Else

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

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

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

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

End If

Click the button on the worksheet to check that the correct word appears when each player wins:

The appropriate text will appear below each player's number.

Add two instructions below the If statement to change the colour of the font when player 1 is the winner:

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

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

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

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

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

Range("B3").Font.Color = rgbGreen

Range("D3").Font.Color = rgbRed

Else

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

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

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

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

End If

Add two instructions below the Else clause to change the colours when player 2 is the winner:

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

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

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

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

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

Range("B3").Font.Color = rgbGreen

Range("D3").Font.Color = rgbRed

Else

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

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

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

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

Range("B3").Font.Color = rgbRed

Range("D3").Font.Color = rgbGreen

End If

Click the button on the worksheet and check that you see the appropriate word in the appropriate colour below each player's number:

Everything should now work as expected.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Creating a Basic If Statement

Adding the Else Clause

Head office

London

Manchester