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.
You can click here to download the file for this page.
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:
Range("B2, D2").Interior.Color = rgbOrange
Range("B3, D3").Value = "DRAW!"
- 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.
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
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.
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!