How to Use VBA Conditional Statements in Excel (4 Examples)

The article will show you some useful applications of Conditional Statements in Excel VBA. If you want to operate an Excel operation based on conditions, you must know about these Statements of VBA. There are basically four Conditional Statements that we often use in Excel. They are the If Statement, With Statement, For Loop, and Case Select. These constructs are used to control the flow of execution. In this article, we shall introduce you to a few common VBA constructs. Let’s stick to this article to understand the basic idea of how to use them properly.

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide

Read More: How to Use the For Each Next Loop in Excel VBA


Download Practice Workbook


Conditional Operators

You may know about these operators as we frequently used them in mathematics. Conditional operators are the equal (=), greater than (>), less than (<), etc. symbols. These symbols are used to apply conditions in VBA codes with the Conditional Statements.


4 Basic Examples to Use VBA Conditional Statements in Excel

When you apply a condition to get a definite result, you need these Conditional Statements. Suppose, you want to buy a smartphone and you are browsing for a phone that suits you and classifies them by their prices. The price range here is the condition and applying this condition to VBA, you can easily classify your products.

excel vba conditional statements


1. Applying VBA Conditional IF Statement in Excel

If-Then is one of the most important control structures in VBA. With this construct, VBA applications can decide which statements to execute. The basic syntax of the If-Then structure is:
If condition Then statements [Else elsestatements]

In simple language, we can say, if a condition is true, then a group of statements will be executed. If you include the Else clause, then another group of statements will be executed if the condition is not true.

In this section, you will see the use of the IF Statement to insert a specific word based on a condition. Let’s go through the process below for a better understanding.

Steps:

  • First, go to the Developer Tab and then select Visual Basic.

Applying VBA Conditional IF Statement in Excel

  • After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

  • Now, type the following code in the VBA Module.
Sub Example_If()
   If Range("B5").Value = "Z" Then
    Range("C5").Value = "Zoo"
   End If
End Sub

If you type Z in B5 and run the Macro, it will return Zoo in C5.

  • Next, go back to your sheet, type Z in B5, and run the Macro named Example_If as it is the name of our current Macro.

Applying VBA Conditional IF Statement in Excel

  • After that, you will see Zoo in cell C5.

Let’s make the process a little bit more convenient.

  • Select Developer >> Insert >> Button.

  • Next, draw the button anywhere on the Excel sheet and give it a name. Here I named the button Full Word’.
  • After that, right click on the button and select Assign Macro.

Applying VBA Conditional IF Statement in Excel

  • Thereafter, select the Macro Example_If and click OK.

  • Now, type Z in B5 and click on the button.

Applying VBA Conditional IF Statement in Excel

  • After that, you will see ‘Zoo’ in C5.

This is a simple example of how to use a Conditional IF Statement in VBA.

In the following description, I’ll show you an example of how to use the Nested IF Statement. This example will also illustrate how to operate the ELSEIF Statement.

  • Type the following code in the VBA Module.
Sub Example_IfElseif()
   Dim mn_letter As String
   Dim mn_FullWord As String
   mn_letter = Range("B5").Value
   If mn_letter = "Z" Then
      mn_FullWord = "Zoo"
   ElseIf mn_letter = "E" Then
      mn_FullWord = "Excel"
   ElseIf mn_letter = "F" Then
      mn_FullWord = "Football"
   End If
   Range("C5").Value = mn_FullWord
End Sub

You can see that the above Macro uses ElseIf Statements. When you type Z in B5 and run it then it will return Zoo. If you type E, it will return Excel, and so on. The Macro uses Range.Value property to return these values.

  • Next, go back to your sheet and assign this Macro to a new button.
  • After that, type E or any other letter that is mentioned in the Macro and click on the button. You will see Excel for E.

The ELSEIF Statement helps you to apply new conditions in VBA.


2. Using FOR Loop as Conditional Statement 

In this section, I’ll show you an example of how we can use FOR Loop as a Conditional Statement. Let’s stick to the description below.

Steps:

  • First, follow the steps of Method 1 to open the VBA Module.
  • Next, type the following code in the module.
Sub Example_ForLoop()
   mnTotal = 0
   For mn_Num = 1 To 10
      mnTotal = mnTotal + (mn_Num ^ 2)
   Next mn_Num
   MsgBox mnTotal
End Sub

Using FOR Loop as Conditional Statement 

The macro will return the sum of the squared numbers from 1 to 10.

