If-Then-Else structure

If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis. As in many other aspects of life, effective decision-making is the key to success in writing CAD or any other macros.

The If-Then structure has this basic syntax:

Example

  If condition Then statements [Else elsestatements]

Use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, if included, lets you execute one or more statements if the condition you’re testing is not true. Sound confusing? Don’t worry; a few examples make this crystal clear.

If-Then examples

The following routine demonstrates the If-Then structure without the optional Else clause:

Example

Sub GoodMorning()
  If Time < 0.5 Then MsgBox “Good Morning.”
End Sub

The GoodMorning procedure uses VBA’s Time function to get the system time. If the current system time is less than .5 (in other words, before noon), the routine displays a friendly greeting. If Time is greater than or equal to .5, the routine ends and nothing happens.

To display a different greeting if Time is greater than or equal to 0.5, add another If-Then statement after the first one:

Example

Sub GoodMorning2()
  If Time < 0.5 Then MsgBox “Good Morning.”
  If Time >= 0.5 Then MsgBox “Good Afternoon.”
End Sub

Notice that I used >= (greater than or equal to) for the second If-Then statement. This ensures that the entire day is covered. Had I used > (greater than), then no message would appear if this procedure were executed at precisely 12:00 noon.

If-Then-Else examples

Another approach to the preceding problem uses the Else clause. Here’s the same routine recoded to use the If-Then-Else structure:

Example

Sub GoodMorning3()
  If Time < 0.5 Then MsgBox “Good Morning.” Else _
  MsgBox “Good Afternoon.”
End Sub

Notice that I use the line continuation character (underscore) in the preceding example. The If-Then-Else statement is actually a single statement. VBA provides a slightly different way of coding If-Then-Else constructs that use an End-If statement. Therefore, the GoodMorning procedure can be rewritten as:

Example

Sub GoodMorning4()
  If Time < 0.5 Then
    MsgBox “Good Morning.”
  Else
    MsgBox “Good Afternoon.”
  End If
End Sub

In fact, you can insert any number of statements under the If part, and any number of statements under the Else part. I prefer to use this syntax because it’s easier to read and makes the statements shorter.

What if you need to expand the GoodMorning routine to handle three conditions: morning, afternoon, and evening? You have two options: Use three If-Then statements or use a nested If-Then-Else structure. Nesting means placing an If-Then-Else structure within another If-Then-Else structure. The first approach, the three statements, is simplest:

Example

Sub GoodMorning5()
  If Time < 0.5 Then Msg = “Morning.”
  If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon.”
  If Time >= 0.75 Then Msg = “Evening.”
  MsgBox “Good” & Msg
End Sub

The Msg variable gets a different text value, depending on the time of day. The final MsgBox statement displays the greeting: Good Morning, Good Afternoon, or Good Evening.

The following routine performs the same action but uses an If-Then-End If structure:

Example

Sub GoodMorning6()
  Dim Msg As String
  If Time < 0.5 Then
    Msg = “Morning.”
  If Time >= 0.5 And Time < 0.75 Then
    Msg = “Afternoon.”
  If Time >= 0.75 Then
    Msg = “Evening.”
  End If
  MsgBox “Good” & Msg
End Sub

If-ElseIf-Else examples

In the previous examples, every statement in the routine is executed — even in the morning. A more efficient structure would exit the routine as soon as a condition is found to be true. In the morning, for example, the procedure should display the Good Morning message and then exit — without evaluating the other superfluous conditions.

With a tiny routine like this, you don’t have to worry about execution speed. But for larger applications in which speed is important, you should know about another syntax for the If-Then structure. The ElseIf syntax follows:

Example

  If condition Then
    [statements]
  [Else condition-n Then
    [elseifstatements]]
  [Else
    [elsestatements]]

Here’s how you can rewrite the GreetMe routine by using this syntax:

Example

Sub GoodMorning7()
  Dim Msg As String
  If Time < 0.5 Then
    Msg = “Morning.”
  ElseIf Time >= 0.5 And Time < 0.75 Then
    Msg = “Afternoon.”
  Else
    Msg = “Evening.”
  End If
  MsgBox “Good” & Msg
End Sub

When a condition is true, VBA executes the conditional statements and the If structure ends. In other words, VBA doesn’t waste time evaluating the extraneous conditions, which makes this procedure a bit more efficient than the previous examples. The trade-off (there are always trade-offs) is that the code is more difficult to understand. (Of course, you already knew that.)