VBA IF Statements – Super Charge your Excel work

You are Learning below Topics:  

1. What are IF statements and how to write a IF statement in VBA?

2. Write IF AND Statements (Multiple Conditions)

3. Write IF OR Statements (Multiple Conditions)

4. Art of writing Nested IF Statements

If you want to be an advanced Excel VBA user then you must know how to simplify your work using IF statements. And, I believe that you are already using the IF Formula in Excel. If you did not use the IF formula in Excel let me try to make you understand the same with the syntax of the formula & basic example. ( if you already know your way around the IF formula in Excel, that’s awesome! 🌟 This article is here for everyone, whether you’re just starting out or you’re already a pro 🚀 )

=IF(logical_test, value_if_true, value_if_false)

Here’s a breakdown of the parameters:

• logical_test: This is the condition you want to evaluate. It can be a comparison, a calculation, or any expression that results in either TRUE or FALSE.

• value_if_true: If the logical test is TRUE, this is the value that will be returned.

• value_if_false: If the logical test is FALSE, this is the value that will be returned.

Let’s look at a practical example:

=IF(A1>10, “Greater than 10”, “Not greater than 10”)

In this example:

• Logical Test: It checks whether the value in cell A1 is greater than 10.

• Value If True: If the value in A1 is greater than 10, the formula returns “Greater than 10”.

• Value If False: If the value in A1 is not greater than 10, the formula returns “Not greater than 10”.

Now I am certain that you have a basic idea of how If condition works in Excel.

Although this article is on VBA, you must know about how the If formula works in Excel so that it will be easy for you to understand the same in VBA since 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 in complex conditions.

1.) IF Statement (Single criteria)

Let’s consider a real-life scenario involving an “IF” condition:

Imagine you’re deciding whether to go out for a walk or stay at home based on the weather. You might use an “if” condition to make your decision.

Syntax

IF (the weather is sunny) THEN

     (go out for a walk)

ELSE

    (stay at home)

In this scenario:

• Condition: The condition is the current weather.

• Decision: If the weather is sunny, you decide to go out for a walk. If it’s not sunny, you stay at home.

This simple “if” condition reflects a decision-making process in your daily life, similar to how programming languages use “if” conditions to execute different sets of instructions based on certain conditions being met or not met.

IF always have then and else keywords. In then we write when our condition has met and in else we write when our condition has not met.

I will give you one more example: Suppose, I want to delete a new sheet if is going to be the 10th sheet in excel. Because I want to keep only 9 sheets. So, it is possible. The point i am trying to make here is that IF statements are very powerful. They are not used to print something only. They can do real actions too.

IF (Total sheet count is 10) then

   delete the sheet

Else

   allow sheet insertion

End if

Note: Every IF has an End IF. 

---

Now let’s start some coding with the use of the VBA IF statement. 

• The Example: Checking for Even Numbers:

Let’s take a simple example. Consider you have the number 14, and you want to know if it’s an even number. In Excel VBA, you can achieve this using the modulo operator (%), which calculates the remainder when one number is divided by another. If the remainder is 0, the number is even.

The VBA Code:

Here’s the VBA code to accomplishes this task:

In this code:

• yourNumber is the variable representing the number you want to check.

• yourNumber Mod 2 calculates the remainder when divided by 2.

• The If statement compares the remainder to 0, determining if the number is even or odd.

• A message box pops up with the result

Result of the above code will be “The Number is even.

Quick Tip:

• Always close IF Statement with End If otherwise VBA will throw an error “Block If without End If” , although VBA errors are quite easy to understand, by merely reading the error you will understand the root cause of error.

• We can use the IF statement in the Excel VBA without giving Else conditions as well. In that case If the condition is false then code will perform the next set of instructions. If It sounds weird to you let understand this with the help of above example without giving Else condition.

---

VBA Code without Else or We can write IF Then also.

If you notice we did not use Else condition in above code but we will still get our desire result, Which is “The number is even”. Reason is, if there is nothing for us to do in a case when condition fails then we can avoid writing else. It is totally fine. Benefit is code is going to short as it will not have else line. Technically , nothing really so beneficial. However, I personally avoid writing else , if there is nothing to do in that section.

I hope now you understand how we can use VBA IF statements without giving Else conditions.

---

 

2. IF AND Statements:

Below code checks the eligibility of the students based on their scores in three subjects.

Condition in below code is to get eligible in all subjects, score should be greater than 80 if in any subject score is less than 80 then Not Eligible. In this type of scenario where we need to check if all conditions are true then we use AND keyword with IF statements.

In our example scores are as science = 99: english = 87: maths = 45 but condition is in all subjects score should be more than 80 so here in science score is 99 which is greater than 80 & in english as well score is 87 which is also greater than 80 but unfortunately 😓 in maths score is 45 which is less than 80 So our result will be “Not Eligible: Keep striving for excellence!”

So what we understand from this is we use AND with IF statements when we need to check if all conditions are true.

Here’s a breakdown of the logic:

• True AND True evaluates to True.

• True AND False evaluates to False.

• False AND True evaluates to False.

• False AND False evaluates to False.

VBA CODE

As we discussed above, the result of the above code will be “Not Eligible: Keep striving for excellence!” Since all conditions are not true.

---

 

IF OR Statements

Let’s take another example similar to above with some additional information to understand OR Keyword in IF statements. In OR conditions we check if any of the conditions is true, If any condition is True then the whole result will be True.

Here’s a breakdown of the logic:

• True Or True evaluates to True.

• True Or False evaluates to True.

• False Or True evaluates to True.

• False Or False evaluates to False.

Below code checks eligibility based on the average of three subjects and the age of the network.

Condition in below code is to get eligible average of three subjects should be greater than 75 & Age of Network is greater than 7 years. In this type of scenario where we need to check if any one single condition is  true then we use OR keyword with IF statements.

First we find the average of subjects by simply adding them & divide it by the number of subjects.

avgScore = (science + english + maths) / 3

avgScore = (99+80.5+45) / 3

avgScore = 74.83 & Age of network is taken 7

So our 1st condition is False as avgScore (74.83) is Less than 75 but our 2nd condition is True as AON (7) is greater than 5. So our result will be true since 1 condition is true.

So the result of the below code will be “Eligible: Congratulations! You meet the advanced criteria.”

VBA CODE:

As we discussed above, the result of the above code will be “Eligible: Congratulations! You meet the advanced criteria.”Since any single condition is true.

As I said, it’s one of the most important parts of VBA and you must learn if you want to master VBA. With the IF statement, you can write simple codes as well as complex codes. You can also use logical operators as we have discussed in detail.

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 website with your friends. We have tons of such articles for you on VBA, Excel, Powerquery, Mcode, Python. Happy Learning! Enjoy other articles too on Python, M code, BI, Dax, Excel, Access VBA etc.