Excel VBA IF Statements
IF statement in VBA code is one of the most frequently used which allows you to make a choice. IF function in VBA is different when compared with EXCEL IF function i.e. in Excel IF function or formula checks whether the given condition or criteria evaluates to TRUE or FALSE and then returns a value based on the evaluation, Whereas VBA IF statement will perform only the first half of statement, i.e. check if the condition evaluates to TRUE or FALSE, additionally THEN statement needs to be entered in VBA IF statement for remaining operation or task to perform.
Parameters of VBA IF Statements
Three parameters which you need to consider or keep in mind while writing VBA IF statement code.
- A condition or parameter to test.
- An operation or task to perform if the condition is TRUE.
- An operation or task to perform if the condition is FALSE
It is a decision-making statement that is used to execute a block of code.
It performs a task or operation when a condition is TRUE, else do nothing or do something else. Usually, an if statement consists of a Boolean expression followed by statements (It may be one or many).
Simple to the complex condition can be written in VBA IF statement.
Below shown are the different types of VBA IF Statements with their syntax and examples.
| IF Statement Types | Syntax |
| IF THEN | If <Condition> Then [Statement]
End if |
| IF ELSE | If <Condition> Then [Statement1]
Else: [Statement2]End if |
| IF ELSEIF | IF <Condition1> Then
[Statement1]
ELSEIF <Condition2>Then [Statement2]End If |
| ELSE AND ELSEIF
(ELSEIF statement must come first, after that ELSE) |
IF [condition1 is true] Then
[Statement1]
ELSEIF [condition2 is true] Then [Statement2]ELSE [Statement3]End If |
| IF WITHOUT ENDIF
(single line code only) Used when ELSE or ELSE IF statement is not used |
IF [condition is true] Then [Statement] |
Apart from the above statement, NESTED IF statement can also be used, i.e. An if or ELSEIF statement inside another if or ELSEIF statement.
How to Use IF Statements in Excel VBA?
We will learn the types and how to use VBA IF Statements with few examples in Excel.
- Open a VB Editor window. Select or click on Visual Basic in the Code group on the Developer tab or you can directly click on Alt + F11 shortcut key.
- This will take us to Visual Basic coding window. Now go to the Insert menu from VBA window and select Module as shown below.
- Now the blank module is created, it is also called a code window, where you can start writing if statement codes.
VBA IF THEN Statement – Example #1
IF THEN is a simple form of VBA statement. The format to write a code is:
If <condition is true> Then <Statement>
You should use matching End If statement after entering the above syntax, When the condition meets or criteria evaluates to true, then all the lines between If Then and End If are processed.
When you type Sub IF_THEN() as the first message without any quotes around it, you can observe, Excel automatically adds the line End Sub below the first message line when you press Enter. Now, all the codes which you enter must be between these two lines.
Code:
Sub IF_THEN() If 3 > 2 Then MsgBox "3 is greater than 2" End If End Sub
Let’s check out with the above code which has a single line of IF statement that verifies or checks the value 3.
If the value “3” is greater than “2″, then popup message appears with message i.e. “3 is greater than 2”. If you change the value from “2” to “5” in the VBA code and run it, then nothing happens. Because 3 is not greater than 5.
You can run the macro by clicking the Run Sub button or by pressing F5. The VBA message box popup window appears with message stating “3 is greater than 2”
VBA IF-THEN-ELSE Statement – Example #2
In this scenario, IF & ELSE statement is used to execute two different conditions. The format or syntax to write code is:
If <Condition> Then
Statement1
Else:
Statement2
End if
IF you run a below-mentioned code, i.e. If the cell B4 contains a value 7, then you will get a message box showing “Cell B4 has value 7” and If the cell B4 contains a value other than 7, then you will get a message box showing “Cell B4 has a value other than 7”.
Here, based on the value present in the cell, different result occurs (i.e. more than one condition).
Code:
Sub IF_THEN_ELSE() If Range("B4").Value = "7" Then MsgBox "Cell B4 has value 7" Else MsgBox "Cell B4 has a value other than 7" End If End Sub
Now, the cell “B4” contains a value 7, if I run above code, the 1st condition appears.
Suppose, I changed the value of cell “B4” from 7 to 5 as shown below.
Code:
Sub IF_THEN_ELSE() If Range("B4").Value = "5" Then MsgBox "Cell B4 has value 7" Else MsgBox "Cell B4 has a value other than 7" End If End Sub
So if I run above VBA IF-THEN-ELSE code, the second condition appears.
IF-THEN-ELSEIF-ELSE – Example #3
It’s an advanced form of VBA if statement, the format or syntax to write a code is
IF [condition1 is true] Then
[Statement1]ELSEIF [condition2 is true] Then
[Statement2]ELSE
[Statement3]End If
The advantage of this code is, ELSE IF can be used singly or multiple times ( 10 or 100 times or more than that) as per your requirement.
Code:
Sub IF_THEN_ELSEIF_ELSE() If 5 > 8 Then MsgBox "5 is greater than 8" ElseIf 6 > 8 Then MsgBox "6 is greater than 8" ElseIf 7 > 8 Then MsgBox "7 is greater than 8" Else MsgBox "5, 6 or 7 is lesser than 8" End If End Sub
Let’s check out how the above code works, Excel will initiate or process the first statement, when it notices, that it is false, then it will move on to the next one. Here all the ElseIf arguments or condition is false, therefore it will move on till the condition is true and at last result of the Else argument is displayed as a final result of the IF statement. i.e. MsgBox “5, 6 or 7 is lesser than 8” which is a TRUE argument.
When you select & run the code by clicking the Run Sub button or by pressing F5. The VBA message box popup window with a message stating “5, 6 or 7 is lesser than 8” which is a true argument
Now, you can save this macro codes, by renaming as VBA_IF_STATEMENT under the name section of the properties tab
Things To Remember
- When the single line of IF Then statement is used, then no need to use End IF at last. But when we split it into more than one line, then End If statement has to be used.
- In the VBA, IF-THEN-ELSEIF-ELSE statement, ElseIf arguments should be always placed before the Else argument.
Recommended Articles
This is a guide to VBA IF Statements. Here we discussed how to use IF statements in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles–
