an ultimate guide to learn about writing vba if statementIF statement is one the most important parts of VBA coding.

And, I am damn sure that after reading this guide, you will be able to write any type of conditional code by using this IF statement in VBA.

Let me tell you something.

Yesterday, I received a mail from one of my reader which was an eye opener for me.

In his mail, he was trying to push me to write more about VBA.

After reading his mail, I realized that I haven’t published any post on VBA from last two months.

And, then I decided to write about VBA, at least 3 times a month.

In this kilometer long guide, I have written each and everything about using IF in VBA.

Instant Access: Download this PDF guide about using IF statement in VBA.

Table of Content

  1. Quick Intro
  2. Syntax 
  3. Secret about writing conditions
  4. Examples

IF statement in VBA: A Quick Intro

I believe that you are already familiar with word IF and you are frequently using IF Function.

In VBA, IF statement works just like same, you can use it to create conditions in your code.

Its basic idea is to perform a task when a condition is TRUE else do nothing or do something else. You can write simple as well as complex conditional statements with IF in VBA.

For understanding purpose, I have split IF statement into three different parts.

  1. A condition to test.
  2. Task to perform if the condition is TRUE.
  3. Task to perform if the condition is FALSE.

using VBA IF statement code in excel

In above example, rain is a condition. If this condition is TRUE, the boy will open his umbrella and if the condition is FALSE he will wear his hat.

Conditions are everywhere in our day to day life. But now, let’s back to our coding world.

Syntax of IF Statement

You have three different types of IF in VBA. You can use any of the types according to your need.

  1. IF-Then
  2. IF-Then-Else
  3. IF-Then-Elseif-Else

IF-Then

If….then… statement is the simplest form of IF statement. All you have to do, specify a condition to check and if that condition is TRUE it will perform a task.

But, If that condition is FALSE it will do nothing and skip the line instantly.

IF-Then Syntax:

If condition Then statement[s]

In above syntax, we have to specify a condition to evaluate and a task to perform if that condition is TRUE.

IF-Then Example:

Sub CheckValue() 
If Range("A1").Value = 10 Then 
MsgBox ("Cell A1 has value 10")
End Sub

vba if statement using if then macro code

In above example, I have used If statement to verify that cell A1 has value 10 in it and if it has, the statement will show a message box with the message “Cell A1 has value 10”.

IF-Then-Else

You can use IF-Then-Else statement where you want to perform a specific task if a condition is TRUE and a different task if a condition is FALSE.

IF-Then-Else Syntax:

IF Condition Then
Statement[s]
Else
Statement[s]
End If

In above syntax, we can perform different tasks according to the result of a condition.

If the condition is TRUE then it will perform the statement which you have mentioned after “Then” or if the condition is FALSE it will perform the statement which you have mentioned after “Else”.

IF-Then-Else Example:

Sub CheckValue()
If Range("A1").Value = "10" Then
MsgBox ("Cell A1 has value 10")
Else
MsgBox ("Cell A1 has a value other than 10")
End Sub

vba if statement using if then else macro code

In above example, I have used the IF-Then-Else statement to check the value in cell A1.

If cell A1 has value 10, you will get a message box showing “Cell A1 has value 10” and if there is any other value in cell A1 you get a message box showing “Cell A1 has a value other than 10”.

So, here we are able to perform different tasks according to the result of the condition.

IF-Then-Elseif-Else

This is the most useful and important statement of IF in VBA which will help you to write advance condition statements.

In IF-Then-Elseif-Else statement, you can specify the second condition after evaluation your first condition.

IF-Then-Elseif-Else Syntax:

IF Condition Then
Statement[s]
Elseif  Condition Then
Statement[s]
Else
Statement[s]
End If

In above syntax, we have:

  1. A condition to evaluate.
  2. A statement to perform if that condition is TURE.
  3. If that condition is FALSE then we have the second condition to evaluate.
  4. And, if that the second condition is TRUE we have a statement to perform.
  5. But, if both conditions, first and second are FALSE then it will perform a statement which you have mentioned after “Else”.

