Sometimes you don’t want every part of your code to be executed as you run your macro. In almost every computer language. In VBA, you can find IF .. ELSEIF .. ELSE statement or SELECT .. CASE.
In this lesson, I’ll show you how you can use the IF statement in VBA.
If .. Then
You can use the If statement if you want to check a condition and execute that part of the code only if the condition is met. You can close the If statement with an End If line.
1 2 3 4 5 6 7 8 |
Sub SingleIf() Dim age As Integer age = 18 If age >= 18 Then MsgBox "This person is an adult" End If End Sub |
The condition (age >= 18) is met therefore the code between If and End If is executed and the message is displayed.
If you change the age variable to a number smaller than 18, Excel will check the condition, and because the condition is not met it won’t do anything.
A single line IF
In the previous example, I showed you that you have to end your if statement with the End If line. That’s true unless you write your statement inside a single line.
Let’s modify our example a bit.
1 2 3 4 5 6 |
Sub SingleLineIf() Dim age As Integer age = 18 If age >= 18 Then MsgBox "This person is an adult" End Sub |
Single line statement can be more readable if you use multiple Ifs with a short single line code to execute.
Multiple Ifs
You can use multiple Ifs if you want to execute multiple parts of code, and each If has to be closed with End If. Here’s what I mean.
1 2 3 4 5 6 7 8 9 10 11 |
Sub MultipleIfs() Dim age As Integer age = 77 If age >= 18 Then MsgBox "This person is an adult" End If If age >= 65 Then MsgBox "This person is a retiree" End If End Sub |
In this case, both conditions are met, therefore Excel will display both messages. If you have multiple Ifs, it’s a good idea to use the SELECT .. CASE statement.
If .. Then .. Else
The If statement allows us to check a single condition. If the condition is met (returns TRUE), the particular code is executed.
But what if the statement returns FALSE and, in this case, you want to execute a different part of a code?
In this case, you can use ELSE statement. This part of code will execute if the condition is not met. Let’s take a look.
1 2 3 4 5 6 7 8 9 10 |
Sub IfElse() Dim age As Integer age = 16 If age >= 18 Then MsgBox "This person is an adult" Else MsgBox "This person is not an adult" End If End Sub |
Excel checks whether the condition is met. In our case, 16 >= 18 returns FALSE, therefore program will execute code that is inside the Else statement.
If .. Then .. ElseIf
So far you’ve learned about If and Else. In this part, you are going to learn about ElseIf, which allows creating more complicated conditions.
It works in the following way. First Excel checks the If condition. If the condition is not met (and only then) it checks the ElseIf condition. If the condition is still not met, it returns the Else statement. You can use multiple ElseIf statements, but only a single If or Else in a single if statement.
Let’s find out using the following example.
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub IfElseIfElse() Dim age As Integer age = 11 If age < 18 Then MsgBox "This person is underage" ElseIf age >= 65 Then MsgBox "This person is a retiree" Else MsgBox "This person is an employee" End If End Sub |
This code checks whether a person is underage. If the condition is met it returns the message. If the condition is not met if checks the next condition – if a person is over 65. If the condition returns TRUE it returns the next message. If both conditions are not met, it returns the last message inside Else.
IF with logical operators
NOT
In our example, we used the following condition:
age >= 18
If you want to select people who are younger than 18, you can use this statement: age < 18, or you can use negation.
With negation, our example looks like this.
1 2 3 4 5 6 |
Sub SingleIfNot() Dim age As Integer age = 18 If Not age >= 18 Then MsgBox "This person is not an adult" End Sub |
In the above example, no code will be executed because it makes our condition negative.
1 2 3 4 5 6 |
Sub SingleIfNot2() Dim age As Integer age = 16 If Not age >= 18 Then MsgBox "This person is not an adult" End Sub |
After you changed our variable to be smaller than 18, the message is going to be displayed.
OR and AND
Besides NOT, you can also use AND and OR.
Let’s modify our example.
First, we are going to use AND.
1 2 3 4 5 6 7 8 |
Sub IfAnd() Dim age As Integer age = 56 If age >= 18 And age <= 65 Then MsgBox "This person is an adult and employee" End If End Sub |
In this example, both conditions have to return TRUE in order to display the message. In other words, the condition is met if the age variable is between 18 and 65.
Nesting IF and ELSEIF (intermediate)
You can find that one If the statement is nested inside another If statement. It’s not only possible, but you can find that this is a common practice.
I’ll illustrate it in the following example.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Sub Fruits() Dim discount As Single Dim amount As Integer Dim fruit As String fruit = "apple" amount = 120 If fruit = "banana" Then If amount > 100 Then discount = 0.1 ElseIf amount > 10 Then discount = 0.05 End If MsgBox "You bought " + CStr(amount) + " banana(s) with " + CStr(discount * 100) + "% discount" ElseIf fruit = "orange" Then If amount > 100 Then discount = 0.2 ElseIf amount > 10 Then discount = 0.1 End If MsgBox "You bought " + CStr(amount) + " orange(s) with " + CStr(discount * 100) + "% discount" ElseIf fruit = "apple" Then If amount > 100 Then discount = 0.4 ElseIf amount > 10 Then discount = 0.2 End If MsgBox "You bought " + CStr(amount) + " apple(s) with " + CStr(discount * 100) + "% discount" Else MsgBox "You didn't buy anything" End If End Sub |
In the first If statement this code checks what kind of fruit you are buying. If there are other If statements inside, it checks how many fruits you are buying and gives you a discount.
The IIf function
There is no typo. There is a function in VBA, called IIf. This function is not as popular as the If statement, but it exists, so I decided that it should be mentioned.
It’s similar to ternary operators from other programming languages.
With this function, you can make your code even shorter than with single-line If statements.
This is our If .. Else example.
1 2 3 4 5 6 7 8 9 10 |
Sub IfElse() Dim age As Integer age = 16 If age >= 18 Then MsgBox "This person is an adult" Else MsgBox "This person is a youngster" End If End Sub |
Let’s modify it in order to use the IIf function.
1 2 3 4 5 6 |
Sub IfElse() Dim age As Integer age = 16 MsgBox IIf(age >= 18, "This person is an adult", "This person is a youngster") End Sub |
It’s going to return this message.