Skip to Content
Course content

3.1 Conditional Statements.

Conditional statements in VBA allow your program to make decisions based on certain conditions. By using conditional statements, you can execute specific blocks of code when a condition is true and skip or run alternative blocks of code when it is false. This is fundamental for creating dynamic and responsive macros.

3.1.1 The If...Then Statement

The simplest form of a conditional statement in VBA is If...Then. It evaluates a condition and executes a block of code if the condition is True.

Syntax:

If condition Then
    ' Code to execute if the condition is true
End If

Example:

Sub IfThenExample()
    Dim score As Integer
    score = 85
    If score >= 50 Then
        MsgBox "You passed!"
    End If
End Sub

In this example, if the variable score is greater than or equal to 50, the message "You passed!" is displayed.

3.1.2 The If...Then...Else Statement

This variation adds an Else block that executes if the condition is False.

Syntax:

If condition Then
    ' Code to execute if the condition is true
Else
    ' Code to execute if the condition is false
End If

Example:

Sub IfThenElseExample()
    Dim score As Integer
    score = 45
    If score >= 50 Then
        MsgBox "You passed!"
    Else
        MsgBox "You failed."
    End If
End Sub

Here, if score is less than 50, the message "You failed." is displayed.

3.1.3 The If...Then...ElseIf...Else Statement

This version allows you to check multiple conditions sequentially using ElseIf.

Syntax:

If condition1 Then
    ' Code to execute if condition1 is true
ElseIf condition2 Then
    ' Code to execute if condition2 is true
Else
    ' Code to execute if none of the above conditions are true
End If

Example:

Sub IfThenElseIfExample()
    Dim score As Integer
    score = 75
    If score >= 90 Then
        MsgBox "Grade: A"
    ElseIf score >= 75 Then
        MsgBox "Grade: B"
    ElseIf score >= 50 Then
        MsgBox "Grade: C"
    Else
        MsgBox "Grade: F"
    End If
End Sub

This example checks a range of scores and assigns a grade based on the value of score.

3.1.4 The Select Case Statement

For situations where you need to evaluate multiple conditions against a single expression, Select Case can be a more concise and readable alternative to If...Then...ElseIf.

Syntax:

Select Case expression
    Case value1
        ' Code to execute if expression = value1
    Case value2
        ' Code to execute if expression = value2
    Case Else
        ' Code to execute if no cases match
End Select

Example:

Sub SelectCaseExample()
    Dim dayNumber As Integer
    dayNumber = 3
    Select Case dayNumber
        Case 1
            MsgBox "Monday"
        Case 2
            MsgBox "Tuesday"
        Case 3
            MsgBox "Wednesday"
        Case Else
            MsgBox "Invalid day"
    End Select
End Sub

In this example, if dayNumber is 3, the output is "Wednesday."

3.1.5 Nested Conditional Statements

You can nest If...Then statements inside each other to handle more complex conditions.

Example:

Sub NestedIfExample()
    Dim age As Integer
    Dim hasPermission As Boolean
    age = 16
    hasPermission = True

    If age >= 18 Then
        MsgBox "You are eligible."
    Else
        If hasPermission = True Then
            MsgBox "You need parental consent."
        Else
            MsgBox "You are not eligible."
        End If
    End If
End Sub

In this example, the program checks both age and hasPermission to determine eligibility.

3.1.6 Using Logical Operators in Conditions

You can combine multiple conditions using logical operators such as:

  • And: All conditions must be true.
  • Or: At least one condition must be true.
  • Not: Negates a condition.

Example with And:

Sub LogicalAndExample()
    Dim age As Integer
    Dim income As Integer
    age = 30
    income = 50000

    If age >= 25 And income >= 40000 Then
        MsgBox "You are eligible for the loan."
    End If
End Sub

Example with Or:

Sub LogicalOrExample()
    Dim age As Integer
    age = 17

    If age < 18 Or age > 60 Then
        MsgBox "You qualify for a discount."
    End If
End Sub

Example with Not:

Sub LogicalNotExample()
    Dim isMember As Boolean
    isMember = False

    If Not isMember Then
        MsgBox "You need to register."
    End If
End Sub

3.1.7 Practical Tips

  • Use Select Case for better readability when checking multiple values of the same variable.
  • Avoid deeply nested If...Then statements; refactor code for clarity.
  • Test all conditions to ensure no unexpected behavior occurs due to logic errors.

Summary

Conditional statements allow you to:

  • Control program flow by evaluating conditions.
  • Execute specific blocks of code based on logical comparisons.
  • Choose between If...Then for general-purpose conditions and Select Case for streamlined evaluation of a single expression.

Understanding and mastering these tools is vital for creating responsive and efficient VBA programs.

Commenting is not enabled on this course.