And, the best part is you can use any number of “Elseif” in your code. That means you can specify any number of conditions in your statement.

IF-Then-Elseif-Else Example:

Sub CheckGrade()
If Range("A2").Value = "A" Then
MsgBox "Very Good"
ElseIf Range("A2").Value = "B" Then
MsgBox "Good"
ElseIf Range("A2").Value = "C" Then
MsgBox "Average"
ElseIf Range("A2").Value = "D" Then
MsgBox "Poor"
ElseIf Range("A2").Value = "E" Then
MsgBox "Very Poor"
Else
Msgbox "Enter Correct Grade"
End Sub

vba if statement using if then elseif else macro code

In above example, I have written a macro which will first check cell A2 for value “A” and if the cell has the grade “A”, the statement will return the message “Very Good”.

This statement will first check cell A2 for value “A” and if the cell has the grade “A”, the statement will return the message “Very Good”.

And, if the first condition is FALSE then it will evaluate the second condition and return the message “Good” if the cell has grade “B”.

And, if the second condition it false then it will go to the third condition and so on.

In the end, if all the five conditions are false it will run the code which I have written after Else.

Secret about writing IF in VBA

Now, you know about all the three types of IF statement in VBA and you are also able to choose one of them according to the task you have to perform.

But before I show some real life examples, let me tell you some secret for writing IF statement in VBA.

  1. One Line If Statement Vs. Block IF Statement
  2. Without Using Else

One Line IF statement Vs. Block IF statement

You can write IF statement in two different ways using one line statement or a block statement. Both have advantages and disadvantages.

different between one line statement and block statement in vba if statement

Let’s have a look.

One Line Statement: One line statement is perfect if you are using IF-Then statement. The basic to use one line statement is to write your entire code in one line.

One Line IF Statement Example:

If A1 = 10 Then Msgbox("Cell A1 has value 10")

In above statement, I have written IF statement to evaluate if cell A1 has value 10 then it will show a message box.

Best practice to use one line statement is when you have to write a simple code. Using one line code for complex and lengthy statements are hard to understand.

Please note while writing single line code you don’t have to use Endif to end the statement.

Block Statement: Block statement is perfect when you want to write your code in a decent and understandable way. When you writing block statement you can use multiple lines in your macro which give you a neat and clean code.

Block IF Statement Example:

Sub CheckValue()
If Range("A1").Value = "10" Then
MsgBox ("Cell A1 has value 10")
Else
MsgBox ("Cell A1 has a value other than 10")
End Sub

In above example, I have written an IF-Then-Else statement in blocks. And, you can see that it is easy to read and even easy to debug.

When you will write complex statements (which you will definitely do after reading this guide) using block statements are always good.

And, while writing nested If statements you can also add indentation in your line for more clarity.

Without Using Else

You have an exception that you can skip using Else in your code when you are using IF-Then-Elseif-Else.

This is very helpful when you do not need to perform any task when none of the conditions is TRUE in your statement.

Syntax If Statement Without Else

Sub CheckValue()
If Range("A1") = 0 Then
MsgBox "Value is Zero"
ElseIf Range("A1") < 0 Then
MsgBox "Value is Negative"
ElseIf Range("A1") > 0 Then
MsgBox "Value is Positive"
ElseIf Range("A1") = "" Then
MsgBox "Cell has no value"
End If
End Sub

In above example, I have written a condition to evaluate cell A1. If the cell has value 10, you will get a message showing “Value is Zero”.

And, If the cell has a negative value then you will get a message showing “Value is Negative”.

If the cell has a positive value then it will show a message box with the message “Value is Positive”. And, message “Cell has no value” if the cell has no value.

So, these are some secrets which you can use to write better codes. Now let’s move ahead to learn some real life examples.

