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.


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.

VBA If – Then – Else Statement in Excel

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

Find the Biggest Number Between Two Numbers with If - Then - Else Statement in VBA

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

Checking Student’s Result Using If - Then - Else Statement in VBA

  • Run the macro and you will get the result according to your code.

Checking Student’s Result Using If - Then - Else Statement in VBA Excel

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


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.

Update Comment Using Multiple If - Then - Else Statement in VBA

  • Run this code and see the following picture where the comments boxes are filled by the appropriate results.

Update the Comment 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

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.

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

  • Run this code and you will get the direction names in the respective cells.

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

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.

If-Then-Else Statement to Update Single Cardinal Directions Based on Code in Excel VBA

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.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo