Sanjida Ahmed
Conditional statements are used to perform a set of actions depending on the specified condition in programming languages. In this article, we will show you what is If – Then – Else conditional statement in VBA Excel is and how to use it.
Download Workbook
You can download the free practice Excel workbook from here.
Introduction to the If – Then – Else Statement in VBA
VBA If – Then – Else conditional statement is mainly used to decide the execution flow of the conditions. If the condition is true then a certain set of actions are executed, and if the condition is false then another set of actions are performed.
- Syntax
If condition Then [statements] [Else else_statements]Or,
If condition Then
[statements]
[Else
[else_statements]]
End IfHere,
| Argument | Required/ Optional | Description |
|---|---|---|
| condition | Required | A numeric expression or a string expression that evaluates whether the expression is True or False. If the condition is Null, it is considered False. |
| statements | Optional | A single-line form that has no Else clause. One or more statements must be separated by colons. If the condition is True, then this statement is executed. |
| else_statements | Optional | One or more statements are performed if no previous condition is True. |
4 Examples of Using VBA If – Then – Else Statement in Excel
In this section, you will learn how to use If-Then-Else in VBA code with 4 examples.
1. Find the Biggest Number Between Two Numbers with If – Then – Else Statement
If you have two numbers and you want to find out which one is the bigger (or smaller) then you can use the If-Then-Else statement in VBA.
Steps:
- Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- In the pop-up code window, from the menu bar, click Insert -> Module.
- Copy the following code and paste it into the code window.
Private Sub BiggestNumber()
Dim Num1 As Integer
Dim Num2 As Integer
Num1 = 12345
Num2 = 12335
If Num1 > Num2 Then
MsgBox "1st Number is Greater than the 2nd Number"
ElseIf Num2 > Num1 Then
MsgBox "2nd Number is Greater than the 1st Number"
Else
MsgBox "1st Number and the 2nd Number are Equal"
End If
End SubYour code is now ready to run.
Here, we are comparing two numbers 12345 and 12335, to find out which one is bigger. This process is usually perfect for finding large numbers in a large dataset.
- Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.
You will get the result in Excel’s MsgBox
In our case, number 12345 – stored in variable Num1 – is bigger than the number 12335, Num2. So the MsgBox is showing us that the 1st Number is Greater than the 2nd Number.
Read More: Excel Formula to Generate Random Number (5 examples)
2. Checking Student Result Using If – Then – Else Statement in VBA
You can check whether a student passes or fails an exam with this statement in the VBA code.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub CheckResult()
If Range("D5").Value > 33 Then
MsgBox "John's Result is Pass"
Else
MsgBox "John's Result is Fail"
End If
End SubYour code is now ready to run.
This code will check whether Cell D5 holds a value that is greater than 33. If it does then it will show one output, if it doesn’t then it will show something else.
- Run the macro and you will get the result according to your code.
Look at the above dataset with the result, Cell D5 holds 95 which is certainly more than 33, hence it is displaying the Result is Pass. But if we run the code for Cell D7 (22), then it would display otherwise.
Read More: How to Use VBA Case Statement (13 Examples)
Similar Readings
- How to Use Log Function in Excel VBA (5 Suitable Examples)
- Use VBA LTrim Function in Excel (4 Examples)
- How to Use VBA FileDateTime Function in Excel (3 Uses)
- Use VBA Mod Operator (9 Examples)
- VBA EXP Function in Excel (5 Examples)
3. Update Comments in Student Grade Using Multiple If – Then – Else Statement in VBA
You have learned how to extract whether a student passer or not with a single If-Then-Else statement, but this time you will learn about Multiple If-Then-Else statements with the following example.
We will run a VBA code to fill out those Comment boxes based on multiple conditions.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub UpdateComment()
For Each grade In Range("D5:D10")
If grade = "A" Then
grade.Offset(0, 1).Value = "Great Work"
ElseIf grade = "B" Then
grade.Offset(0, 1).Value = "Keep It Up"
ElseIf grade = "C" Then
grade.Offset(0, 1).Value = "Needs Improvement"
Else
grade.Offset(0, 1).Value = "Parents-Teacher Meeting"
End If
Next grade
End SubYour code is now ready to run.
This code will print comments according to the grade achieved by students.
- Run this code and see the following picture where the comments boxes are filled by the appropriate results.
4. If-Then-Else Statement to Update Cardinal Directions Based on Code in Excel
You can also utilize the If-Then-Else to find the cardinal directions based on the indicator code provided. Look at the following picture where we will find out the directions based on the initials that have been given.
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub UpdateDir()
For Each iDirection In Range("B5:B8")
If iDirection = "N" Then
iDirection.Offset(0, 1).Value = "North"
ElseIf iDirection = "S" Then
iDirection.Offset(0, 1).Value = "South"
ElseIf iDirection = "E" Then
iDirection.Offset(0, 1).Value = "East"
Else
iDirection.Offset(0, 1).Value = "West"
End If
Next iDirection
End SubYour code is now ready to run.
- Run this code and you will get the direction names in the respective cells.
Or, if you want to find just one direction based on code, then you can use the code below.
Sub UpdateDirections()
Dim iDirection As String
Dim iDirectionName As String
iDirection = Range("B5").Value
If iDirection = "N" Then
iDirectionName = "North"
ElseIf iDirection = "S" Then
iDirectionName = "South"
ElseIf iDirection = "E" Then
iDirectionName = "East"
Else
iDirectionName = "West"
End If
Range("C5").Value = iDirectionName
End SubThis code will take the value from Cell B5 into consideration and returns the result according to it in Cell C5.
For instance, if you write “N” in Cell B5, it will give you “North; if you write “S” in Cell B5, it will show you “South” in Cell C5.
Conclusion
This article showed you how to use the If – Then – Else statement in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
