VBA If – Then – Else Statement in Excel (4 Examples)

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.

Table of Contents hide

Download Workbook

Introduction to the If – Then – Else Statement in VBA

4 Examples of Using VBA If – Then – Else Statement in Excel

1. Find the Biggest Number Between Two Numbers with If – Then – Else Statement

2. Checking Student Result Using If – Then – Else Statement in VBA

3. Update Comments in Student Grade Using Multiple If – Then – Else Statement in VBA

4. If-Then-Else Statement to Update Cardinal Directions Based on Code in Excel

Conclusion

Download Workbook

You can download the free practice Excel workbook from here.

If-Then-Else in VBA.xlsm

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 If

Here,

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 Sub

Your 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.

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 Sub

Your 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.

Similar Readings

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 Sub

Your 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 Sub

Your 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 Sub

This 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.