9  Conditionals

“The best thing about a boolean is even if you are wrong, you are only off by a bit.” — Anonymous.

Conditionals are statements that allow controling the flow of a program based on certain conditions or criteria.

They are used to make decisions within the code, enabling the program to: - perform different actions, or - execute specific blocks of code depending on whether a given condition is True or False.

With conditions, we can create dynamic and responsive programs that adapt to different situations and user input.

In VBA, conditionals are expressed using:

• If statements
• If … Else statements
• If … Else If statements
• Select Case statements

9.1 If Statements

An “If” statement is used to conditionally execute a block of code. For example:

Option Explicit

Sub ExampleIfStatement()
    Dim x As Integer
    x = 6
    
    ' Check if x is greater than 5
    If x > 5 Then
        Debug.Print "x is greater than 5"
    End If
End Sub

In this example, the code inside the If block will only execute if the condition x > 5 is True.

Figure 9.1: Isn’t artificial intelligence (AI) just a series of nested if statements? In some sense, yes. But AI can handle more complex logic by learning from large amounts of data. For example, it would be impractical to write a decision tree for recognizing objects in images using nested If, Else If, and Else statements.

9.1.1 Example - Grade Classification

A function that returns “A”, “B”, “C”, or “D” if grade falls in the following numerical ranges:

• If grade is between 90 and 100, return “A”.
• If grade is between 80 and 89, return “B”.
• If grade is between 70 and 79, return “C”.
• If grade is between 0 and 69, return “D”.

Option Explicit

Function ClassifyGrade(grade As Double) As String

    If grade >= 90 Then
        ClassifyGrade = "A"
    End If
    
    If grade >= 80 And grade < 90 Then
        ClassifyGrade = "B"
    End If
    
    If grade >= 70 And grade < 80 Then
        ClassifyGrade = "C"
    End If
    
    If grade >= 0 And grade < 70 Then
        ClassifyGrade = "D"
    End If

End Function

Sub TestClassifyGrade()

    Debug.Assert ClassifyGrade(100) = "A"
    Debug.Assert ClassifyGrade(80) = "B"
    Debug.Assert ClassifyGrade(70) = "C"
    Debug.Assert ClassifyGrade(60) = "D"
    
End Sub

9.2 Exercises (If)

9.2.1 Simple If Statement

Write a VBA function that takes an integer as input and returns “Even” if the number is even and “Odd” if it’s odd.

Option Explicit

Function CheckEvenOdd(num As Integer) As String
    
    If num Mod 2 = 1 Then
        'Return odd
        CheckEvenOdd = "Odd"
    End If
    
    If num Mod 2 = 0 Then
        'Return even
        CheckEvenOdd = "Even"
    End If
        
End Function

Sub TestCheckEvenOdd()
    Debug.Assert CheckEvenOdd(4) = "Even"
    Debug.Assert CheckEvenOdd(7) = "Odd"
    Debug.Assert CheckEvenOdd(0) = "Even"
End Sub

9.2.2 Categorize Number

Write a VBA function that takes an integer as input and returns:

• “Positive” if it’s positive,
• “Negative” if it’s negative, and
• “Zero” if it’s zero,

using only “If” statements.

Option Explicit

Function CategorizeNumber(num As Integer) As String
    'Add code here!
End Function

Sub TestCategorizeNumber()

    Debug.Assert CategorizeNumber(5) = "Positive"
    Debug.Assert CategorizeNumber(-3) = "Negative"
    Debug.Assert CategorizeNumber(0) = "Zero"

End Sub

9.2.3 Determining Quadrant

Write a function that takes two numbers (x and y) as input representing coordinates on a 2D plane. The function should return the quadrant in which the point lies (Quadrant I, II, III, or IV) or “Origin” if the point is at the origin (0, 0). Use only If statements without Else.

Option Explicit

Function DetermineQuadrant(x As Double, y As Double) As String
    'Add code here!
End Function

Sub TestDetermineQuadrant()

    Debug.Assert DetermineQuadrant(3, 4) = "Quadrant I"
    Debug.Assert DetermineQuadrant(-3, 4) = "Quadrant II"
    Debug.Assert DetermineQuadrant(-3, -4) = "Quadrant III"
    Debug.Assert DetermineQuadrant(3, -4) = "Quadrant IV"
    Debug.Assert DetermineQuadrant(0, 0) = "Origin"

End Sub

9.2.4 Vowel or Consonant

Write a function that takes a single character (a letter) as input and returns “Vowel” if it’s a vowel, namely, “a”, “e”, “i”, “o”, or “u”, or “Consonant” otherwise. Use only “If” statements.