Examples To Master IF

Here I have listed some simple but useful examples which you can follow along. These are also the most important uses of IF.

  1. Nesting If Statement
  2. Create Loop With If and Goto
  3. Check If Cell Contain A Number
  4. Using OR and AND With If Statement
  5. Using Not With IF Statement
  6. If Statement With Checkbox
  7. Check If Cell Is Merged
  8. Delete Row If Cell Is Blank

Example 1: Nesting If Statement

The best part of IF statement is you create nesting statements like IF function. You can add another If statement in an IF statement.

The basic Idea to use nesting IF Statements to apply more than one condition.

Nesting IF Statement Example:

writting nesting if with vba if statement

Sub NestIF()
Dim res As Long
res = MsgBox("Do you want to save this file?", vbYesNo, "Save File")
If res = vbYes Then 'start of first IF statement
If ActiveWorkbook.Saved <> True Then 'start of second IF statement.
ActiveWorkbook.Save
MsgBox ("Workbook Saved")
Else
MsgBox "This workbook is already saved"
End If 'end of second IF statement
Else
MsgBox "Make Sure to save it later"
End If ' end of first IF statement
End Sub

In above example, I have used a nested IF statement.

When you run this macro you will get a message box with OK and cancel option. Work of if statement starts after that.

First IF statement will evaluate that which button you have clicked. If you clicked “Yes” button then second If statement checks that your worksheet is saved or not.

If your workbook is not saved, it will save it and you will get a message after saving.

And, if the workbook is already saved it will show a message about that.

But, If you click no button the condition of the first macro will be FALSE and you will only get a message to save your book later.

The basic idea in above code is that the second condition is totally dependent on the first condition if the first condition is FALSE then the second condition will not get evaluated.

Example 2: Create Loop With If and Goto

You can also create a loop by using Goto with IF Statement. Most of the programmers avoid writing loops with IF and Goto as we have other neater ways to write codes in VBA.

  1. Debugging a code is not easy.
  2. It’s hard to understand complex codes.

But there is no harm to learn that how can you do this.

Loop With If and Goto Example:

Sub auto_open()
Alert:
If InputBox("Enter Username") <> "Puneet" Then
GoTo Alert
Else
MsgBox "Welcome"
End If
End Sub

In above example, I have used If statement to create a loop. I have use auto_open as the name of the macro so that whenever anyone opens the file it will show an input box.

You have to enter a username and if that username is not equal to “Puneet” if will repeat the code and show input box again.

And, If you enter right text then you will able to work in your worksheet.

Example 3: Check If Cell Contain A Number

Here I am using IF statement to check whether the active cell contains a numeric value.

using vba if statement to check number in cell

Sub CheckNumber()
If IsNumeric(ActiveCell) = True Then
MsgBox "Yes, Active Cell Has A Numeric Value"
Else
MsgBox "No Numeric Value In Active Cell"
End If
End Sub

In above example, I have written a condition by using the Isnumeric function in VBA which as same as worksheet Isnumber function to check whether the value in a cell in numeric or not.

If the value is a number it will return TRUE and you will get a message “Yes, Active Cell Has A Numeric Value”. And, if the value is non-number then you will get a message “No Numeric Value In Active Cell”.

Example 4: Using OR and AND With If Statement

You can also use IF Statement with OR operator and AND Operator to create a condition.

By using OR you can specify two or more conditions and perform a task if at least one condition is TRUE from all.

And, by using AND condition you can specify more than one condition and perform a task if  all the conditions are TRUE.

Sub UsingOR()
If Range("A1")< 70 Or Range("B1") < 70 Then
MsgBox "You Are Pass"
ElseIf Range("A1")< 40 And Range("B1") < 40 Then
MsgBox "You Are Pass"
Else
MsgBox "You Are Fail"
End Sub

In above example, in line 3 I have written two conditions using OR operator. If any student gets 70 marks in any of the subjects the result will be “Pass”.

