VBA Conditional Statements

Conditional statements are the building blocks of decision-making in programming. They allow programs to adapt their behavior based on specific conditions, creating intelligent and dynamic applications. In this post, let’s explore how we can use conditional statements in VBA to make our Excel projects smarter and more responsive.

Types of Conditional Statements

In VBA, we have three types of conditional statements:

1. If…Then
2. If….Then…Else
3. If… Then… Elseif
4. Select Case

The first three are quite similar, while the Select Case statement offers a more streamlined approach for handling multiple conditions.

If…Then Statements

IIf...Then statements are used when you want the code to execute a specific action only if a condition is TRUE. Here’s a simple example:

If Range("A1").Value > 10 Then
  MsgBox "The value in A1 is greater than 10!"
End If

The code checks for Cell A1 to be greater than 10, and if the condition is TRUE, it displays a message saying the same.

If…Then…ELSE

If...Then...Else statements are used when you need to handle both TRUE and FALSE conditions. For example:

If Range("A1").Value > 10 Then
  MsgBox "The value in A1 is greater than 10!"
Else
  MsgBox "The value in A1 is 10 or less."
End If

Here, the code provides feedback for both scenarios: whether the value in A1 is greater than 10 or not.

If… Then… ElseIf

If...Then...ElseIf statements are handy when you have multiple conditions to check. For instance:

If Range("A1").Value > 10 Then
  MsgBox "The value in A1 is greater than 10!"
ElseIf Range("A1").Value = 10 Then
  MsgBox "The value in A1 is exactly 10."
Else
  MsgBox "The value in A1 is less than 10."
End If

Here, there can be three possible outcomes – and hence, the elseif statement was used.

Case… Select

Select Case statements offer a more efficient way to handle multiple conditions by evaluating a single expression against various cases. Here’s an example:

Select Case Range("A1").Value
  Case Is > 10
    MsgBox "The value in A1 is greater than 10!"
  Case Is = 10
    MsgBox "The value in A1 is exactly 10."
  Case Else
    MsgBox "The value in A1 is less than 10."
End Select

Practical Example: Triangle Test

Let’s put these concepts into practice with a program that determines whether three given numbers can form the sides of a triangle, and if so, what type of triangle they form.

Sub TriangleTest()

    Range("A1:E2").Clear  ' Clears contents in range A1:E2
    GenerateRandomSides   ' Calls GenerateRandomSides subroutine
    IsTriangle            ' Calls IsTriangle subroutine
    IsRightAngledTriangle ' Calls IsRightAngledTriangle subroutine

End Sub

Sub GenerateRandomSides()
    
    'Give Headings

    Range("A1") = "Side 1"
    Range("B1") = "Side 2"
    Range("C1") = "Side 3"
    
    'Generate Random Values between 0 and 10 (not inclusive)
    
    Range("A2") = 10 * Rnd()
    Range("B2") = 10 * Rnd()
    Range("C2") = 10 * Rnd()

End Sub

Sub IsTriangle()
    
    'Define Variables

    Dim num1 As Double, num2 As Double, num3 As Double
    Dim largestNumber As Double

    'Assign values to num1, num2, num3
    
    num1 = Range("A2").Value
    num2 = Range("B2").Value
    num3 = Range("C2").Value
  
    'Use Excel Function to get largest side. If... Then... Else Statement can be used but is inefficient
    
    largestNumber = Application.WorksheetFunction.Max(num1, num2, num3)
    
    'Checks whether the three sides can form a triangle based on the condition that the sum of the two shorter sides must be greater than the longest side
    'Use If... Then... ElseIf statement with If... Then statement nested inside
    
    If num1 = largestNumber Then
        If num2 + num3 > num1 Then
            Range("D2") = "Is Triange"
        End If
    ElseIf num2 = largestNumber Then
        If num1 + num3 > num2 Then
            Range("D2") = "Is Triange"
        End If
    Else
        If num1 + num2 > num3 Then
            Range("D2") = "Is Triangle"
        End If
    End If
    
    'If the three values cannot form a triangle, prints Not a triangle
    
    If Range("D2").Value = "" Then
        Range("D2") = "Not a triangle"
    End If
        
End Sub

Sub IsRightAngledTriangle()
    
    'Define Variables
    
    Dim side1 As Double, side2 As Double, side3 As Double
    
    'Assign Values to Variables
    
    side1 = Range("A2").Value
    side2 = Range("B2").Value
    side3 = Range("C2").Value
    
    'Define a temporary Variable
    
    Dim temp As Double
    
    'Use If... Then statement to decide longest side
    
    If side1 > side2 Then
        temp = side1
        side1 = side2
        side2 = temp
    End If
    If side2 > side3 Then
        temp = side2
        side2 = side3
        side3 = temp
    End If
    
    'Use If... Then Statement to evaluate if it is necessary to evaluate whether it is a right angled triangle
    
  
    If Range("D2") = "Is Triangle" Then
    
    'Use Case... Select to determine and print whether it is a Right Angled Triangle
    
        Select Case Sqr(side1 ^ 2 + side2 ^ 2)
          Case Is = side3
            Range("E2") = "Right Angled Triangle"
          Case Else
            Range("E2") = "Not a Right Angled Triangle"
        End Select
    End If

End Sub

In this example, we divided the task into multiple subroutines, making the code more modular and easier to manage. By calling each subroutine from the main TriangleTest subroutine, we ensure the process flows logically and efficiently.

Conclusion

Understanding and effectively using conditional statements in VBA is crucial for building robust and flexible applications. With these tools, you can create more dynamic and responsive Excel projects. Stay tuned for our next post, where we’ll dive into the world of loops. Until then, happy coding!