VBA Conditionals
Conditionals are constructs used to make decisions during the execution of a program. In VBA, there are Block If statements, Select Case statements, the Inline If function, the Switch function, and the Choose function. Conditionals can be nested inside each other and combined with other programming constructs to create complex programs.
Block If Statement
The Block If statement is used to execute a certain block of code based on a set of conditional statements. If the result of an expression in a conditional statement evaluates to True then that code block will execute and then exit the If Block. There must be one If condition, there can be any number of optional ElseIf conditions, and there can be one optional Else condition. If the If condition and all ElseIf conditions fail then the Else block will execute. If ElseIf and Else are omitted, the If condition is evaluated and the code block is executed or not.
Public Sub Example()
Dim x As Long
x = 50
If x > 100 Then
Debug.Print "x is greater than 100."
ElseIf x > 50 Then
Debug.Print "x is greater than 50."
ElseIf x >= 0 Then
Debug.Print "x is greater than or equal to 0."
Else
Debug.Print "x is negative."
End If
End SubPublic Sub Example()
'If the user does not answer yes, exit the subroutine
If MsgBox("Do you want to continue?", vbYesNo + vbQuestion) <> vbYes Then
Exit Sub
End If
Msgbox "You chose to continue!", vbOkOnly + vbInformation
End SubSelect Case Statement
The Select Case statement is used to test a single value or expression and perform branching logic. The Select Case statement will evaluate an expression and then go sequentially down the list of case expressions checking if they evaluate to True. If a True case expression is encountered the block of code for that case will execute and then exit the Select Case statement. An optional Else case can be specified which will execute if no case expression evaluates to True.
Public Sub Example()
Dim Message As String
Message = "Hello, World!"
Select Case Message
Case "Hello"
Debug.Print "Hello!"
Case "Goodbye"
Debug.Print "Goodbye!"
Case Else
Debug.Print "I do not understand."
End Select
End SubCase expressions can include comparison operators using the "Is" keyword.
Public Sub Example()
Dim x As Long
x = 100
Select Case x
Case Is > 100
Debug.Print "x > 100"
Case Is > 50
Debug.Print "x > 50"
Case Is >= 0
Debug.Print "x >= 0"
Case Else
Debug.Print "x < 0"
End Select
End SubCases can be joined with a comma using the same logic as the logical Or operator.
Public Sub Example()
Dim Message As String
Message = "Hi"
Select Case Message
Case "Hello", "Hey", "Hi"
Debug.Print "Hello!"
Case "Goodbye", "Bye", "See you later"
Debug.Print "Goodbye!"
Case Else
Debug.Print "I do not understand."
End Select
End SubCase expressions can use ranges of values separated by the To keyword.
Public Sub Example()
Dim x As Long
x = 5
Select Case x
Case 0 To 9
Debug.Print "x is between 0 and 9"
Case Else
Debug.Print "x is not between 0 and 9"
End Select
End SubComparison operators and ranges can also be used with Strings.
Public Sub Example()
Dim Message As String
Message = "Hello, World!"
Select Case Message
Case Is < "A"
Debug.Print "Message is greater than A"
Case "A" To "Z"
Debug.Print "Message is between A and Z"
Case Is > "Z"
Debug.Print "Message is greater than Z"
End Select
End SubSelect Case statements are often used with Enum types.
Note: Enums are named integers so they will match with any matching integer value.
Public Enum Options
Option1 = 1
Option2 = 2
Option3 = 3
End Enum
Public Sub Example()
Dim Opt As Options
Opt = Option2
Select Case Opt
Case Option1
Debug.Print "Option1 selected"
Case Option2
Debug.Print "Option2 selected"
Case Option3
Debug.Print "Option3 selected"
End Select
End SubInline If Function
The IIf (Inline If) function evaluates an expression and returns the result of the truepart expression or the falsepart expression. The conditional expression, the truepart expression, and the falsepart expression are all evaluated regardless of the result of the other expressions. If the condition, the truepart, or the falsepart evaluate to an error the function will cause an error.
Public Sub Example()
Dim x As Long
Dim y As Long
Dim Result As String
x = 1
y = 2
Result = IIf(x = y, "x = y", "x <> y")
Debug.Print Result
End SubSwitch Function
The Switch Function takes a list of expressions and values and returns the value corresponding to the first expression that evaluates to true.
Public Sub Example()
Dim x As Long
x = 2
Dim Result As String
Result = Switch(x = 1, "x = 1", x = 2, "x = 2", x = 3, "x = 3")
Debug.Print Result
End SubChoose Function
The Choose Function takes an index and a list of values and returns the value corresponding to the index.
Public Sub Example()
Dim i As Long
For i = 1 To 3
Debug.Print Choose(i, "One", "Two", "Three")
Next i
End SubNesting Conditionals
Conditional statements and functions can be nested inside each other to achieve more complex decision making and branching.
Public Sub Example()
Dim Score As Long
Score = 99
If Score >= 0 Then
If Score > 100 Then
Debug.Print "Maximum score allowed is 100."
Else
Select Case Score
Case 100
Debug.Print "Perfect!"
Case 0
Debug.Print "No Points!"
Case Is > 75
Debug.Print "Great Job!"
Case Is > 50
Debug.Print "Good Job."
Case Is > 25
Debug.Print "There's always next time."
Case Else
Debug.Print "You need to practice."
End Select
End If
Else
Debug.Print "Negative scores are not allowed."
End If
End Sub