By John Walkenbach

If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis. 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. Here’s a simple CheckUser procedure, recoded to use the If-Then-Else structure:

Sub CheckUser2()
  UserName = InputBox(“Enter Your Name: “)
  If UserName = “Satya Nadella” Then
    MsgBox (“Welcome Satya…”)
‘    …[More code here] …
  Else
    MsgBox “Sorry. Only Satya Nadella can run this.”
  End If
End Sub

If-Then examples

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

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

The GreetMe procedure uses VBA’s Time function to get the system time. If the current time is less than .5, 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 .5, you can add another If-Then statement after the first one:

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

Notice that >= (greater than or equal to) is used for the second If-Then statement. This ensures that the entire day is covered. Had > (greater than) been used, no message would appear if this procedure were executed at precisely 12:00 noon. That’s pretty unlikely, but with an important program like this, you don’t want to take any chances.

An If-Then-Else example

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

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

Notice that the line continuation character (underscore) is used 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 GreetMe procedure can be rewritten as

Sub GreetMe4()
  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. This syntax is easier to read and makes the statements shorter.

What if you need to expand the GreetMe 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, using three If-Then statements, is simpler:

Sub GreetMe5()
 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”
 MsgBox “Good “ & Msg
End Sub

A new twist was added with the use of a variable. The Msg variable gets a different text value, depending on the time of day. The 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:

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

Using ElseIf

In the previous examples, every statement in the routine is executed. A slightly 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 critical, you should know about another syntax for the If-Then structure.

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

Sub GreetMe7()
 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, this procedure is a bit more efficient than the previous examples. The trade-off is that the code is more difficult to understand.

Another If-Then example

Here’s another example that uses the simple form of the If-Then structure. This procedure prompts the user for a quantity and then displays the appropriate discount, based on the quantity the user enters:

Sub ShowDiscount()
  Dim Quantity As Long
  Dim Discount As Double
  Quantity = InputBox(“Enter Quantity:”)
  If Quantity > 0 Then Discount = 0.1
  If Quantity >= 25 Then Discount = 0.15
  If Quantity >= 50 Then Discount = 0.2
  If Quantity >= 75 Then Discount = 0.25
  MsgBox “Discount: “ & Discount
End Sub

Notice that each If-Then statement in this routine is executed, and the value for Discount can change as the statements are executed. However, the routine ultimately displays the correct value for Discount because the If-Then statements are in order of ascending Discount values.

The following procedure performs the same tasks by using the alternative ElseIf syntax. In this case, the routine ends immediately after executing the statements for a true condition:

Sub ShowDiscount2()
 Dim Quantity As Long
 Dim Discount As Double
 Quantity = InputBox(“Enter Quantity: “)
 If Quantity &gt; 0 And Quantity &lt; 25 Then
  Discount = 0.1
 ElseIf Quantity &gt;= 25 And Quantity &lt; 50 Then
  Discount = 0.15
 ElseIf Quantity &gt;= 50 And Quantity &lt; 75 Then
  Discount = 0.2
 ElseIf Quantity &gt;= 75 Then
  Discount = 0.25
 End If
 MsgBox “Discount: “ & Discount
End Sub

These multiple If-Then structures are rather cumbersome. You may want to use the If-Then structure only for simple binary decisions.