VBA IF Else

What is IF Else Statement in VBA Excel?

The VBA If Else statement in excel is a decision-making construct used to test conditions and take different actions based on the result. It allows your VBA program to choose what code to execute based on whether a particular condition is met. The statement helps control the program’s flow and is instrumental in creating complex and dynamic VBA scripts. It works by evaluating a logical test and then taking one of two possible actions depending on whether the result is True or False.

Let us look at an example. In this example, we have a subroutine named “TestIfElseExample.” The purpose of this code is to demonstrate the use of a VBA IF ELSE statement. It initializes a variable “number” with a value of 10 and checks if it is greater than 0. If the condition is true, a message box displays “The number is positive.” Otherwise, if the condition is false, another message box shows “The number is non-positive.”

The output of this code will be the above message box displaying “The number is positive .” The value of “number” (10) satisfies the condition in the IF statement, indicating that it is indeed positive.

What is IF Else Statement in VBA Excel?

The If Else statement in VBA Excel is a decision-making construct used to test conditions and take different actions based on the result.
The If Else statement can include multiple conditions using ElseIf parts, and the conditions are evaluated from top to bottom.
In VBA, when using an IF ELSE statement to perform a “VBA IF Else Do Nothing” action, there is no need to include the “END IF” statement. Instead, if the condition in the IF statement is false, the program simply moves on to the next line of code after the IF-ELSE block.
The VBA IF ELSE IF statement allows for multiple conditions to be evaluated in sequence. When using IF ELSE IF statements, it is essential to close the entire block with “END IF.”

The Syntax of the VBA If Else Statement

The general syntax of the VBA If Else statement in VBA is as follows.

If condition Then

‘Code to execute if condition is True

Else

‘Code to execute if condition is False

End If

In this syntax, ‘condition’ is a logical test in excel that the VBA If Else statement evaluates. If the condition is true, the code following the ‘Then’ keyword is executed. The code following the ‘Else’ keyword is executed if the condition is false.

The VBA If Else statement can be nested to test multiple conditions and can also include ElseIf parts to test for additional conditions. The syntax for an If Else statement with ElseIf parts is:

If condition1 Then

‘Code to execute if condition1 is True

ElseIf condition2 Then

‘Code to execute if condition1 is False and condition2 is True

Else

‘Code to execute if both condition1 and condition2 are False

End If

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

The Working of the VBA If Else Statement

The VBA IF ELSE statement provides a way to make decisions in a program based on a specified condition. Here’s how it works:

The VBA IF ELSE statement starts with the keyword “If,” followed by a condition in parentheses. The condition is typically an expression that evaluates to either true or false.
If the condition is true, the code block inside the “Then” statement is executed. This block contains the actions to perform when the condition is met.
If the condition is false, the program skips the code block inside the ” VBA IF ELSE Then” statement and moves to the code block inside the “Else” statement (optional). This block contains the actions that should be executed when the condition is unmet.
After executing the appropriate code block, the program executes the rest of the code following the VBA IF ELSE statement.

The VBA IF ELSE statement allows the program to choose between different actions based on the condition’s evaluation. It provides a way to control the flow of the program and perform different tasks accordingly.

Examples

Example #1: Basic If Else Statement

This example demonstrates a simple If Else statement that tests whether a number is positive or negative.

Step 1: Open Excel and press “ALT + F11” to open the VBA editor.

Step 2: Insert a new module by clicking “Insert” from the menu and selecting “Module.”

Step 3: Define a subroutine named “TestIfElse.”

Step 4: Declare a variable named “number” as an integer VBA data type. This variable will store the number we want to check.

Step 5: Assign the value of -10 to the “number” variable. In this case, we assign -10 to the variable for testing purposes.

Step 6: Check if the value of “number” is greater than or equal to 0. This line initiates an “If” statement to evaluate the condition inside the parentheses.

Step 7: If the condition is true, display a message box saying, “The number is positive.” This line is executed if the condition inside the “If” statement is true.