Option Explicit

Function IsVowelOrConsonant(char As String) As String
    'Add code here!
End Function

Sub TestIsVowelOrConsonant()

    Debug.Assert IsVowelOrConsonant("a") = "Vowel"
    Debug.Assert IsVowelOrConsonant("b") = "Consonant"
    Debug.Assert IsVowelOrConsonant("i") = "Vowel"

End Sub

9.3 If-Else Statement (Two-way If)

An “If-Else” statement allows us to handle two cases based on a condition. If the condition is true, one block of code is executed; otherwise, another.

9.3.1 Example 1

In this example, if x is greater than 5, the message “x is greater than 5” will be printed. Otherwise, the message “x is not greater than 5” will be printed.

Option Explicit

Sub ExampleIfElseStatement()
    Dim x As Integer
    x = 4
    
    'Only one block within the conditions will trigger
    If x > 5 Then
        Debug.Print "x is greater than 5"
    Else
        Debug.Print "x is not greater than 5"
    End If
End Sub

9.3.2 Example 2

In this example, the function CheckEvenOdd takes an integer as input and returns “Even” if the number is even and “Odd” if it’s odd.

Option Explicit

Function CheckEvenOdd(num As Integer) As String
    
    If num Mod 2 = 1 Then
        'Return odd
        CheckEvenOdd = "Odd"
    Else
        'Return even
        CheckEvenOdd = "Even"
    End If
        
End Function

9.4 Nested If Statements

Nested “If” statements allow us to check multiple conditions by nesting one “If” within another. It provides a way to handle more complex decision trees.

9.4.1 Example 3

In this example, we check multiple conditions, nesting one “If” within another. First, we check if x is greater than 0, and then we check if y is greater than 0.

Option Explicit

Sub ExampleNestedIfStatement()
    Dim x As Integer
    Dim y As Integer
    x = 5
    y = 7
    
    ' Check multiple conditions, nesting one "If" within another
    If x > 0 Then
        If y > 0 Then
            Debug.Print "Both x and y are greater than 0"
        Else
            Debug.Print "x is greater than 0, but y is not"
        End If
    Else
        Debug.Print "x is not greater than 0"
    End If
End Sub

9.5 Exercises (If-Else)

9.5.1 Checking Divisibility

Write a VBA function that takes two integers as input and returns “Divisible” if the first number is divisible by the second number, and “Not Divisible” otherwise.

Option Explicit

Function CheckDivisibility( _
        num1 As Integer, _
        num2 As Integer) As String
    'Add code here!
End Function

Sub TestCheckDivisibility()
    Debug.Assert CheckDivisibility(12, 4) = "Divisible"
    Debug.Assert CheckDivisibility(7, 3) = "Not Divisible"
    Debug.Assert CheckDivisibility(20, 7) = "Not Divisible"
End Sub

9.5.2 Checking Age Eligibility

Write a VBA function that takes an age as input and returns “Eligible” if the age is 18 or older, and “Not Eligible” otherwise.

Function CheckAgeEligibility(age As Integer) As String
    'Add code here!
End Function

Sub TestCheckAgeEligibility()

    Debug.Assert CheckAgeEligibility(25) = "Eligible"
    Debug.Assert CheckAgeEligibility(17) = "Not Eligible"
    Debug.Assert CheckAgeEligibility(18) = "Eligible"

End Sub

9.5.3 Checking Valid Username

Write a VBA function that takes a username as input and returns “Valid” if the username contains at least 6 characters and does not contain spaces, and “Invalid” otherwise.

Option Explicit

Function CheckUsernameValidity(username As String) As String
    'Add code here!
End Function

Sub TestCheckUsernameValidity()

    Debug.Assert CheckUsernameValidity("john_doe") = "Valid"
    Debug.Assert CheckUsernameValidity("user name") = "Invalid"
    Debug.Assert CheckUsernameValidity("short") = "Invalid"

End Sub

9.6 Else If Statement (Multi-Way If)

Allows us to check multiple conditions sequentially and execute the first one that evaluates to true. It’s useful for handling multiple cases.

9.6.1 Example 1

Determine the grade based on the value of the score.

Option Explicit

Sub DetermineGrade()

    Dim score As Integer
    score = 75
    
    'Only one block within the conditions will trigger
    If score >= 90 Then
        Debug.Print "Grade: A"
    ElseIf score >= 80 Then
        Debug.Print "Grade: B"
    ElseIf score >= 70 Then
        Debug.Print "Grade: C"
    ElseIf score >= 60 Then
        Debug.Print "Grade: D"
    Else 'Catch all block
        Debug.Print "Grade: F"
    End If

