Control Flow Statements: Conditional and Select

What are Control Flow Statements?

Control statements influence whether other statements are executed, they affect the flow of program execution. VBA provides for three basic types of control statements:

Conditional/Selection statements
Iteration/Loop statements
Goto statements

Within these statements, Conditional Operators and Wildcards are an integral part of the decision-making process.

Conditional Statements

Conditional statements are like forks in a road where the path decision is based on certain conditions. These are often referred to as "If .. Then" statements.

If … Then

If (Condition1) then (take Action1)

The If … Then control is the simplest of the control statements. It is always written on one line and commands only one action if the condition or conditions in Condition1 are true.

If Then Statement

Sub testIfThen()
Dim X, Y as Integer

X = 1

Y = 2

If X = 1 then Y = 3

Msgbox Y

End Sub

If … Then … End If

If (Condition1) then
Action1
Action2
.
.
.
End If

The If … Then statement does not have to be written all on one line, but requires ending with an End If whenever writing on more than one line. This allows for taking more than one action whenever the condition or conditions are true.

If Then End If Statement

Sub testIfThen()
Dim X, Y as Integer

X = 1

Y = 2

If X = 1 or X = 0 then

Y = 3

Msgbox Y

End If

End Sub

Msgbox will display the value of Y in the first example, regardless of whether the If statement is true. In the second example (above), the Msgbox will only be invoked if X = 1 or X = 0. Action is taken only if certain conditions are true in either case.

If … Else … Then … End If

If (Conditions1) then
Actions1
Else
Actions2
End If

If Conditions1 are true, then Actions1 will be executed, otherwise Actions2 will be executed. With this structure, Actions1 must appear on a separate line from the If construct. The first action in Actions2 may appear on the same line as Else, but a ":" must separate them, Else: Action2a, where Action2a is the first action in Actions2.

If Then Else Statement

Sub testIfThenElse()
Dim X, Y, Z as Integer

X = 1

Y = 2

If X = 1 & Y = 2 then

Z = 3

Else: Z = 2*X + Y

Y = 0

End If

Msgbox Z

End Sub

Because X = 1 and Y = 2, Z is set equal to 3. If Y had been initialized to 3, then the conditions in the If part of the statement would be false and Z would be set equal to 2*1 + 3 = 5 and Y would be set equal to zero (0).

If … Elseif … Else … Then … End If

If (Conditions1) then
Actions1
Elseif (Conditions2) then
Actions2
Else
Actions3
End If

Elseif Actions2 are executed if Conditions1 are false, and Conditions2 are true. As above, the first action in Actions 2 and Actions3 may appear on the same line as Elseif and Else, respectively, but must have the ":" separator before the action statement. Note: More than one Elseif checks can be made with the above structure.

If Then Elseif Else Statement

Sub testIfThenElseIf()
Dim X, Y, Z As Integer

X = 1

Y = 2

Z = 3

If X = 2 Then

Y = Z

ElseIf X = 1 And Y = Z Then

Z = 4

Else

MsgBox "All failed"

End If

End Sub

In this example, Msgbox displays "All failed," since neither of the conditions checked were true.

Multiple If Then Elseif Statements

Sub testIfThenMultipleElseIf()
Dim X, Y, Z As Integer

X = 1

Y = 2

Z = 3

If X = 2 Then

Y = Z

ElseIf X = 1 And Y = Z Then

Z = 4

ElseIf X = 1 Then: Z = 4

ElseIf Y = 2 Then

Z = 6

Else

MsgBox "All failed"

End If

MsgBox Z

End Sub

In this example, the first two checks are false since X does not equal 2 (first check) and Y is not equal to Z (in the second check). But the third and fourth checks are both true, X equals 1 (third check) and Y equals 2 (fourth check). But Elseif is only executed if the previous statement is false, so VBA stops on the first true and executes the action associated with it. All remaining checks are ignored. So here Msgbox displays a 4.

Select Statements

Selection statements are useful for situations in which there are many forks in the road because they enable the decision-making statements to be written more succinctly than If … Then statements and, thus, provide easier code management.

Select Case … Case … End Select

Select Case Variable
Case Criteria1
Action1a
Action1b
Case Criteria2: Action2a
Acion2b
[Case Else: Action3]
End Select

Select Case tries to match a variable with a list of criteria, each preceded by Case. VBA steps through the list in order from the top down. A (optional) Case Else statement means "all other values."

When a match is found, the associated actions are executed. VBA executes on the first match and ignores all that follow. If no matches are found and there is no Case Else statement, the check will complete with no errors and no actions executed. Rules for Select Case are:

If an action is written on the same line as a case criteria statement, then a colon must separate the criteria from the action.
Each action must appear on a separate line.
The criteria may be single values, ranges of values, or disjointed sets of values.
Relationship operators may be used as part of the criteria.

Select Case with numeric criteria of multiple types

Sub selectCaseExample_1()
Dim X As Integer

X = 55

Select Case X

Case 0 To 25

MsgBox ("1 - Between 0 and 25")

Case Is < 75

MsgBox ("2 - Less than 75")

Case 55, 76 Or 78, 81 To 92: MsgBox "3"

Case Else

MsgBox "4 - All other cases"

End Select

End Sub

In this example, message 2 is returned as it is the first match. Criteria in the third case statement is true if X equals 55 or 76 or 78 or any integer between 81 and 92.

Select Case with string criteria

If the variable is a string variable, then double quotes are used as parts or all of the criteria. Select Case is case sensitive. Relationship comparisons are based on sort order.

Sub selectCaseExample_2()
Dim Inital As String

initial = "H"

Select Case initial

Case "A" To "F"

MsgBox ("Group 1")

Case "G" To "M": MsgBox ("Group 2")

MsgBox "Enter additional actions here"

Case Is <> "P": MsgBox ("Not equal to P")

Case Else

MsgBox ("Group 3")

MsgBox "Enter additional actions here"

End Select

End Sub

Sub selectCaseExample_3()
Dim Inital As String

initial = "H"

Select Case initial

Case Is < "G": MsgBox ("Group 1 - all capital letters A - F")

Case Is < "N": MsgBox ("Group 2 - letters G - M")

Case Is <> "P": MsgBox ("Not equal to P")

Case Else: MsgBox ("Group 3 - all other cases")

End Select

End Sub

Both examples above perform the same checks and return the same result — Group 2.

Select Case using double quotes with numeric data

Double quotes within criteria may also be used with other variable types.

Sub selectcaseExample_3()
Dim X, Y, Z As Integer

X = 1

Y = 2

Z = 3

Select Case X

Case "1": Z = X

Case "2": Z = Y

End Select

MsgBox Z

End Sub

In this example, X matches the first case value in the list, so Z is set equal to X and Msgbox returns a 1. If no matches had been found, it would have returned a 3 (the original value of Z), but had Z not been initialized before the Select Case statements, then Msgbox would have returned a blank message.