VBA If Statements with Multiple Conditions

Introduction

One of the most common things to do in coding is to build logic that checks for the occurrence of multiple conditions. As a coder, whether novice or experienced, hopefully you agree that this is one of the most common coding tasks.

In VBA, If statements are commonly used to build such logic. For example, you could build logic that checks if the weather is good today.

If it is, then you can leave home. If it is not, then check if it’s slightly raining or if it’s a blizzard. If it is slightly raining and you have your rain coat ready, then you can leave. This is a simple scenario, but it resembles a lot of common coding problems.

We mostly can handle such logic by using the IF statement with multiple conditions. An alternative that makes sense under certain conditions is to use the Select Case statement. In this guide, we will focus on the If Statement with multiple conditions, but we will also briefly mention the basics of Select Case statement as well as when to use it.

Flow chart with if or logic about whether to go outside or not

IF Statement Syntax Table

Description Format Example
If Then If [condition is true] Then [do something] End If If Good_Weather = True Then MsgBox “Go Out" End If
If without End If (only one line) If [condition is true] Then [do something] If Good_Weather then MsgBox “Go Out"
If Else If [condition is true] Then [do something] Else [do something] End If If Rain_Coat_Ready = True Then MsgBox “Go Out" Else MsgBox “Stay Home" End If
If ElseIf If [condition 1 is true] Then [do something] ElseIf [condition2 is true] Then [do something] End If If Good_Weather = True Then Msgbox “Go Out" ElseIf Good_Weather = False AND Rain_Coat_Ready = True Then Msgbox “Go Out" End if
ElseIf and Else (must be in this order) If [condition1 is true] Then [do something] ElseIf [condition 2 is true] Then [do something] End If If Good_Weather = True Then Msgbox “Go Out" ElseIf Good_Weather = False AND Rain_Coat_Ready = True Then Msgbox “Go Out" Else Msgbox “Stay Home" End if

Logic Gates

X Y AND RESULT OR RESULT
TRUE TRUE AND TRUE
TRUE FALSE FALSE TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE

Example 1: Should I go out today?

We will develop the logic for the above diagram step-by-step.

Sub ShouldIGoOutToday()
'Set the weather to good or bad
Good_Weather = False
'Set the Rain coat to ready or not
Rain_Coat_Ready = True
'Set the type of bad weather
Bad_Weather = "Some Rain"
If Good_Weather = True Then
    'if weather is good then go out
    MsgBox "Go Out"
Else
    'if weather is not good
    If Bad_Weather = "Some Rain" Then
    'If the bad weather is some rain
        If Rain_Coat_Ready = True Then
        'If the bad weather is some rain and the rain coat is ready
            MsgBox "Go Out"
        Else
            'if the bad weather is some reain and the rain coat is not ready
            MsgBox "Stay Home"
        End If
    ElseIf Bad_Weather = "Blizzard" Then
    'if the bad weather is blizzard
        MsgBox " Stay Home"
    End If
End If
End Sub
Example 1 vba code

Example 2: Using AND / OR

You could specify multiple conditions within the same condition by using AND and OR. Instead of checking that condition = True, we could check that condition1 = True AND condition2 = True. This can be applied to the above example by checking if the bad weather is ‘some rain’ AND the rain coat is ready.

Sub ShouldIGoOutTodayEx2()
'Set the weather to good or bad
Good_Weather = False
'Set the Rain coat to ready or not
Rain_Coat_Ready = True
'Set the type of bad weather
Bad_Weather = "Some Rain"
If Good_Weather = True Then
    'if weather is good then go out
    MsgBox "Go Out"
Else
    'if weather is not good
    If Bad_Weather = "Some Rain" And Rain_Coat_Ready = True Then
    'If the bad weather is some rain AND the rain coat is ready then Go Out
            MsgBox "Go Out"
    ElseIf Bad_Weather = "Blizzard" Or Rain_Coat_Ready = False Then
    'if the bad weather is blizzard or the rain coat is not ready then Stay Home
        MsgBox " Stay Home"
    End If
End If
End Sub
Example 2 code

For more on using AND and OR with If statements, see this article:

https://software-solutions-online.com/vba-if-or/

Alternative to If Statement: Select Case

The most obvious way to select case is to do something when we are only interested in the value of a particular variable and the things we do will depend on the value of that variable.

In our above example, there is no single variable that we depend on to decide the outcome. We will attempt to implement the above logic using Select Case.


The main difference between the Select Case and If statements is that Select Case allows you to only ‘do something’ based on the value of the Select Case variable. In order to overcome this hurdle, you can set the Select Case variable to True. You would then specify the full condition after the keyword Case and the thing we want to do when it is met on the next line.

Hence, instead of this syntax:

If X = 2 and Y = 3 then

[do something]

End if

With Select Case, it is this syntax:

Select Case True

Case X = 2 and Y = 3

[do something]

End Select

The full implementation of the above examples using Select Case is shown below:

Sub ShouldIGoOutTodayEx3()
'Set the weather to good or bad
Good_Weather = False
'Set the Rain coat to ready or not
Rain_Coat_Ready = False
'Set the type of bad weather
Bad_Weather = "Some Rain"

Select Case True

    Case Good_Weather = True
    MsgBox "Go Out"
    
    Case Good_Weather = False And Bad_Weather = "Some Rain" And Rain_Coat_Ready = True
    MsgBox "Go Out"
    
    Case Good_Weather = False And Bad_Weather = "Some Rain" And Rain_Coat_Ready = False
    MsgBox "Stay Home"

    Case Good_Weather = False And Bad_Weather = "Blizzard"
    MsgBox "Stay Home"
 
End Select
End Sub 
Select case sample code

Summary

If statements are ideal for handling simple and complex logic that involves multiple conditions and things we want to happen accordingly. On the other hand, Select Case could also be used as an alternative, but it’s usually meant for a situation where we want to select a single thing to do out of a number of potential things. When the logic gets more complex and multiple levels get added to the logic, the Select Case becomes less intuitive.

Leave a Reply

Your email address will not be published. Required fields are marked *