End Sub

9.7 Exercises (If-Else If)

In the following exercises, first try to design an overall plan regarding all conditions that have to be taken into account to determine a solution.

9.7.1 Number Sign

Write a function that takes two integers as input and returns “Positive” if both are positive, “Negative” if both are negative, and “Mixed” otherwise.

Option Explicit

Function CheckNumberSign( _
        num1 As Integer, _
        num2 As Integer) As String
    'Add code here!
End Function

Sub TestCheckNumberSign()

    Debug.Assert CheckNumberSign(5, 3) = "Positive"
    Debug.Assert CheckNumberSign(-2, -4) = "Negative"
    Debug.Assert CheckNumberSign(7, -1) = "Mixed"

End Sub

9.7.2 Increasing / Decreasing Order

Write a function that takes three numbers as input and returns “Increasing” if they are in increasing order, “Decreasing” if they are in decreasing order, and “Neither” otherwise.

Option Explicit

Function CheckNumberOrder( _
        num1 As Integer, _
        num2 As Integer, _
        num3 As Integer) As String
    'Add code here!
End Function

Sub TestCheckNumberOrder()
    Debug.Assert CheckNumberOrder(1, 2, 3) = "Increasing"
    Debug.Assert CheckNumberOrder(3, 2, 1) = "Decreasing"
    Debug.Assert CheckNumberOrder(2, 1, 3) = "Neither"
End Sub

9.7.3 Triangle Type

Write a function that takes three integers as input representing the sides of a triangle and returns “Equilateral” if all sides are equal, “Isosceles” if two sides are equal, and “Scalene” if all sides are different.

Option Explicit

Function CheckTriangleType( _
        side1 As Integer, _
        side2 As Integer, _
        side3 As Integer) As String
    'Add code here!
End Function

Sub TestCheckTriangleType()
    Debug.Assert CheckTriangleType(5, 5, 5) = "Equilateral"
    Debug.Assert CheckTriangleType(4, 4, 3) = "Isosceles"
    Debug.Assert CheckTriangleType(3, 4, 5) = "Scalene"
End Sub

9.7.4 Checking Leap Year

Write a function that takes a year as input and returns “Leap Year” if it’s a leap year and “Not a Leap Year” if it’s not.

Option Explicit

Function CheckLeapYear(year As Integer) As String
    'Add code here!
End Function

'VBA Sub to test the function using Debug.Assert
Sub TestCheckLeapYear()
    Debug.Assert CheckLeapYear(2020) = "Leap Year"
    Debug.Assert CheckLeapYear(2023) = "Not a Leap Year"
    Debug.Assert CheckLeapYear(2000) = "Leap Year"
End Sub

9.7.5 Checking Validity of a Date

Write a function that takes three integers as input representing a date (day, month, year) and returns

• “Valid” if the date is valid, or
• “Invalid”, otherwise.

Make sure you consider leap years!

Option Explicit

Function CheckDateValidity( _
        day As Integer, _
        month As Integer, _
        year As Integer) As String

    'Check year is valid
    'Check if month is valid
    'If month has 30 days, check if day <= 30
    'If month has 31 days, check if day <= 31
    'If month is Feb and the year is a leap year, day <=29
    'If month is Feb and the year is not a leap year, day <=28
    
End Function

Sub TestCheckDateValidity()
    Debug.Assert CheckDateValidity(31, 12, 2023) = "Valid"
    Debug.Assert CheckDateValidity(29, 2, 2024) = "Valid"
    Debug.Assert CheckDateValidity(30, 2, 2024) = "Invalid"
    'Add more edge cases here!
End Sub

9.7.6 Multiple Conditions with Strings

Write a function that takes two strings as input and returns “Equal” if they are the same, “Different” if they are different, and “CaseSensitive” if they are different considering case.

Option Explicit

Function CompareStrings( _
        str1 As String, _
        str2 As String) As String
    'Add code here!
End Function

Sub TestCompareStrings()
    Debug.Assert CompareStrings("hello", "Hello") = "CaseSensitive"
    Debug.Assert CompareStrings("openai", "openai") = "Equal"
    Debug.Assert CompareStrings("Test", "test") = "CaseSensitive"
End Sub

9.7.7 Checking Password Validity

Mark Zuckerberg, the CEO of Facebook, fell victim to a hack due to his notoriously weak password: dadada. To avoid such security vulnerabilities, it’s crucial to use strong passwords. Write a function that takes a password as input and returns “Valid” if the password meets the following criteria:

1. Contains at least one uppercase letter.
2. Contains at least one lowercase letter.
3. Contains at least one numeric digit.
4. Contains at least one special character (!@#$%^&*()_+[]{}|;:'",.<>?).
5. Has a minimum length of 8 characters.

Return “Invalid” if any of these criteria are not met.

Option Explicit

Function CheckPasswordValidity(password As String) As String
    'Add code here!
End Function

Sub TestCheckPasswordValidity()

    Debug.Assert CheckPasswordValidity("Password123!") = "Valid"
    Debug.Assert CheckPasswordValidity("WeakPass") = "Invalid"
    Debug.Assert CheckPasswordValidity("SecurePass123") = "Invalid"
    Debug.Assert CheckPasswordValidity("Complex@Pswd123") = "Valid"

End Sub

9.7.8 Checking Password Validity (Using Subfunctions)

Redo the function CheckPasswordValidity using the following subfunctions to test the criteria:

• HasUppercase(password As String) As Boolean
• HasLowercase(password As String) As Boolean
• HasDigit(password As String) As Boolean
• HasSpecialChar(password As String) As Boolean
• HasMinLength(password As String, minLength As Integer) As Boolean

Also, write a test Sub with the edge cases for each subfunction and a test Sub for the CheckPasswordValidityClean.

Option Explicit

Function CheckPasswordValidityClean(password As String) As String
    'Add code here!
End Function

9.8 Select Case Statement

Used to evaluate a single expression against multiple possible values and execute different code blocks based on the matching case.

9.8.1 Examples

Option Explicit

Sub ExampleSelectCaseStatement()

    Dim dayOfWeek As String

    ' Set the day of the week
    dayOfWeek = "Saturday"
    
    ' Determine the day type based on the value of dayOfWeek
    Select Case dayOfWeek
        Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"
            Debug.Print "It's a weekday"
        Case "Saturday", "Sunday"
            Debug.Print "It's a weekend"
        Case Else 'No option was matched
            Debug.Print "Invalid day"
    End Select

End Sub

9.8.1.1 Example 1 - Categorize BMI

Write a function that takes a person’s Body Mass Index (BMI) as input and returns the corresponding category (Underweight, Normal, Overweight, or Obese) using the “Select Case” statement.

Option Explicit

Function CategorizeBMI(bmi As Double) As String
    Select Case bmi
        Case Is < 18.5
            CategorizeBMI = "Underweight"
        Case 18.5 To 24.9
            CategorizeBMI = "Normal"
        Case 25 To 29.9
            CategorizeBMI = "Overweight"
        Case Is >= 30
            CategorizeBMI = "Obese"
        Case Else
            CategorizeBMI = "Invalid BMI"
    End Select
End Function

Sub TestCategorizeBMI()

    Debug.Assert CategorizeBMI(22.5) = "Normal"
    Debug.Assert CategorizeBMI(29#) = "Overweight"
    Debug.Assert CategorizeBMI(31.7) = "Obese"
    Debug.Assert CategorizeBMI(-5) = "Invalid BMI"

End Sub

9.8.1.2 Example 2 - Determining Zodiac Sign

Write a function that takes a birth month (1 to 12) and day (1 to 31) as input and returns the corresponding zodiac sign using the “Select Case” statement.

Option Explicit

Function GetZodiacSign( _
        month As Integer, _
        day As Integer) As String
    
    Select Case month
        Case 1
            If day >= 20 Then
                GetZodiacSign = "Aquarius"
            Else
                GetZodiacSign = "Capricorn"
            End If
        Case 2
            If day >= 19 Then
                GetZodiacSign = "Pisces"
            Else
                GetZodiacSign = "Aquarius"
            End If
        Case 3
            If day >= 21 Then
                GetZodiacSign = "Aries"
            Else
                GetZodiacSign = "Pisces"
            End If
        Case 4
            If day >= 20 Then
                GetZodiacSign = "Taurus"
            Else
                GetZodiacSign = "Aries"
            End If
        Case 5
            If day >= 21 Then
                GetZodiacSign = "Gemini"
            Else
                GetZodiacSign = "Taurus"
            End If
        Case 6
            If day >= 21 Then
                GetZodiacSign = "Cancer"
            Else
                GetZodiacSign = "Gemini"
            End If
        Case 7
            If day >= 23 Then
                GetZodiacSign = "Leo"
            Else
                GetZodiacSign = "Cancer"
            End If
        Case 8
            If day >= 23 Then
                GetZodiacSign = "Virgo"
            Else
                GetZodiacSign = "Leo"
            End If
        Case 9
            If day >= 23 Then
                GetZodiacSign = "Libra"
            Else
                GetZodiacSign = "Virgo"
            End If
        Case 10
            If day >= 23 Then
                GetZodiacSign = "Scorpio"
            Else
                GetZodiacSign = "Libra"
            End If
        Case 11
            If day >= 22 Then
                GetZodiacSign = "Sagittarius"
            Else
                GetZodiacSign = "Scorpio"
            End If
        Case 12
            If day >= 22 Then
                GetZodiacSign = "Capricorn"
            Else
                GetZodiacSign = "Sagittarius"
            End If
        Case Else
            GetZodiacSign = "Invalid Date"
    End Select

End Function

Sub TestGetZodiacSign()

    Debug.Assert GetZodiacSign(3, 21) = "Aries"
    Debug.Assert GetZodiacSign(7, 30) = "Leo"
    Debug.Assert GetZodiacSign(13, 10) = "Invalid Date"

End Sub

9.8.1.3 Example 3 - Categorizing Car Features

Write a function that takes two Boolean parameters, isElectric and isSporty, which represent whether a car is electric and sporty, respectively. The function should categorize the car into one of four categories using the “Select Case” statement based on the following criteria:

Car Categories:

• Electric Sportscar: isElectric is True and isSporty is True
• Electric Car: isElectric is True and isSporty is False
• Sportscar: isElectric is False and isSporty is True
• Regular Car: isElectric is False and isSporty is False

Option Explicit

Function CategorizeCar( _
        isElectric As Boolean, _
        isSporty As Boolean) As String

    Select Case True
        Case isElectric And isSporty
            CategorizeCar = "Electric Sportscar"
        Case isElectric And Not isSporty
            CategorizeCar = "Electric Car"
        Case Not isElectric And isSporty
            CategorizeCar = "Sportscar"
        Case Not isElectric And Not isSporty
            CategorizeCar = "Regular Car"
        Case Else
            CategorizeCar = "Invalid Input"
    End Select

End Function

Sub TestCategorizeCar()

    Debug.Assert CategorizeCar(True, True) = "Electric Sportscar"
    Debug.Assert CategorizeCar(True, False) = "Electric Car"
    Debug.Assert CategorizeCar(False, True) = "Sportscar"
    Debug.Assert CategorizeCar(False, False) = "Regular Car"
    Debug.Assert CategorizeCar(True, 123) = "Invalid Input"

End Sub

9.9 Exercises (Select Case)

9.9.1 Convert Letter Grade

Write a VBA function that takes a numeric grade (0 to 100) as input and returns the corresponding letter grade (A, B, C, D, or F) using the “Select Case” statement.

The grading scale is as follows:

• if the grade is between 90 and 100, return “A”.
• if the grade is between 80 and 89.9, return “B”.
• if the grade is between 70 and 79.9, return “C”.
• if the grade is between 60 and 69.9, return “D”.
• if the grade is less than 60, return “F”.

If the grade is outside the range 0 to 100, return “Invalid Grade”.

Option Explicit

Function ConvertToLetterGrade(grade As Double) As String
    'Add code here!
End Function

' VBA Sub to test the function using Debug.Assert
Sub TestConvertToLetterGrade()

    Debug.Assert ConvertToLetterGrade(92) = "A"
    Debug.Assert ConvertToLetterGrade(78) = "C"
    Debug.Assert ConvertToLetterGrade(55) = "F"
    Debug.Assert ConvertToLetterGrade(105) = "Invalid Grade"

End Sub

9.9.2 Categorizing Blood Pressure

Write a function that takes systolic and diastolic blood pressure readings as input and returns a category (Normal, Prehypertension, Hypertension Stage 1, Hypertension Stage 2, or Hypertensive Crisis) using the “Select Case” statement.

Blood Pressure Category	Systolic Range	Diastolic Range
Normal	Below 120	Below 80
Prehypertension	120–129	Below 80
Hypertension Stage 1	130–139	80–89
Hypertension Stage 2	140 and above	90 and above
Hypertension Crisis	Above 180	Above 120

Option Explicit

Function CategorizeBloodPressure( _
        systolic As Integer, _
        diastolic As Integer) As String
    'Add code here!
End Function

' VBA Sub to test the function using Debug.Assert
Sub TestCategorizeBloodPressure()

    Debug.Assert CategorizeBloodPressure(110, 70) = "Normal"
    Debug.Assert CategorizeBloodPressure(125, 85) = "Invalid Blood Pressure"
    Debug.Assert CategorizeBloodPressure(150, 95) = "Hypertension Stage 2"
    Debug.Assert CategorizeBloodPressure(190, 130) = "Hypertensive Crisis"
    Debug.Assert CategorizeBloodPressure(134, 85) = "Hypertension Stage 1"

End Sub