And,  line 7 I have written two conditions using AND operator. If any student gets more than 40 marks in both of the subjects the result will be “Pass”.

Example 5: Using Not With IF Statement

By using NOT with IF statement you can change TRUE into FALSE and FALSE into TRUE.

Sub WithNot()
If Range("D1") <= 40 And Not Range("E1") = "E" Then
MsgBox "You Are Pass."
Else
MsgBox "You Are Fail"
End If
End Sub

In above example, I have written an If statement by using AND and NOT.

I have two cell with the subject score. In one cell score is in numbers and in another cell it has grades. If a student has marks above 40 in the first subject and above E grade in the second subject then he/she is passed else fail.

Now, here I am using AND to specify two statements in IF and NOT will convert the result of the second statement in FALSE if it is TRUE.

So every time when a student’s marks are more than 40 and grade other than E you will get a message ” You are Pass” else “You Fail”.

Example 6: If Statement With Checkbox

Now, here I am using IF Statement with a checkbox to run a macro.

using vba if statement with checkbox

Sub Ship_To_XL()
If Range("D15") = True Then
Range("D17:D21") = Range("C17:C21")
ElseIf Range("D15") = False Then
Range("D17:D21").ClearContents
Else: MsgBox ("Error!")
End If
End Sub

In above example, I have used If statement to create a condition that if the checkbox is tick mark then range D17:D21 is equal to range C17:C21. And, if the checkbox is not ticked then range D17:D21 will be blank.

Using this technique I can use billing address as shipping address and if I need something else I can enter the address manually.

Example 7: Check If Cell Is Merged

And here, I am using If statement to get an alert if active cell is merged.

check if a cell is merged using vba if statement

Sub MergeCellCheck()
If ActiveCell.MergeCells Then
MsgBox "Active Cell Is Merged"
Else
MsgBox "Active Cell Is Not Merged"
End If
End Sub

In above code, I have use merge cells property to check whether the active cell is a merged cell or not. If active cell is merged cell then condition will return an alert for that.

Example 8: Delete Row If Cell Is Blank

I am using IF Statement to check a row is blank or not. And, if that row is blank statement will delete that particular row.

Sub DeleteRow()
If Application.CountA(ActiveCell.EntireRow) = 0 Then
ActiveCell.EntireRow.Delete
Else
MsgBox Application.CountA(ActiveCell.EntireRow) & "Cell(s) have values in this row"
End If
End Sub

In above example, I f condition will first check for the cells which have value in it. If the count of cells with value is zero then the condition will delete the active row else return the alert showing the number of cells having value.

Conclusion

VBA IF Statement is most important part of macros. You can write some useful macros using VBA IF Statement.

You can write simple as well as complex codes using IF. You can also use logical operators with VBA IF Statement to make best out of it.

I hope this guide will help you to write code using IF Statement.

Are you confident about writing new codes using it?

And, If you are struggling with some of your codes or If you have any awesome code which you want to share with me, you can write in the comment box.



  • Great article, Puneet. Very Detailed. Best wishes.

    • Puneet Gogia

      Thanks Dinesh

  • vikas

    Very well explained.

    i have a question: is it possible to use AND & OR both ?

    • vikas

      i have a question: is it possible to use AND & OR both in same line ?

      • hi,
        yes it is – be aware of the fact that AND is executed first in this case. If you do want a predeceding OR to “win” you have to use brackets. e.g.
        If myCell = “A” Or myCell = “B” And yourCell < 0 Then …
        will react when B and yourCell is negative at the same time as well as when A disregarding yourCell
        If (myCell = "A" Or myCell = "B") And yourCell < 0 Then …
        will react when A or B and at the same time yourCell is negative
        Got it?

  • It’s really everything about If, Puneet – good description and easy-to-be-followed examples. I’ll recommend this to my trainees.
    Best Wishes
    Katharina