VBA IF Statement – Explained With Examples

IF is one of the most popular and frequently used statements in VBA. IF statement in VBA is sometimes also called as IF THEN ELSE Statement. The task of the IF Statement is to check if a particular condition is met or not.

If you have followed my earlier posts, then you would remember that we discussed If Function in Excel. The IF Function in Excel and the IF Statement in VBA are basically designed to do the same tasks, but the way they work is slightly different from each other.

Excel IF function checks a particular condition and if the condition is TRUE, it returns one value otherwise it returns the second value.

On the other hand, VBA IF Statement checks a condition but it doesn’t return any value. If the condition evaluates to TRUE then, it simply takes the program control to the instructions inside the IF block and starts executing them sequentially. However, if the condition evaluates to FALSE then it takes the program control to the statements inside the Else Block.

VBA IF Statement

Although, it is not mandatory to have an Else Block with every IF statement. In such a case, if the condition inside IF statement evaluates to FALSE then the program control just moves to the next instruction (the instruction after the IF Block) and starts executing them sequentially.

Recommended Reading: Nested IF’s in Excel

Syntax of VBA IF Statement

Now let’s see the syntax of the IF statement in VBA:

IF condition_1 THEN
'Instructions inside First IF Block
ELSEIF condition_2 Then
'Instructions inside ELSEIF Block
...
ELSEIF condition_n Then
'Instructions inside nth ELSEIF Block
ELSE
'Instructions inside Else Block
END IF

Here, ‘condition_1’ to ‘condition_n’ refers to the expression that must evaluate to a Boolean value (i.e. either it should be TRUE or it should be FALSE).

The ‘THEN’ keyword is basically a directive signifying that the instructions immediately following the IF Statement are to be executed if the condition evaluates to TRUE.

IF function usually ends with an ‘END IF’ statement which tells the application that it is the last line of the IF function.

How VBA IF Statement Works

The conditions along with the IF Statements will be evaluated sequentially. This means, first of all, the IF Statement with ‘condition_1’ will be evaluated, if it evaluates to TRUE then statements inside the first IF block will be executed and the rest of the blocks (ELSEIF’s and ELSE blocks) will be skipped.

But, if the First IF Statement evaluates to FALSE then the ELSEIF statement following it will be evaluated. If it evaluates to TRUE then the instructions inside the ELSEIF Block will be sequentially executed and the rest of the blocks (ELSEIF’s and ELSE blocks) will be skipped.

However, in case, it also evaluates to FALSE then the next ELSEIF statement will be evaluated and so on. Finally, if all the IF and ELSEIF’s evaluate to FALSE then the ELSE block will be executed.

Note: Remember that out of IF, ELSEIF’s, and ELSE code blocks; only a single code block will be executed at a time based on the condition.

How to Use IF Statement in VBA

Now let’s understand how to use the IF Statement in VBA.

Before preceding let’s make our objective very clear.

Objective: Here we will generate a random number between 1-10 and then our task is to identify if the generated number is less than 5, equal to 5 or greater than 5.

So, we will try to write a VBA program as:

Sub IF_Test()
Dim num As Integer
num = WorksheetFunction.RandBetween(1, 10)
If num > 5 Then
MsgBox num & " is greater than 5"
ElseIf num = 5 Then
MsgBox num & " is equal to 5"
Else
MsgBox num & " is less than 5"
End If
End Sub

Explanation: In the above code we have used the RandBetween function of Excel to generate any random number from 1 – 10. After this, we have used an IF statement to check whether this number is greater than 5, equal to 5, or less than 5.

Based on the generated number, any one of the three conditions will evaluate to TRUE, and a suitable message box will pop out.

How this code works in three conditions:

If the Random number is greater than 5: Let’s consider the random number generated is 7. The program starts from Line-1 and executes all the instructions sequentially till Line-4. When it reaches Line-4 it checks ‘If 7  > 5’, which is TRUE. So, it jumps to the instruction immediately beneath it and pops up a message saying “7 is greater than 5”. After this, it directly jumps to the Line-10 and comes out of the whole IF Statement.

If the Random number is equal to 5: Let’s consider the random number generated is 5. In this case, when the program control reaches Line-4, it checks ‘If 5  > 5’, which is FALSE. So, it skips the IF Block and jumps to the ELSEIF statement, here it checks ‘If 5 = 5’ which evaluates to TRUE. So, it pops up a message saying “5 is equal to 5”. (I know it’s a weird message, but I just used it for helping you to understand the things.)

After this, the program control directly jumps to Line-10, skips the ELSE part, and comes out of the whole IF statement.

If the Random number is less than 5: Let’s consider the random number generated is 3. So, In this case, when the program control reaches Line-4 it checks ‘If 3 > 5’, which is FALSE. So, it skips the IF Block and jumps to the ELSEIF block, here it checks ‘If 3 = 5’, which also evaluates to FALSE.

Now, as the above two blocks have evaluated to FALSE hence the ELSE block will be executed and pops out a message saying “3 is less than 5”. Later the program control jumps to Line-10 and ends the IF Statement.

Examples of VBA IF Statement

Now, let’s move to some examples of the IF Statement in VBA.

Example 1: Using less than '<' operator with the VBA IF Function

Write a program to check whether the number entered by the user is negative or not.

Below VBA code can accomplish this:

