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
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.
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.
- First, go to the Developer Tab and then select Visual Basic.
- 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.
- 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.
- Thereafter, select the Macro Example_If and click OK.
- Now, type Z in B5 and click on the button.
- 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.
Read More: How to Use Do Until Loop in Excel VBA (with 2 Examples)
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
The macro will return the sum of the squared numbers from 1 to 10.
Code Explanation (Line by Line)
- Sub SumOfSquaredNumber(): This statement starts the macro Sub Procedure.
- Total = 0: Total is a variable. We assign value 0 in this variable with this statement.
- 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.
- 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).
- 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.
- 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).
- 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.
- 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).
- In this way, the statement Total = Total + (Num ^ 2) will be executed until Num’s value is equal to 10.
- MsgBox Total: This statement shows a pop-up dialog box with the value of Total (385).
- 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 For Each Loop in Excel VBA (3 Suitable Examples)
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.
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.
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
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.
Read More: How to Use Do While Loop in Excel VBA (3 Examples)
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
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
Read More: How to Use For Next Loop in Excel VBA (with 5 Examples)
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.