VBA in Excel stands for Visual Basic for Applications which is Microsoft’s programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend.
In this article, we are going to use how to use the If statement in Excel VBA.
Implementation
In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available.
The Developer Tab can be enabled easily by a two-step process :
- Right-click on any of the existing tabs at the top of the Excel window.
- Now select Customize the Ribbon from the pop-down menu.
- In the Excel Options Box, check the box Developer to enable it and click on OK.
- Now, the Developer Tab is visible.
Now click on the Visual Basic option in the Developer tab and make a new module to write the program using the Select Case statement.
Developer -> Visual Basic -> Tools -> Macros
- Now create a Macro and give any suitable name.
- This will open the Editor window where can write the code.
VBA IF Statement
The syntax is :
If condition/expression Then
Code Block for True value
Flow Diagram :
Example: Consider a company that wants to hire employees for a certain role. The company kept eligibility criteria as the age of the person. The age of the candidate who can apply for this interview must be less than 27 years.
Sub Allocate_Employee()
‘Declaring and initializing the variable age
Dim age As Integer
age = 30
If age >= 27 Then
MsgBox “You are not eligible for this post.”
End If
End Sub
Output:
Since, the age is 30, the IF condition becomes TRUE and the code block inside IF statement executes.
Related Topic: Record Macros in Excel
IF Then Statements in Excel
VBA If-Then Statements serve as conditional checks to determine whether expressions are TRUE or FAlse, enabling the execution of different sets of code based on the evaluation.
Let’s delve into a straightforward example to better understand this concept:
If Range(“A2”).Value >0 Then Range(“B2”).Value = “Positive”
In this case, the code tests whether the value within Range A2 is greater than zero. If this condition holds true, the code proceeds to set the value of Range B2 as “Positive”.
.png)
Note: When assessing conditions, we employ comparison operators such as =,>,<,<>,<=, and >=. A more detailed exploration of these operators will follow later in this article.
Here’s the syntax for a concise single-line If statement:
If [test_expressions] Then [action]
To enhance readability, you can utilize a line continuation character(underscore) to spread the If statement across two lines, as demonstrated in the image above:
If [test_expression] then_
[action]
If Range(“a2”).Value >0 Then _
Range(“b2”).Value = “Positive”
End If Statements
The previously illustrated “Single-line” If statement effectively addresses scenarios where you are testing a single condition. However, as your IF Statements grow more intricate, involving multiple conditions, it becomes imperative to incorporate an “End If” Statement to delineate the conclusion of the conditional logic.
For example, observe the following code:
If Range(“A2”).Value >0 Then
Range(“B2”).Value = “Positive”
End If
.png)
Syntax:
If [test_expression] then
[action]
End If
Then End If signifies the end of the if statement.
Using ElseIf with the VBA If Statement -Multiple Conditions
To address more nuanced scenarios involving multiple conditions, the ElseIf and Else Statements become integral components within your VBA code. The ElseIf statement supplements an existing If statement by assessing a condition only if the preceding conditions have not been met.
Consider the following illustration, where we expand upon the initial example of testing for a positive cell value by incorporating an ElseIf condition to determine i the value is negative:
If Range(“A2”).Value>0 Then
Range(“B2″).Value =”Positive”
ElseIf Range(“A2”).Value <0 Then
Range(“B2″).Value =” Negative”
End If
.png)
Multiple ElseIf statements can be employed to accommodate varying conditions, enhancing the decision-making process. Take a look at this code snippet to comprehend its application:
Sub If_Multiple_Conditions()
If Range(“AA2″).Value =”Cat” Then
Range(“B2”).Value = “Kitten”
ElseIf Range (“A2”). Value = “Dog” Then
Range (“A2”).Value = “Puppy”
ElseIf Range (“A2”). Value = “Duck” Then
Range(“B2”).Value = “Duckling”
End If
End Sub
.png)
By skillfully integrating ElseIf and Else statements, you adeptly navigate intricate decision paths within your VBA code, enabling you to respond dynamically to diverse scenarios and fostering heightened control and versatility in your Excel projects.
Using Else With the VBA If Statement
For example, the Else statement steps in when neither the positive nor negative conditions hold true, indicating that the cell value must be zero. It serves as a vital catch-all mechanism, ensuring that a definitive outcome is assigned even when other specific conditions are unmet.
By incorporating the Else statement, you bestow your VBA code with the adaptability to handle a comprehensive spectrum of possibilities. Whether it’s dealing with unexpected inputs, exceptional scenarios, or default outcomes, the Else statement empowers your code to gracefully manage diverse circumstances and deliver meaningful results, enhancing the robustness and effectiveness of your Excel applications.
If Range(“a2”). Value>0 Then
Range(“b2”). Value = “Positive”
ElseIf Range(“a2”).Value< 0 Then
Range(“b2”).Value = “Negative”
Else
Range(“b2”).Value = “Zero”
End If
.png)
Using If-Else Statement in VBA
Among the fundamental constructs of VBA, the If-Else statement stands out as a versatile tool for decision-making within your Excel macros. This construct enables your code to assess conditions and respond dynamically, fostering tailored outcomes based on varying scenarios.
In a basic yet illustrative example, consider the following VBA code:
Sub If_Else()
If Range(“A2”).Value>0 Then
Range (“B2”).Value = “Positive”
Else
Range(“B2”).Value = “Not Positive”
End If
End Sub
.png)
In this code, the If-Else statements evaluate the value within Rabge A2. If the condition “Range(“A2″). Vaalue>0” holds true, it assigns “positive” to Range B2. Conversely, if the condition is false, it designates “Not Positive” to the same cell.
The If-Else statement embodies the essence of dynamic decision-making, enabling your VBA code to adapt and react based on real-time data. Its significance becomes pronounced as you tackle more intricate tasks, allowing you to handle diverse scenarios, and optimize the workflows, and functionality of your Excel spreadsheets.
By integrating If- Else statements into your VBA toolkit, you empower your macros to make informed choices and deliver relevant outcomes, thereby elevating the precision, efficiency, and intelligence of your Excel projects.
Using Nested IFs Statements
Venturing beyond the confines of basic decision-making, VBA equips you with the capability to construct nested IF statements-empowering your code to explore multiple layers of conditions and responses.
The concept of nested IF statements is exemplified through the following VBA code:
Sub Nested_Ifs()
If Range(“A2”).Value >0 Then
Range(“B2″).Value =”Positive”
Else
If Range(“A2”).Value <0 Then
Range(“B2″).Value =”Negative”
Else
Range(“b2”).Value= “Zero”
End If
End If
End Sub
.png)
In this example, a hierarchy of conditions is evaluated. Initially, the code assesses if the value within Range A2 is greater than Zero. Should this condition prove true,” Positive” is assigned to Range B2. In instances where the initial condition is False, the code then delves into a secondary nested IF statement. Here, it checks if the value is less than zero, and if so, assigns “Negative” to Range B2. Should both conditions remain unmet, the final ELSE statement designates “Zero” to the cell.
Nested IF statements grant you the power to navigate intricate decision trees, accommodating various scenarios and delivering precise outcomes. While the example demonstrates a binary hierarchy, you can further expand these constructs to accommodate multiple branches of logic, enhancing the adaptability and intelligence of your Excel macros.
By integrating nested IF statements into your VBA repertoire, you harness a sophisticated mechanism for managing complex conditions and refining the functionality of your Excel projects, contributing to enhanced decision-making and streamlined workflows.
Using Logical Operators with the VBA If Statement
IF-Or, And, Xor, Not
In the realm of VBA, logical operators -Or, And, Xor, and Not -emerge as pivotal tools, fortifying your code with the ability to dissect complex conditions and execute dynamic decisions.
If Or
The Or operator delves into the terrain of inclusivity, assessing whether at least one condition holds True. Observe how the following code scrutinizes a range’s value, gauging if it is less than 5,000 or surpasses 10,000:
If Range(“A2”).Value<5000 Or Range (“A2).Value> 10000 Then
Range(“B2”).Value= “Out of Range”
End If
If Range(“A2”). Value < 5000 or_
Range(“A2”). Value >10000 Or _
Range(“A2”).Value = 9999 Then
Range(“B2”).Value = “Out Of Range”
End If
If And
In contrast, the And operator operates under the premise of totality, evaluating whether ALL conditions converge to true. Consider the ensuring illustration, where the code scrutinizes whether the range value resides within the range of 5,000 and 10,000:
If Range(“A2”). Value >=5000 And Range(“A2”). Value<=10000 Then
Range (“B2”). Value = “In Range”
End If
If Xor
The Xor operator introduces a nuanced perspective, confirming if a singular condition exclusively holds true. Xor returns FALSE when zero conditions or multiple conditions are satisfied.
If Not
If Not is used to convert the False to True or vice versa:
Sub If_Not()
MsgBox Not (True)
End Sub
.png)
How do VBA If statements work?
VBA If statement evaluates a condition and, the code block will execute if the condition is true. And If the condition becomes False, the code block is skipped and the program continues with the next statement after the if block.
What to do if none of the conditions are met in an If statement?
You can use the Else clause to specify a block of code to execute when none of the previous conditions are met.
Similar Reads
R - if statement
If statement is one of the Decision-making statements in the R programming language. It is one of the easiest decision-making statements. It is used to decide whether a certain statement or block of statements will be executed or not i.e if a certain condition is true then a block of statement is ex
3 min read
Swift - If Statement
Just like other programming languages in Swift language also if statement is used to execute the program based on the evaluation of one or more conditions or in other words if statement is used to run a piece of code only when the given condition is true. There are also known as branch statement. Fo
3 min read
VBA Switch Statement
In Visual Basic for Applications (VBA), when a user needs to execute a series of statements depending on the value of an expression, they turn to the versatile tool known as the "Switch Case." This construct allows programmers to define multiple cases, each representing a different value, and then "
5 min read
Swift - If-else Statement
Just like other programming languages in Swift language also support the if-else statement. In the if-else statement, when the given condition is true then the code present inside the if condition will execute, and when the given condition is false then the code present inside the else condition wil
3 min read
Vectorized IF Statement in R
In R Language vectorized operations are a powerful feature that allows you to apply functions or operations over entire vectors at once, rather than looping through each element individually. The ifelse() function is a primary tool for creating vectorized if statements in R Programming Language. Int
3 min read
Rust - If-else Statement
Branching with if-else in rust also is similar to other languages. Just the difference is the way of writing(syntax). Here the condition doesn't need to be surrounded by parenthesis. Decision-making structure is an important part of life. Similarly, in programming also there are situations where you
3 min read
Swift - If-else-if Statement
In Swift, the if-else if-else condition is used to execute a block of code among multiple options. It gives a choice between more than two alternative conditions as soon as one of the conditions is true, the statement associated with that if is executed. If all conditions are false or not satisfied
3 min read
R Next Statement
Next statement in R is used to skip any remaining statements in the loop and continue the execution of the program. In other words, it is a statement that skips the current iteration without loop termination. 'next' is a loop control statement just like the break statement. But 'next' statement wor
6 min read
If Statement in Solidity
If statement is a type of conditional statement. It is used to execute a certain block of code or statements only if a certain condition is true else no statement is executed. Syntax: if (condition) { // code is executed if condition is true } Here, if: keyword used to initiate the conditional state
1 min read
IF-ELSE-IF statement in R
if-else-if ladder in R Programming Language is used to perform decision making. This ladder is used to raise multiple conditions to evaluate the expressions and take an output based on it. This can be used to evaluate expressions based on single or multiple conditions connected by comparison or arit
2 min read