Excel Visual Basic for Applications online training

This page has 1 thread | 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.4 - ElseIf Conditions

At this point in the course you have learnt how to use the If statement to test a condition and perform a series of actions if the condition is met. You also know how to use the Else clause to perform a different sequence of actions if the condition is not met. In this part of the lesson you'll learn how to add multiple conditions to an If statement by using the ElseIf keyword.

The ElseIf keyword allows you to test multiple, mutually-exclusive conditions within the same If - End If block. You'll learn about how to combine multiple conditions into a single test in a later part of this module.

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 linked to in the Files Needed section above. You'll find a modified version of the game you used in the previous part of this lesson:

You can click the button to create a random number in cells B2 and D2. The higher number is the winner and is coloured green, while the loser is coloured red.

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

The main difference from the previous part of the lesson is that the code generates a number with a maximum value of 10 rather than 100.

The If statement treats player 1 as the winner only if the number in cell B2 is greater than that in D2. This means that if there is a draw, player 2 is treated as the winner:

If the value of B2 is not greater than D2, player 1 loses!

This is slightly unfair to player 1, so we'll change the code to make sure that player 2 only wins if the value of D2 is greater than B2.

Creating an ElseIf Condition

To solve this problem, we need to create a second mutually-exclusive condition within the same If - End If block to test if the value of cell D2 is greater than that of B2. Start by altering the Else keyword to ElseIf:

It's important to note that in VBA, ElseIf is one word with no space.

You must write a condition on the same line as the ElseIf keyword. In this case, we'll test if the value of D2 is greater than B2:

Don't forget to write Then at the end of the condition.

We can now test that we get the correct result by running the subroutine until we get a tied result (you can cheat and alter the RandBetween function to use a smaller range of numbers if you like!):

When the same number is generated in each cell, neither one will change colour.

Multiple ElseIf Conditions

You can have as many ElseIf conditions within the same If - End If block as you like. Let's add another condition to test if the value of B2 and D2 are the same:

Add a new ElseIf keyword above the line containing End If.

Add a condition to the same line which tests if the value of B2 and D2 are equal:

Don't forget the Then keyword at the end of the line.

Add an instruction to change the fill colour of cells B2 and D2 to the same colour:

We can change the colour of B2 and D2 at the same time with this instruction.

Test that the code works by clicking the button on the worksheet until the scores are tied:

When the scores are equal, both cells will have the same fill colour.

Using the Else Clause

You can use the Else clause along with ElseIf conditions in the same If - End If block. In our example we don't need to create an explicit test to check if the value of B2 and D2 is equal. If B2 is not greater than D2 and D2 is not greater than B2 then, implicitly, the value of the two cells must be equal.

Edit the final ElseIf condition in your code:

Select this part of your code.

You can delete the selected code shown above to convert the ElseIf condition into a simple Else:

This instruction will run only when all of the previous conditions in the If - End If block have returned False.

Your subroutine should resemble the one shown below:

The final subroutine.

Try clicking the button on the worksheet a few times to check that the correct colours are generated each time.

You can now either practise using ElseIf conditions in the Extra Practice section below, or continue to the next part of this lesson.

To practise using ElseIf conditions:

Using the same subroutine, add instructions below the original If condition to add the words Winner and Loser to the appropriate cells 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!"

Add instructions below the ElseIf condition to add the words Winner and Loser to the appropriate cells when player 2 wins:

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

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

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

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

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

Add an instruction below the Else keyword to add the word Draw (or Tie if you prefer) to the appropriate cells when the score is the same:

Else

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

Range("B3, D3").Value = "DRAW!"

End If

Play the game a few times and make sure that the correct words appear in the expected cells:

Your worksheet should resemble this one after clicking the button.

Save and close the workbook.

This page has 1 thread

Add a new post

From:	Kayvan
When:	16 Sep 20 at 13:51

Hi Andrew,

Many thank you for your great course in VBA that I've ever had in whole my life.

I have two questions:

1- What is the difference if we write

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

"Versus "

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

That is, if we don't write .Value in our IF statement?

2- I got your book in Black & White and I'd like to know if I can get the colored version like the in-text section of your training as it helps a lot to recognize line, command, and error section easier and better.

Sincerely

Kayvan

From:	Andrew G
When:	17 Sep 20 at 09:15

Hi Kayvan,

Happy to hear that you enjoyed the course! To answer your questions:

1 - There's no difference between the two ways of writing the statement. Objects in VBA have a default property and the default property of a Range object happens to be Value. This means that you can omit the explicit reference to the Value property if that's the one you want. I believe this was designed to make writing VBA code more efficient when referring to the most commonly used property of objects. In practice, I always explicitly reference object properties as I think it makes the intent of the instruction clearer. I also write code in languages in which objects don't have default properties so it helps to stop me developing bad habits!

2 - Sadly, we don't have a colour version of the book available. When we looked into producing one the cost would have been three to four times more expensive than the black and white version.

I hope that helps and thank you for your comments and questions!

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Sample Workbook

Creating an ElseIf Condition

Multiple ElseIf Conditions

Using the Else Clause

Head office

London

Manchester