Blog

In previous tutorials, we became familiar with VBA comparison and logical operators. In this section, we will explore how to construct conditional and logical statements using these operators.

Definition of Conditional Statements

Conditional statements are used to check the existence or non-existence of a condition in a program. They play a key role in controlling program flow, as in many scenarios, it is necessary to evaluate a condition before executing a section of code.

Why Do We Need Conditional Statements?

In the first VBA program, we squared the value entered by the user in an InputBox. Now imagine the user types a letter instead of a number. In that case, VBA would attempt to execute the following:

intCalculatedNumber = strUserInput ^ 2

Since it is impossible to square a non-numeric value, the program will encounter a Type mismatch (Error 13) and stop. To prevent such errors, input must be validated using comparison and logical operators before performing calculations.

In other words, before performing calculations, you should validate the user’s input using conditional statements and comparison and logical operators.

Conditions Are Not Just for Error Prevention!

Although one of the most important uses of conditional statements is to prevent common errors (e.g., invalid input), their purpose goes much deeper.

Conditional statements act as a bridge between user behaviour and program flow. Using conditions, you can:

1. Guide the user to enter only valid input. In the example below, if the user input is not a number, a warning message is displayed and program execution stops:

If Not IsNumeric(strInput) Then
    MsgBox "Please enter numbers only.", vbExclamation
    Exit Sub
End If

2. Control program flow and determine what happens in each case. In the example below, if the entered number is positive, one message is shown; if zero, another; if negative, a different message appears:

If num > 0 Then
    MsgBox "The number is positive."
ElseIf num = 0 Then
    MsgBox "The number is zero."
Else
    MsgBox "The number is negative."
End If

3. Implement decision logic, such as choosing between options, applying constraints, or handling special cases. In the example below, if the variable operationType is 1–4, one of addition, subtraction, multiplication, or division is performed between a and b; otherwise, an error message is shown.

Select Case operationType
    Case 1
        result = a + b
    Case 2
        result = a - b
    Case 3
        result = a * b
    Case 4
        result = a / b
    Case Else
        MsgBox "Invalid option."
End Select

4. Create smarter interactions with the user. In the example below, if the input is very large, the program asks the user whether to continue:

If num > 1000 Then
    If MsgBox("The number is very large. Continue?", vbYesNo + vbQuestion) = vbNo Then
        Exit Sub
    End If
End If

In other words, conditions make your program more than just a calculation machine; it can recognise different behaviours, make decisions, and respond appropriately.

The Key Role of Conditions in Program Algorithms

Every algorithm—from the simplest to the most complex—is ultimately based on conditions. To align the user and program, the software must know:

• When to continue,
• When to warn,
• When to show alternative outputs,
• When to stop execution.

All these decisions are made using conditional structures like If…Then…Else or Select Case.

Hence, conditional statements are the core of control flow logic in programming; without them, no program can act intelligently or interact meaningfully with the user.

Boolean Variables in Conditional Statements

The result of all comparison and logical operators in VBA can only be either True or False. Therefore, Boolean variables are the best option to store the outcome of conditional statements.

For example, the following expressions always result in True or False:

(5 > 3)     ' Result True
(10 = 20)   ' Result False

Exercise:

You can type different conditional expressions in the Immediate Window and see their results instantly. To open the Immediate Window in VBA, press Ctrl + G.

Practice examples:

1. Type:

? 7 < 10

Press Enter and see the result True.

2. Try other expressions:

? 15 = 20
? "Hello" = "Hello"
? 5 > 10 And 3 < 4

This exercise helps you observe the behaviour of comparison and logical operators and their Boolean outcomes interactively.

To learn more about the Immediate Window and its features, you can check this tutorial:
Immediate Window | Introduction to the VBA Immediate Window

Combining Comparison and Logical Operators

Comparison operators alone cover simple conditions, but for more complex conditions, you can use logical operators such as And, Or, and Not along with them.

Simple example:

If we want to check whether both variables intA and intB are positive, we can use:

(intA > 0) And (intB > 0)

The expression is True only when both variables are greater than zero.

Note: When combining comparison and logical operators, always consider operator precedence. For better readability, use parentheses when constructing compound expressions.

Practical Example: Storing Result in a Boolean Variable

Dim i As Integer
Dim j As Integer
Dim sw As Boolean

sw = (i > 100) Or (j < -20)

Here, the result of the logical expression is stored in the Boolean variable sw and can be either True or False.

Interactive Exercise:

You can test combinations of operators in the Immediate Window and observe the results interactively.

1. Open the Immediate Window with Ctrl + G.
2. Type the following expressions and press Enter:

? (5 > 3) And (10 < 20)       ' Result True
? (5 > 10) Or (3 = 3)         ' Result True
? Not (7 < 2)                  ' Result True
? (4 > 2) And Not (3 = 3)      ' Result False

This exercise lets you interactively observe how logical operators combine with comparison operators and their Boolean results.

For more information about the Immediate Window and its features, visit:
Immediate Window | Introduction to the VBA Immediate Window

Summary

In this tutorial, we learned the concept of conditional statements and their role in controlling program flow. We also saw how to evaluate conditions using comparison and logical operators and store the True or False results in Boolean variables. We learned that combining operators allows creating more complex conditions and that using parentheses improves readability and understanding of the code.

What You Will Learn Next

In the next part of our free VBA tutorial series, we will delve into decision-making structures. We will fully introduce the powerful If statement and learn how to implement the conditional expressions created in this lesson using If, along with structures like Else, ElseIf, and nested If statements.

If you want to learn how to control program flow using conditions, don’t miss the next tutorial on the If statement.