totn Excel Functions

MS Excel: How to use the IF-THEN-ELSE Statement (VBA)

This Excel tutorial explains how to use the Excel IF-THEN-ELSE statement (in VBA) with syntax and examples.

Description

The Microsoft Excel IF-THEN-ELSE statement can only be used in VBA code. It executes one set of code if a specified condition evaluates to TRUE, or another set of code if it evaluates to FALSE.

The IF-THEN-ELSE statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Please read our IF function (WS) page if you are looking for the worksheet version of the IF statement as it has a very different syntax.

subscribe button Subscribe


If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the IF-THEN-ELSE statement in Microsoft Excel is:

If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2

...

ElseIf condition_n Then
   result_n

Else
   result_else

End If

Parameters or Arguments

condition_1, condition_2, ... condition_n
The conditions that are to be evaluated in the order listed. Once a condition is found to be true, the corresponding code will be executed. No further conditions will be evaluated.
result_1, result_2, ... result_n
The code that is executed once a condition is found to be true.
result_else
The code that is executed when all previous conditions (condition1, condition2, ... condition_n) are false.

Returns

The IF-THEN-ELSE statement evaluates the conditions in the order listed. It will execute the corresponding code when a condition is found to be true.
If no condition is met, then the Else portion of the IF-THEN-ELSE statement will be executed.

Note

  • The ElseIf and Else clauses are optional.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • VBA function (VBA)

Example (as VBA Function)

The IF-THEN-ELSE statement can only be used in VBA code in Microsoft Excel.

Let's look at some Excel IF-THEN-ELSE statement function examples and explore how to use the IF-THEN-ELSE statement in Excel VBA code:

First, let's look at a simple example.

If LRegion ="N" Then
   LRegionName = "North"
End If

Next, let's look at an example that uses ElseIf.

If LRegion ="N" Then
   LRegionName = "North"

ElseIf LRegion = "S" Then
   LRegionName = "South"

ElseIf LRegion = "E" Then
   LRegionName = "East"

ElseIf LRegion = "W" Then
   LRegionName = "West"

End If

Finally, let's look at an example that uses Else.

If LRegion ="N" Then
   LRegionName = "North"

ElseIf LRegion = "S" Then
   LRegionName = "South"

ElseIf LRegion = "E" Then
   LRegionName = "East"

Else
   LRegionName = "West"

End If

Example#1 from Video

In the first video example, we are going to use the IF-THEN-ELSE statement to update cell C2 with "North", "South", "East" or "West" depending on the region code entered in cell A2.

So if we entered "N" in cell A2, we want "North" to appear in cell C2. If we entered "S" in cell A2, we want "South" to appear in cell C2, and so on.

Sub totn_if_example1()

   Dim LRegion As String
   Dim LRegionName As String

   LRegion = Range("A2").Value

   If LRegion = "N" Then
      LRegionName = "North"

   ElseIf LRegion = "S" Then
      LRegionName = "South"

   ElseIf LRegion = "E" Then
      LRegionName = "East"

   Else
      LRegionName = "West"
   End If

   Range("C2").Value = LRegionName

End Sub

Example#2 from Video

In the second video example, we have a list of students in column A and their corresponding grade in column B. We want to update the comment value in column C based on the grade in column B.

So a grade of "A" or "B" will have a corresponding comment value of "Great Work", a grade of "C" will have a comment of "Needs Improvement", and all other grades will have the comment "Time for a Tutor".

Sub totn_if_example2()

   For Each grade In Range("B2:B8")
      If grade = "A" Or grade = "B" Then
         grade.Offset(0, 1).Value = "Great work"

      ElseIf grade = "C" Then
         grade.Offset(0, 1).Value = "Needs Improvement"

      Else
         grade.Offset(0, 1).Value = "Time for a Tutor"
      End If

   Next grade

End Sub