The If Statement | VBA Selection Statements

Introducing the If Statement

Humans often decide on a course of action by evaluating one or more criteria. Likewise, computer programs select a flow of execution (a.k.a. control) by evaluating expressions. The If-Then-[ElseIf]-[Else] statement is one of two VBA selection statements (the Select Case statement being the other).

An execution path is only chosen if the expression evaluates to a specific Boolean value. The Boolean return is either True or False, which equates to a non-zero (≠ 0) or zero (= 0) value. In this sense, the If-Then-[ElseIf]-[Else] statement is also called a conditional statement.

The header image above shows the If-Then-[ElseIf]-[Else] statement’s syntax. Also, the flowchart below illustrates its logic flow.

The single-line form has the same elements as the block-form (header image). However, all its elements are on a single line with colons separating multiple statements.

Also, in the single-line form the first set of statements (i.e., #1) is not optional and the End If statement is invalid. You should use the single-line form for short and simple selections, instead of the block form.

Salient Points on Usage

There are several important things to note about the If–Then-[ElseIf]-[Else] statement:

• Control expressions can be of two types, numeric or string and TypeOf–Is expressions. In either case, the expression evaluates to a True (i.e., ≠ 0) or False (i.e., = 0) value.
• The compiler treats a Null control expression return as False.
• The block form is more versatile and easier to read, maintain, and debug than the single-line form.
• In the block form, If statements can be nested within preceding If, ElseIf or Else statements. But each nesting must have its own End If statement. Also, you can use as many ElseIf statements as needed, but none can appear after the Else statement – unless as part of a new nested If statement.
• You can include Line labels or line numbers in the If-block. But the If statement must be the first entry on the block’s first line.
• Although the Else statement is optional, you should always include it. That way you control what happens should all control expressions in the preceding If and ElseIf statements return False. Otherwise, program execution (or flow of control) shifts to the line below End If.

Too many nested If-Then-[ElseIf]-[Else] statements make your code difficult to read. When different values of an expression lead to different execution paths, use the Select Case statement instead.

Sample Code (Examples)

The code samples below show the various forms of the If-Then-[ElseIf]-[Else] statement in use. They also illustrate the crucial points discussed above.

In each sample code, the aim is to decide a person’s generational class based on their birth year. The programs adopt the Pew Research Center’s definition (shown in the image below).

Single-line If-Then Statement

The sample code below illustrates the single-line If-Then statement’s usage. It assigns age-based generations as per the Pew Research Center’s definition.

In brief, the program:

• Declares Integer and String variables int_BirthYear, str_Validation, and str_Gen (code window line #8). These variables store the user’s birth year of interest, an input validation message, and the defined generation.
• Collects the user’s birth year of interest via the CInt and InputBox functions (line #11).
• Certifies that the user’s birth year of interest is within the applicable range (lines #15-18). Otherwise, it ends execution (line #18). The single-line If-Then statement, MsgBox function, and Exit Sub statement are used for this.
• Assigns a generation to the birth year by matching it to the Pew Research Center’s definition. Single-line If-Then statements and the And operator are used for this (lines #21-25).
• Displays the result via the MsgBox function (lines #28-30). Also, with the line continuation character (underscore, _), the function is able to span across several lines.

CInt and CStr are built-in functions for converting expressions into Integers and Strings. However, CInt throws an error if the user enters non-numeric input, so the Val function would be a better choice here.

The InputBox and MsgBox functions are also built-in VBA functions. They serve as means of inputting and outputting data to and from programs.

Block-form If-Then-[ElseIf]-[Else] Statement

The single-line If–Then statement’s sample code above works, but it is inefficient. The issue is that each If–Then statement in the generation assignment block must run. But the birth year can only ever be in one age-range at any time, so only one of these If–Then statements is useful at any one time.

The If–Then-[ElseIf]-[Else] statement’s block form stops range-checking once it finds a match. The sample code below shows its use in assigning generations as per the Pew Research Center’s definition.

Nested If-Then-[ElseIf]-[Else] Statement

You can improve the structure of the block-form If–Then-[ElseIf]-[Else] statement’s sample code above by nesting. The sample code below shows the nested If–Then-[ElseIf]-[Else] statement’s use in defining generations as per Pew Research Center’s classification.

Here, the generation assignment block (lines #19-29) nests beneath the input validation block. Also, the assignment block now has an Else statement after the Exit Sub statement (line #17).