Code Explanation (Line by Line)

  1. Sub SumOfSquaredNumber(): This statement starts the macro Sub Procedure.
  2. Total = 0: Total is a variable. We assign value 0 in this variable with this statement.
  3. For Num = 1 To 10: Num is also a variable and it is assigned now value 1. Excel checks Num’s assigned value (now, 1) with Num’s limiting value (here, 10). If Num’s assigned value is less than or equal to Num’s limiting value, then the statement between For and Next will execute.
  4. Total = Total + (Num ^ 2): Num variable’s value (right now 1) will be squared here at first. Excel then sums the square value of Num (1) and the value of Total (0). The result of the sum will be assigned to Total again. Finally Total variable now holds: 1 (0+1=1).
  5. Next Num: This statement increases the value of the Num variable by 1. So Num’s value is now 1+1=2. Excel now checks again Num’s assigned value (2) with Num’s limiting value (10). As the assigned value is less than the limiting value, the statement Total = Total + (Num ^ 2) executes again.
  6. Total = Total + (Num ^ 2): This statement now executes again. Num variable’s value (right now 2) will be squared here at first. Excel then sums the square value of Num (4) and the value of Total (1). The result of the sum will be assigned to Total again. Finally Total variable now holds 5 (4+1=5).
  7. Next Num: This statement increases the value of the Num variable by 1. So Num’s value is now 2+1=3. Excel now checks again Num’s assigned value (3) with Num’s limiting value (10). As the assigned value is less than the limiting value, the statement Total = Total + (Num ^ 2) executes again.
  8. Total = Total + (Num ^ 2): This statement now executes again. Num variable’s value (right now 3) will be squared here at first. Excel then sums the square value of Num (9) and the value of Total (5). The result of the sum will be assigned to Total again. Finally Total variable now holds 14 (9+5=14).
  9. In this way, the statement Total = Total + (Num ^ 2) will be executed until Num’s value is equal to 10.
  10. MsgBox Total: This statement shows a pop-up dialog box with the value of Total (385).
  11. End Sub: This statement ends the macro subprocedure.
  • After that, run the Macro.
  • You will see a message box showing the result.

This is another example of a Conditional Statement in Excel VBA.

Read More: How to Use Do Until Loop in Excel VBA

Read More: For-Next loops in Excel


3. Implementing WITH END Structure as Conditional Statement

This example will show you how to apply VBA WITH END Structure as a Conditional Statement. Say, we want to middle-align the cell contents of an Excel sheet. Let’s stick to the description below to see how to do it with a WITH Statement.

Implementing WITH END Structure as Conditional Statement

Steps:

  • First, follow the steps of Method 1 to open the VBA Module.
  • Next, type the following code in the module.
Sub Example_WithStatement()
   With Selection
     .HorizontalAlignment = xlCenter
     .VerticalAlignment = xlCenter
     .WrapText = False
     .Orientation = xlHorizontal
   End With
End Sub

The macro will return the sum of the squared numbers from 1 to 10.

  • After that, go back to your sheet and run the Macro named Example_WithStatement.

  • Next, you will see the cell contents are now middle-aligned.

Implementing WITH END Structure as Conditional Statement

This is another example of a Conditional Statement in Excel VBA.


4. Using SELECT CASE Structure as VBA Conditional Statement

This example will show you how to apply VBA SELECT CASE Structure as a Conditional Statement. The execution of this Macro will return the same result we got in Method 1. Please continue with the description.

Steps:

  • First, follow the steps of Method 1 to open the VBA Module.
  • Next, type the following code in the module.
Sub Example_Case()
Dim mn_letter As String
Dim mn_FullWord As String
mn_letter = Range("B5").Value
Select Case mn_letter
   Case "Z"
      mn_FullWord = "Zoo"
   Case "E"
      mn_FullWord = "Excel"
   Case "F"
      mn_FullWord = "Football"
   End Select
   Range("C5").Value = mn_FullWord
End Sub

Using SELECT CASE Structure as VBA Conditional Statement

The Macro will work the same as the second Macro we created in Method 1. Please follow this link to see the input and output processes of Method 1

This is another example of a Conditional Statement in Excel VBA.


Applying Excel VBA Conditional Statements for Multiple Conditions

In this section, you will see an exclusive example of using VBA Conditional Statements. Suppose, you want to buy a smartphone and you are browsing for a phone that suits you and classifies them by their prices. Let’s see the process below to see how we can do this.

Steps:

  • First, follow the steps of Method 1 to open a VBA Module.
  • Next, type the following code in the VBA Module.
Sub Example_IfElse()
   For Each mn_price In Range("C5:C10")
      If mn_price > 500 Then
         mn_price.Offset(0, 1).Value = "Overpriced"
      ElseIf mn_price > 200 And mn_price <= 500 Then
         mn_price.Offset(0, 1).Value = "Medium Price"
      Else
         mn_price.Offset(0, 1).Value = "Lower Priced"
      End If
   Next mn_price
End Sub

Applying Excel VBA Conditional Statements for Multiple Conditions

The Macro will return “Overpriced” if the price exceeds 500 dollars, “Medium Price” if the price is between 200 bucks to 500 bucks, and “Lower Priced” otherwise.

  • Later, go back to your sheet and run the Macro named Example_IfElse.

  • After that, you will see your smartphones are classified by their price.

You can also achieve the same result using the SELECT CASE Structure too. Use the following code.

Sub Example_MultipleCaseSelect()
   For Each mn_price In Range("C5:C10")
      Select Case mn_price
      Case Is > 500
         mn_price.Offset(0, 1).Value = "Overpriced"
      Case Is > 200 And mn_price <= 500
         mn_price.Offset(0, 1).Value = "Medium Price"
      Case Is <= 200
         mn_price.Offset(0, 1).Value = "Lower Priced"
      End Select
   Next mn_price
End Sub


Conclusion

Here, I showed you some basic examples of how to use Conditional Statements in Excel VBA. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.

Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo