The ElseIf structure
VBA supports a special type of If structure, using the ElseIf keyword. The ElseIf form is a shorthand notation that allows you to simplify If structures that follow this form:
If expression Then statements Else If expression Then statements Else If expression Then statements End If End IF End If
Using the ElseIf keyword, you can express the same structure like this:
If expression Then statements ElseIf expression Then statements ElseIf expression Then statements End If
If this example is a little too abstract, consider a macro that displays one of three messages, depending on the day of the week. On Sunday, the macro displays "Time for Tennis!" On Saturday, it displays "Time to mow the lawn!!" And, on any other day, it displays "Time to go to work!!!" Here's how to code this macro by using ordinary If statements:
DayOfWeek=Weekday(Now()) If DayOfWeek = 1 Then MsgBox("Time for tennis!") Else If DayOfWeek = 7 Then MsgBox("Time to mow the lawn!!") Else MsgBox("Time to go to work!!!") End IF End If
Notice that the first Else clause contains a nested If statement. By using the ElseIf keyword, the second If statement is subsumed into the first, so a single If statement handles the whole thing:
DayOfWeek=Weekday(Now()) If DayOfWeek = 1 Then MsgBox("Time for tennis!") ElseIf DayOfWeek = 7 Then MsgBox("Time to mow the lawn!!") Else MsgBox("Time to go to work!!!") End If
In this example, only one End If line is required because it has only one If statement. In other words, the ElseIf keyword doesn't require its own, matching End If.
In most cases, a Select Case statement implements If structures that require ElseIf clauses more easily, which describe later in this tutorial, in the section "The Select Case statement."
The single-line If
In VBA, you can use a single-line form of the If statement, which looks like this:
If condition Then statement [Else statement]
To use this form of the If statement, you must code the condition, Then clause, and Else clause (if any) all on the same line. For example:
If x > 0 Then MsgBox("X is " & x)
This example displays the message X is n, where n is the value of x. But the message displays only if x is greater than zero.
You can include more than one statement in the Then part by separating the statements with colons. But if more than one statement is required, or if an Else part is required, I suggest that you use the basic multiline If form instead of the single-line form.
In this tutorial:
- Programming with VBA
- Basic Structure of VBA Macros
- Understanding the Basic Elements of VBA
- Working with Variables and Data
- Using static variables
- Using Strings
- Of Objects, Properties, and Methods
- Getting to know the object model
- Using the With statement
- Controlling Your Programs
- The ElseIf structure
- For/Next Loops
- User Input and Output
- User-Defined Procedures and Functions