Sub Find_Negative()
On Error GoTo catch_error
Dim number As Integer
number = InputBox("Enter the number: ")
If number < 0 Then
MsgBox "Entered number is negative!"
Else
MsgBox "Entered number is positive!"
End If
Exit Sub
catch_error:
MsgBox "Oops, Some Error Occurred !"
End Sub

Explanation:

In this code, first of all, we are accepting input numbers from the user. And then we check whether that number is greater than zero or not. If the number is less than zero, then IF block is executed and a message is displayed to the user saying, “Entered number is negative!”.

But however, if the entered number is greater than zero then the program jumps to the Else block where it displays a message to the user saying, “Entered number is positive!”.

Example 2: Using less than '=' operator with the VBA IF Function

Write a VBA code to tell if the number entered by the user is Even or Odd.

Below is the code to do this:

Sub Find_Even_Odd()
On Error GoTo catch_error
Dim number As Integer
number = InputBox("Enter the number: ")
If number Mod 2 = 0 Then
MsgBox "Entered number is Even!"
Else
MsgBox "Entered number is Odd!"
End If
Exit Sub
catch_error:
MsgBox "Some Error Occurred"
End Sub

Explanation:

In this code, just like the previous example first of all we are accepting input numbers from the user. And then we check whether the Modulus of that number with 2 is zero or not. If the Modulus is zero that means the number is divisible by 2 and hence is Even.

But however, if the modulus result is non-zero that means the number is not perfectly divisible by 2 and hence it is Odd.

Example 3: Using other functions within the VBA IF Function

Write a program to check if the string entered by the user is Palindrome or not.

A Palindrome string is that which reads the same forward as it does backward for example level, civic, etc.

Below is the code to accomplish this task:

Sub Check_Palindrome()
On Error GoTo catch_error
Dim word As String
Dim Rev_Word As String
word = InputBox("Enter the string ")
Rev_Word = StrReverse(word)
If LCase(word) = LCase(Rev_Word) Then
MsgBox "Entered String is Palindrome !"
Else
MsgBox "Entered String is non Palindrome !"
End If
Exit Sub
catch_error:
MsgBox "Some Error Occured"
End Sub

Explanation:

The logic of this code is quite simple, first of all, we have asked the user to enter a text sting. And then with the use of VBA StrReverse a function (inbuilt function to reverse a text string), we have reversed the text string entered by the user.

Finally, we are matching both the strings i.e. user-entered string and reversed string in an IF statement to check whether both of them are the same or different. If both are the same that means the entered string is a palindrome.

Using IF statement with And & Or operators:

Logical operators make it possible for you to check multiple conditions at a time, inside a single IF statement. There are many logical operators in VBA like: And, Or, Not, AndAlso, OrElse, and Xor but in most cases, we only deal with the first three.

Note: All the above-mentioned operators are binary (i.e. they accept at least two operands) except NOT. NOT is unary because it takes a single operand.

Now, let’s have a look at their truth tables:

ConditionNOT Result
TrueFalse
FalseTrue

After seeing the above truth table you can clearly see that Not just returns the opposite logic of the condition.

Condition1Condition2AND ResultOR Result
TrueTrueTrueTrue
TrueFalseFalseTrue
FalseTrueFalseTrue
FalseFalseFalseFalse

See that AND only returns a TRUE value if both the conditions are TRUE. While OR returns TRUE if at-least any one of the two conditions is TRUE.

Now, let’s have a look at some examples of Logical Operators with IF Statement:

Example 4: Using OR Logical Operator With IF Statement in VBA

Write a program to ask the user his favorite color. If the color entered by the user is ‘White’ or ‘Black’, then display a message to tell him that you like the same color.

Below is the code to do this:

Sub Fav_Color()
On Error GoTo catch_error
Dim color As String
color = InputBox("Enter your favorite color: ")
If LCase(color) = "white" Or LCase(color) = "black" Then
MsgBox "Oh Really! I too like the same."
Else
MsgBox "Nice Choice"
End If
Exit Sub
catch_error:
MsgBox "Some Error Occurred"
End Sub

See how I have used Or operator to check the combination of multiple conditions in my program.

Example 5: Using AND Logical Operator With IF Statement in VBA

In the below table we have a Grade Table. Our task is to write a program that accepts Marks from user and displays the corresponding Grade.

Grade Table Imgae 1

Below is the code to accomplish this:

Sub Grade_Marks()
On Error GoTo catch_error
Dim Marks As Integer
Marks = InputBox("Enter your marks: ")
If Marks <= 100 And Marks >= 85 Then
MsgBox "Grade A"
ElseIf Marks < 85 And Marks >= 75 Then
MsgBox "Grade B"
ElseIf Marks < 75 And Marks >= 65 Then
MsgBox "Grade C"
ElseIf Marks < 65 And Marks >= 55 Then
MsgBox "Grade D"
ElseIf Marks < 55 And Marks >= 45 Then
MsgBox "Grade E"
ElseIf Marks < 45 Then
MsgBox "Fail"
End If
Exit Sub
catch_error:
MsgBox "Some Error Occurred"
End Sub

In this code see how I have used the AND operator to produce the required conditions.

Note: As a better coding practice it is always nice to use Select Case statements instead of writing multiple ELSEIF statements (just like we have seen in the above example). Select Case statements to execute faster and look cleaner than IF THEN ELSE.

Recommend Reading: Select Case Statement

So, this was all about VBA IF Statement. Do read this post if this long article has bored you and don’t forget to share your ideas and thoughts with us in the comments section.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.