Step 8: If the condition is false, execute the code inside the “Else” block. The program proceeds to this line if the condition inside the “If” statement is false.

Step 9: We display a message box saying, “The number is negative.” This line is executed if the condition inside the “If” statement is false, indicating that the number is negative. It is followed by the End If statement.

Step 10: End the subroutine. This line marks the end of the subroutine and indicates that the program should continue executing from where it was called.
Step 11: Save the module and close the VBA. Now press Alt + F8 to open the VBA Macro menu, select “TestIfElse,” and run the code.

Step 12: Once you execute the code, you will see the message box with “The number is negative.”

Here is the complete code.

Sub TestIfElse()
Dim number As Integer
number = -10
If number >= 0 Then
MsgBox “The number is positive.”
Else
MsgBox “The number is negative.”
End If
End Sub

Example #2: If Else Statement with Multiple Conditions.

This example demonstrates an If Else statement with multiple conditions using ElseIf. Here, we are printing the grades based on the score obtained.

Step 1: In the new module, define a subroutine named “TestIfElseIf.”

Step 2: Declare a variable named “score” as an integer. This variable will store the score we want to evaluate.

Step 3: Assign the value of 85 to the “score” variable.

Step 4: Check if the ” score ” value is greater than or equal to 90. This line initiates an “If” statement to evaluate the condition inside the parentheses.

Step 5: If the condition is true, display a message box saying “Grade: A.” This line is executed if the condition inside the “If” statement is true.

Step 6: Similarly, using an “ElseIf” statement, check if the ” score ” value is greater than or equal to 80.

Step 7: If the condition is true, display a “Grade: B” message box.

Step 8: We check if the “score” is greater than or equal to 70, using another “ElseIf” statement.

Step 9: If the condition is true, display a “Grade: C” message box.

Step 10: If none of the previous conditions are true, execute the code inside the “Else” block. Display a message box saying “Grade: F.” This line is executed if none of the previous conditions are true, indicating a failing grade.

Step 11: End the “If” statement. This line marks the end of the “If” statement and indicates that the program should continue executing after the “If” block.

Step 12: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “TestIfElseIf,” and run the code.

Step 13: Once you run the code, you will see the message box “Grade: B,” as the score we provided is 85, which falls in the B category.

Here is the complete code:

Sub TestIfElseIf()
Dim score As Integer
score = 85
If score >= 90 Then
MsgBox “Grade: A”
ElseIf score >= 80 Then
MsgBox “Grade: B”
ElseIf score >= 70 Then
MsgBox “Grade: C”
Else
MsgBox “Grade: F”
End If
End Sub

Important Things To Note

In VBA, the Else part of an If Else statement is optional. If it is not included and the condition in the “If” part is False, the program simply skips the entire VBA If Else statement.
The conditions in a VBA If Else statement are evaluated from top to bottom, and as soon as a true condition is found, the rest of the statement is skipped. So, the order of the conditions can influence the outcome of the statement.
The ‘condition’ in a VBA If Else statement is a logical test that must return a Boolean value, either True or False. VBA will give a compile error if the condition returns any other value type.

Frequently Asked Questions (FAQs)

1. What can I use instead of if else in VBA?

The “Select Case” statement can be an alternative to the VBA If Else statement. It is particularly useful when you have multiple conditions to test, as it can make the code more readable and organized.

2. Why is VBA IF ELSE not working?

There could be several reasons why a VBA If Else statement is not working. The condition might not be returning a Boolean value, the order of conditions might be incorrect, the End If keyword might be missing, or a syntax error.

3. What are the advantages of VBA IF ELSE?

The If Else statement in VBA is flexible and powerful. It can test multiple conditions, allow for complex decision-making structures, and control the program’s flow based on dynamic conditions. It is also easy to understand and use.

4. Which VBA statement is used to exit out of an If Else structure permanently?

The Exit Sub or Exit Function statement can be used to exit out of an If Else structure and the entire subroutine or function in which the If Else structure is located.

VBA IF Else