If you want to be an advanced VBA user then an IF statement is must learn. And, I believe that you are already familiar with the word IF and you are frequently using it as a worksheet function.
In VBA, IF works just like the same. Its basic idea is to perform a task when a condition is TRUE else do nothing or do something else. You can write simply as well as complex conditions.
For understanding purposes, I have split it into three different parts.
- A condition to test.
- A task to perform if the condition is TRUE.
- A task to perform if the condition is FALSE.
This is how it looks like in real life:
In the 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 and explore it.
Syntax : VBA IF
We have three different types of IF statements in VBA.
IF THEN is the simplest form of IF statement. All we need 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 condition Then statement[s]
In the above syntax, we have to specify a condition to evaluate and a task to perform if that condition is TRUE.
In the above example, we have used verified 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”.
Sub CheckValue() If Range("A1").Value = 10 Then MsgBox ("Cell A1 has value 10") End Sub
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 Condition Then Statement[s] Else Statement[s] End If
With the 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”.
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 the 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.
This is the most useful and important type of IF which will help you to write advanced condition statements. In this type, you can specify the second condition after evaluation your first condition.
IF Condition Then Statement[s] Elseif Condition Then Statement[s] Else Statement[s] End If
In above syntax, we have:
- A condition to evaluate.
- A statement to perform if that condition is TURE.
- If that condition is FALSE then we have the second condition to evaluate.
- And, if that the second condition is TRUE we have a statement to perform.
- 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.
Sub check_grade() If Range("A2").Value = "A" Then MsgBox "Very Good" Else If 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
In the above example, we 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 an IF statement in VBA
Now, you know about all the types of IF and you are also able to choose one of them according to the task you need to perform. Let me tell you a secret.
One Line IF statement Vs. Block IF statement
You can write an IF statements in two different ways and both have advantages and disadvantages. Have a look.
1. One Line Statement
One line statement is perfect if you are using the IF-Then statement. The basic to use one line statement is to write your entire code in one line.
If A1 = 10 Then Msgbox("Cell A1 has value 10")
In the above statement, we have written an IF statement to evaluate if cell A1 has a value of 10 then it will show a message box. The 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.
Quick Tip: While writing single line code you don’t need to use Endif to end the statement.
2. Block Statement
Block statement is perfect when you want to write your code in a decent and understandable way. When you writing a block statement you can use multiple lines in your macro which give you a neat and clean code.
Sub check_value() If Range(“A1”).Value = “10” Then MsgBox ("Cell A1 has value 10") Else MsgBox ("Cell A1 has a value other than 10") End If End Sub
In the above example, we 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.
Quick Tip – You have an exception that you can skip using Else at the end of 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.
8 Real Life Examples
Here I have listed some simple but useful examples which you can follow along.
1. Nested IF
The best part of IF statement is you create nesting statements. You can add a second condition in the first condition.
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.SaveMsgBox ("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 the above example, we have used a nested IF statement. When you run this macro you will get a message box with OK and Cancel option. Work of conditional statement starts after that.
First, it will evaluate that which button you have clicked. If you clicked “Yes” then nest it will evaluate that your worksheet is saved or not.
If your workbook is not saved, it will save it and you will get a message. 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 this 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.
More on Nested IF
2. Create Loop With IF and GoTo
You can also create a loop by using goto with IF. Most of the programmers avoid writing loops this way as we have better ways for a loop. But there is no harm to learn how we can do this.
Sub auto_open() Alert: If InputBox("Enter Username") <> "Puneet" Then GoTo Alert Else MsgBox "Welcome" End If End Sub
In the above example, we have used a condition statement to create a loop. We have used auto_open as the name of the macro so that whenever anyone opens the file it will run that macro.
The user needs to enter a username and if that username is not equal to “Puneet” it will repeat the code and show the input box again. And, if you enter the right text then he/she will be able to access the file.
3. Check if a Cell Contains a Number
Here we have used a condition to check whether the active cell contains a numeric value or not.
Sub check_number() If IsNumeric(Range("B2").Value) Then MsgBox "Yes, active cell has a number." Else MsgBox "No, active cell hasn't a number." End If End Sub
In the above example, I have written a condition by using the isnumeric function in VBA which is the same as the worksheet’s isnumber function to check whether the value in a cell is a number 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”.
4. Using OR and AND With IF
By using IF OR you can specify two or more conditions and perform a task if at least one condition is TRUE from all.
Sub UsingOR() If Range("A1") < 70 Or Range("B1") < 70 Then MsgBox "You Are Pass" Else If Range("A1") < 40 And Range("B1") < 40 Then MsgBox "You Are Pass" Else MsgBox "You Are Fail" End If End If End Sub
In the above example, in line 2, we have two conditions using the OR. If a student gets 70 marks in any of the subjects the result will be “Pass”. And on line 7, we have two conditions using the AND operator. If a student gets more than 40 marks in both of the subjects the result will be “Pass”.
By using the IF AND you can specify more than one condition and perform a task if all the conditions are TRUE.
5. Using Not With IF
By using NOT in a condition you can change TRUE into FALSE and FALSE into TRUE.
Sub IF_Not() If Range(“D1”) <= 40 And Not Range(“E1”) = “E” Then MsgBox "You Are Pass." Else MsgBox "You Are Fail." End If End Sub
In the above example, we have used NOT in the condition. We 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 a PASS.
- If a student has marks above 40 in the first subject and above E grade in the second subject then he/she is PASS.
So every time when a student’s marks are more than 40 and grade other than E we will get a message “You are Pass” else “You are Fail”.
6. IF Statement With a Checkbox
Now, here we are using a checkbox to run a macro.
Sub ship_as_bill() If Range("D15") = True Then Range("D17:D21") = Range("C17:C21") Else If Range(“D15”) = False Then Range("D17:D21").ClearContents Else MsgBox (“Error!”) End If End If End Sub
In the above example, we have used an IF statement to create a condition that if the checkbox is tick marked 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 we can use billing address as shipping address and if we need something else we can enter the address manually.
7. Check if a Cell is Merged
And here, we are writing a condition to get an alert if active cell is merged.
Sub MergeCellCheck() If ActiveCell.MergeCells Then MsgBox "Active Cell Is Merged" Else MsgBox "Active Cell Is Not Merged" End If End Sub
8. Delete the Entire Row if a Cell is Blank
Here we are using IF 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 the above example, it 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.
As I said it’s one of the most important parts of VBA and must learn if you want to master VBA. With IF statement, you can write simple codes as well as complex codes. You can also use logical operators and write nested conditions.
I hope this guide will help you to write better codes.
Now tell me this. Do you write conditions in VBA frequently? What kind of codes do you write? Please share your views with me in the comment section. And, please don’t forget to share this guide with your friends.
Related: Exit IF
- VBA Comment Block
- VBA Functions List + Examples
- VBA UDF
- Personal Macro Workbook
- VBA Message Box
- VBA Add Sheet
- Record a Macro in Excel
- VBA Option Explicit Statement