VBA IF Statement – Explained With Examples

IF is one of the most popular and frequently used statement in VBA. IF statement in VBA is sometimes also called as IF THEN ELSE Statement. The task of 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 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 it 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 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 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 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 suitable message box will pop out.

How this code works in three conditions:

If 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 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 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.

3 Examples of VBA IF Statement:

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

Example 1: Write a simple 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 number 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: Write a VBA code to tell if the number entered by 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 number 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 number is not perfectly divisible by 2 and hence it is Odd.

Example 3: Write a simple program to check if the string entered by 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 user to enter a text sting. And then with the use of VBA StrReverse 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 same or different. If both are same that means the entered string is palindrome.

Using IF statement with And & Or operators:

Logical operators make it possible for you to check multiple condition 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:

Condition NOT Result
True False
False True

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

Condition1 Condition2 AND Result OR Result
True True True True
True False False True
False True False True
False False False False

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: Write a program to ask 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 combination of multiple conditions in my program.

Example 5: 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 above example). Select Case statements 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

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About