VBA If-Then-Else Statement

VBA If-Then-Else statement is a fundamental part of the VBA programming language, used to control the flow of a program by allowing it to make decisions based on certain conditions. It is commonly used in conjunction with other VBA statements, such as loops and functions, to create more complex code. The purpose of the If-Then-Else statement is to execute a block of code if a certain condition is met, and to execute a different block of code if the condition is not met.

VBA If-Then-Else Statement

Syntax

The basic syntax of the If-Then-Else statement in VBA is as follows:

If condition Then
‘code to be executed if condition is true
Else
‘code to be executed if condition is false
End If

The ‘If’ and ‘Then’ keywords are mandatory, and are followed by the condition that needs to be evaluated. This condition can be a logical expression, a VBA function, or a comparison between two values. If the condition is met, the code within the first block (after ‘Then’) will be executed. If the condition is not met, the code within the second block (after ‘Else’) will be executed. The ‘Else’ keyword is optional, and if it is not included, the program will just skip the ‘Else’ block and continue with the code following the ‘End If’ statement.

Examples on VBA If-Then-Else Statement

1. Checking for a specific value

One of the most common uses of the If-Then-Else statement is to check for a specific value in a cell or variable. For example, if we want to check if the value in cell A1 is equal to 5, we can use the following code:

If Range("A1").Value = 5 Then
    MsgBox "The value in cell A1 is 5."
Else
    MsgBox "The value in cell A1 is not 5."
End If

2. Nested If-Then-Else statements

The If-Then-Else statement can also be nested within another If-Then-Else statement to create more complex logic. In the following example, we check if the value in cell A1 is equal to 5, and if it is, we also check if the value in cell A2 is equal to 10.

If Range("A1").Value = 5 Then
    If Range("A2").Value = 10 Then
        MsgBox "The values in cells A1 and A2 are both equal to 5 and 10 respectively."
    Else
        MsgBox "The value in cell A2 is not equal to 10."
    End If
Else
    MsgBox "The value in cell A1 is not equal to 5."
End If

3. Multiple conditions

The If-Then-Else statement can also evaluate multiple conditions using the ‘And’ and ‘Or’ operators. In the following example, we check if the value in cell A1 is between 1 and 10, and if it is, we also check if the value in cell A2 is equal to either 5 or 10.

If Range("A1").Value >= 1 And Range("A1").Value <= 10 Then
    If Range("A2").Value = 5 Or Range("A2").Value = 10 Then
        MsgBox "The values in cells A1 and A2 satisfy the conditions."
    Else
        MsgBox "The value in cell A2 does not satisfy the condition."
    End If
Else
    MsgBox "The value in cell A1 does not satisfy the condition."
End If

4. Checking for blank cells

The If-Then-Else statement can also be used to check if a cell is blank. In the following example, we check if cell A1 is blank, and if it is, we assign the value 10 to it.

If Range("A1").Value = "" Then
    Range("A1").Value = 10
Else
    MsgBox "Cell A1 is not blank."
End If

5. Using the ‘ElseIf’ keyword

Instead of nesting If-Then-Else statements, we can use the ‘ElseIf’ keyword if we have multiple conditions to evaluate. In the following example, we check if the value in cell A1 is equal to 5, and if it is not, we check if it is equal to 10.

If Range("A1").Value = 5 Then
    MsgBox "The value in cell A1 is 5."
ElseIf Range("A1").Value = 10 Then
    MsgBox "The value in cell A1 is 10."
Else
    MsgBox "The value in cell A1 is neither 5 nor 10."
End If

Important Notes & Remarks

• The condition should be written in a way that produces a logical result, either ‘True’ or ‘False’.
• If the code within the ‘Then’ block or the ‘Else’ block contains more than one line, it must be enclosed within ‘Begin’ and ‘End’ statements.
• When using the ‘And’ and ‘Or’ operators, it is important to enclose each condition within parentheses to avoid any ambiguity.
• The ‘ElseIf’ keyword must be used after the ‘If’ keyword and before the ‘Else’ keyword.
• Indentation is used to make the code more readable, but it is not necessary for the program to function correctly.

In conclusion, the If-Then-Else statement is a powerful tool in VBA that allows programmers to control the flow of their code by making decisions based on conditions. By using the syntax correctly and combining it with other VBA statements, it is possible to create complex and efficient code. However, it is important to note that proper planning and testing is crucial when using If-Then-Else statements to ensure the desired outcome is achieved.
I hope this post was informative and helped you to understand the purpose and syntax of the If-Then-Else statement in VBA. Is there anything you would like to add or any suggestions for future topics?

Please share your feedback and views in the comments below!