Chapter 11

Making Decisions in Your Code

Computers behave intelligently in large part because programming languages include commands that test conditions. Then based on the results of that test, the code jumps (branches) to an appropriate area within the program. This is similar to human decision-making: if it's raining, then take an umbrella. If not, leave it home.

This chapter covers what are called conditional expressions. VBA uses these to create decision structures to direct the flow—the path of execution—of your procedures.

By using decision structures, you can cause your procedures to branch to different sections of code depending on such things as the value of a variable or expression or whether the user clicks the OK or Cancel button in a message box.

VBA offers two types of decision structures: If blocks and Select Case blocks. And there is a set of various kinds of If statements suitable for making typical decisions. For more complicated decision-making, you'll want to use the heavy-duty Select Case block structure. It's more efficient when working with truly involved decisions.

The chapter starts by introducing you to the comparison operators and logical operators you can use when building conditional expressions and logical expressions. Then it covers the different types of If blocks, which take up the bulk of the chapter. At the end of the chapter, you'll learn how to use Select Case.

In this chapter you will learn to do the following:

• Use comparison operators
• Compare one item with another
• Test multiple conditions
• Use If blocks
• Use Select Case blocks

How Do You Compare Things in VBA?

To compare things in VBA, you use comparison operators to specify what type of comparison you want: whether one variable or expression is equal to another, whether one is greater than another, whether one is less than or equal to another, and so on.

VBA supports the comparison operators shown in Table 11.1.

The first six comparison operators shown in Table 11.1 are straightforward. Numeric expressions are evaluated as you would expect. Alphabetical expressions are evaluated in alphabetical order: for example, because ax comes before handle in alphabetical order, it's considered “less than” handle.

So, "ax" < "handle" would evaluate to True. And whether an evaluation results in True or False determines what happens in an If… Then block. (In other words, the code in the Then section is executed when something is True. And it is not executed if something is False. Think If it's raining, Then take an umbrella. Otherwise, don't.)

Mixed expressions (numbers and letters) are evaluated in alphabetical order as well: Office 97 is “greater than” Office 2013 because 9 is greater than 2.

Is, the seventh comparison operator, is less familiar, and less often used. You use Is to compare object variables to establish whether two object variables represent the same object (a named object, not an object such as a document or a range).

For example, the following statements declare two objects—objTest1 and objTest2—and assign to each ActiveDocument.Paragraphs(1).Range, the range consisting of the first paragraph in the active document in Word. The next statement then compares the two objects to each other, returning False in the message box because the two objects are different even though their contents are the same:

Dim objTest1 As Object
Dim objTest2 As Object
Set objTest1 = ActiveDocument.Paragraphs(1).Range
Set objTest2 = ActiveDocument.Paragraphs(1).Range
'the next statement returns False because the objects are different
MsgBox objTest1 Is objTest2

However, if both object variables refer to the same object, the Is comparison returns True, as in the following example, in which both objTest1 and objTest2 refer to the object variable objTest3:

Dim objTest1 As Object
Dim objTest2 As Object
Dim objTest3 As Object
Set objTest3 = ActiveDocument.Paragraphs(1).Range
Set objTest1 = objTest3
Set objTest2 = objTest3
'the next statement returns True because
'objTest1 and objTest2 refer to the same object
MsgBox objTest1 Is objTest2

When using Is, keep in mind that it isn't the specific contents of the object variables that are being compared, but which object they refer to.

Testing Multiple Conditions by Using Logical Operators

Often, you'll need to test two or more conditions before taking an action: If statement X is True and statement Y is True, then do this; if statement X is True or statement Y is True, then do the other; if statement X is True and statement Y isn't True, then find something else to do; and so on. For example, if it's raining and you have a cold, put on your warmest rain gear.

To test multiple conditions, you use VBA's logical operators to link the conditions together. Table 11.2 lists the logical operators that VBA supports, with short examples and comments.

Of these six logical operators, you'll probably use the conjunction (And), disjunction (Or), and negation (Not) operators the most, with the other three thrown in on special (in other words, rare) occasions. (If the Imp logical operator doesn't make sense to you at this point, you probably don't need to use it.)

Dim strShort As String
strShort = Selection.Text
If Len(strShort) > 1 And _
  Mid(strShort, Len(strShort) - 1, 1) = "T" Then
  MsgBox "The second-last character is T."
End If

If Len(strShort) > 1 Then
  If Mid(strShort, Len(strShort) - 1, 1) = "T" Then
    MsgBox "The second-last character is T."
  End If
End If

If ActiveWorkbook.Saved = True Then
  ActiveWorkbook.Saved = False
Else
  ActiveWorkbook.Saved = True
End If

ActiveWorkbook.Saved = Not ActiveWorkbook.Saved

If Blocks

As in most programming languages, If blocks in VBA are among the most immediately useful and versatile commands for making decisions.

In the sections that follow, you'll look at three variations on the If statement:

• If…Then
• If…Then… Else
• If…Then… ElseIf… Else

If…Then

If…Then statements tell VBA to make the simplest of decisions. If the condition is met, execute the following statement (or statements). If the condition isn't met, skip to the line immediately following the conditional block.

An If statement block begins with If and concludes with End If. (However, a short If…Then statement can be written entirely on a single line, in which case the End If is omitted.)

SYNTAX

Simple If…Then statements can be expressed entirely on a single line. A one-line If…Then statement looks like this:

If condition Then Code to be executed goes here

If the condition is met, VBA executes the statement or statements that follow on that same logical line. If the condition isn't met, VBA doesn't execute the statement or statements.

But you can also write multi-line If…Then blocks. A multiple-line If…Then statement (the lines of code between If and End If are more properly known as a block) looks like this:

If condition Then
Code to be executed goes here
End If

If the condition is met, VBA executes all the code within the block (the statements enclosed between the If and End If). If the condition isn't met, VBA skips over the enclosed line or lines of code and resumes execution at the line after the End If statement.

EXAMPLES

In the previous chapters, you've already encountered a number of If blocks—they're so necessary in programming (not to mention in life itself) that it's hard to get anything done without them. The following sections show you some further examples.

One-Line If Statements

Here's an example of a one-line If statement:

Dim bytAge As Integer
bytAge = InputBox("Enter your age.", "Age")

If bytAge < 21 Then MsgBox "You may not purchase alcohol.",, "Underage"

The first line declares the Integer variable bytAge. The second line prompts the user to enter their age in an input box and stores the answer in the variable. The third line checks the value held in bytAge and displays an Underage message box if bytAge is less than 21.

You can include multiple statements on a single line if you separate the statements by a colon. A single-line If statement can sometimes be a good candidate for a multi-statement line of code. What you are doing is specifying that more than one action should be taken if the expression in the If…Then statement evaluates to True.

For example, let's say you wanted to halt the macro after displaying the Underage message box. You could include the End statement after a colon on the same line, as shown here:

If bytAge < 21 Then MsgBox "You may not purchase alcohol.",, "Underage": End

VBA executes this as follows:

1. First, it evaluates the condition.
2. If the condition is met, it executes the first statement after Then—in this case, it displays the Underage message box. Then it proceeds to execute any further statements on that line. Notice that all statements on a single-line If structure are conditional based on (depend on) that If statement. They are executed (or not) based on whether the condition is true or false.
3. Once the user has dismissed the Underage message box (by clicking the OK button, the only button it has), VBA executes the statement after the colon: End.

If you wanted, you could even add several more statements on the same “logical” line, separated by colons. End would have to be the last one because it ends the procedure. (By the way, a logical line means that VBA sees this as a single line of code to be executed, no matter how many real-world, physical lines the code takes up on your monitor.)

You could even add another If statement if you felt like it:

If bytAge < 21 Then If bytAge > 18 Then MsgBox _
   "You may vote but you may not drink.",, "Underage": End

As you'll see if you're looking at this line in the Visual Basic Editor, there are a couple of problems with this approach:

• First, you need to break long lines of code with the line-continuation character or else they go off the edge of the Code window in the Editor, forcing you to scroll horizontally to read the ends of each line. You could hide all windows except the Code window, use a minute font size for your code, or buy a larger monitor, but you're probably still not going to have any fun working with long lines of code. So, in practice, you don't want to pile statements onto a single code line. The brief End statement is probably the most you'll want to add.
• Second, long lines of code (broken or unbroken) that involve a number of statements tend to become visually confusing. Even if everything is obvious to you when you're entering the code, you may find the code hard to read when you have to debug it a few months later. Usually it's better to use If blocks rather than complex one-line If statements.

If Blocks

Block If constructions work the same way as one-line If statements except blocks contain multiple lines—typically with one command to each line—and they require an End If statement at the end. For example, the one-line If statement from the previous section could also be constructed as an If block like this:

If bytAge < 21 Then
  MsgBox "You may not purchase alcohol.",, "Underage"
  End
End If

If the condition in the first line (the line with the If command) is True, VBA executes the statements within the block If. VBA displays the message box and then executes the End statement.

As you can see from this example, If blocks are much easier to read (and thus easier to debug) than one-line If statements. This is especially true when you nest If statements within one another, which you'll need to do fairly often.

To make If blocks easier to read, the convention is to press the Tab key to indent the lines within the block (VBA ignores the indentation during execution). You can see this indentation in the previous code example.

With short If blocks, like the ones shown in this section, indentation doesn't make a great deal of difference. But with complex If statements, it can make all the difference between clarity and incomprehensibility, as you'll see in “Nesting If Blocks” later in this chapter.

If…Then… Else Statements

If…Then statements are good for taking a single course of action based on a condition, but often you'll need to decide between two courses of action. To do so, you use the If…Then… Else statement.

By using an If…Then… Else statement, you can take one course of action if a condition is True and another course of action if it's False. It's the equivalent of ordinary language, such as If it's raining, Then take an umbrella, Else wear sunscreen.

For example, If…Then… Else statements are a great way to deal with two-button message boxes. If the user clicks the OK button, the code will do one thing. If they click the Cancel button, it will do something different.

SYNTAX

The syntax for the If…Then… Else statement is as follows:

If condition Then
statements1
Else

statements2
End If

If the condition is True, VBA executes statements1, the first group of statements. If the condition is False, VBA moves execution to the Else line and executes statements2, the second group of statements.

Again, you have the option of creating one-line If…Then… Else statements or block If…Then… Else statements. However, it makes more sense to create block If…Then… Else statements because they're much easier to read and debug and because an If…Then… Else structure is inherently longer than an If…Then structure and thus certain to result in an awkwardly long line.

EXAMPLE

As a straightforward example of an If…Then… Else statement, consider the Electronic_Book_Critic procedure shown in Listing 11.1.

 1.  Sub Electronic_Book_Critic()
 2.
 3.    Dim intBookPages As Integer
 4.
 5.    intBookPages = InputBox _
        ("Enter the number of pages in the last book you read.", _
        "The Electronic Book Critic")
 6.    If intBookPages > 1000 Then
 7.       MsgBox "That book is seriously long.", vbOKOnly _
           + vbExclamation, "The Electronic Book Critic"
 8.    Else
 9.       MsgBox "That book is not so long.", vbOKOnly _
           + vbInformation, "The Electronic Book Critic"
10.    End If
11.
12.  End Sub

Here's what happens in Listing 11.1:

• Line 1 starts the procedure, and line 12 ends it. Lines 2, 4, and 11 are spacers.
• Line 3 declares the Integer variable intBookPages. Line 5 then assigns to intBookPages the result of an input box prompting users to enter the number of pages in the last book they read.
• Line 6 checks to see if intBookPages is greater than 1000. If it is, the statement in line 7 runs, displaying a message box that states that the book is long.
• If intBookPages is not greater than 1000, VBA branches to the Else statement in line 8 and executes the statement following it, which displays a message box telling the user that the book wasn't so long.
• Line 10 ends the If condition.

If…Then… ElseIf… Else Statements

The last variation of the If command that you'll look at here is the If… Then… ElseIf… Else block, which you can use to help VBA decide between multiple courses of action. You can use any number of ElseIf lines, depending on how complex the condition is that you need to check.

Again, you could create either one-line If… Then… ElseIf… Else statements or If… Then… ElseIf… Else blocks. However, in almost all cases, If… Then… ElseIf… Else blocks are easier to construct, to read, and to debug. As with the other If statements, one-line If… Then… ElseIf… Else statements don't need an End If statement, but If… Then… ElseIf… Else blocks do need one.

SYNTAX

The syntax for If… Then… ElseIf… Else is as follows:

If condition1 Then
statements1
ElseIf condition2 Then
statements2
[ElseIf condition3 Then
statements3]
[Else
statements4]
End If

If the condition expressed in condition1 is True, VBA executes statements1, the first block of statements, and then resumes execution at the line after the End If clause. If condition1 is False, VBA branches to the first ElseIf clause and evaluates the condition expressed in condition2. If this is True, VBA executes statements2 and then moves to the line after the End If line; if it's False, VBA moves to the next ElseIf clause (if there is one) and evaluates its condition (here, condition3) in turn.

If all the conditions in the ElseIf statements prove False, VBA branches to the Else statement (if there is one) and executes the statements after it (here, statements4). The End If statement then terminates the conditional statement, and execution resumes with the line after the End If.

The Else clause is optional, although in many cases it's a good idea to include it to let VBA take a different course of action if none of the conditions specified in the If and ElseIf clauses turns out to be True.

You can have any number of ElseIf clauses in an If block, each with its own condition. But if you find yourself needing to use If statements with large numbers of ElseIf clauses (say, more than 5 or 10), you may want to try using the Select Case command instead, which you'll look at toward the end of the chapter.

EXAMPLES

This section shows you two examples of If…Then… ElseIf… Else statements:

• A simple If…Then… ElseIf… Else statement for taking action based on which button the user clicks in a three-button message box
• An If…Then… ElseIf statement without an Else clause

A Simple If…Then… ElseIf… Else Statement

A simple If…Then… ElseIf… Else statement, as used in Listing 11.2, is perfect for dealing with a three-button message box.

 1.  Sub Creating_a_Document()
 2.
 3.    Dim lngButton As Long
 4.    Dim strMessage As String
 5.
 6.    strMessage = "Create a new document based on the " & _
        "VP Report project?" & vbCr & vbCr & _
        "Click Yes to use the VP Report template." & vbCr & _
        "Click No to use a blank document." & vbCr & _
        "Click Cancel to stop creating a new document."
 7.
 8.    lngButton = MsgBox _
        (strMessage, vbYesNoCancel + vbQuestion, "Create New Document")
 9.
10.    If lngButton = vbYes Then
11.      Documents.Add Template:= "z:public	emplatevpreport.dotm"
12.    ElseIf lngButton = vbNo Then
13.      Documents.Add
14.    Else  'lngButton is vbCancel
15.      End
16.    End If
17.
18.  End Sub

The Creating_a_Document procedure in Listing 11.2 displays a Yes/No/Cancel message box inviting the user to create a new document based on the VP Report project. The user can choose the Yes button to create such a document, the No button to create a blank document, or the Cancel button to cancel out of the procedure without creating a document at all.

Here's what happens:

• Line 1 starts the procedure, and line 18 ends it.
• Line 2 is a spacer, after which line 3 declares the Long variable lngButton and line 4 declares the String variable strMessage. Line 5 is another spacer.
• Line 6 assigns to the String variable strMessage a long string that contains all the text for the message box. Line 7 is another spacer.
• Line 8 displays the message box, using strMessage as the prompt, specifying the vbYesNoCancel constant to produce a Yes/No/Cancel message box, and applying a suitable title (Create New Document). It assigns the result of the message box to the Long variable lngButton. Line 9 is a spacer.
• Line 10 starts the If…Then… ElseIf… Else statement, comparing the value of lngButton to vbYes.

  If line 10 matches, line 11 uses the Add method of the Documents object to create a new document based on the vpreport.dotm template. If not, the ElseIf condition in line 12 is evaluated, comparing the value of lngButton to vbNo. If you run this procedure and choose the Yes button in the message box, you will need to have a template named vpreport.dotm in the folder z:public emplate for line 11 to run. If you don't have the template, you'll get an error. Given that you're unlikely to have this template, you might want to change the path and filename to a template that you do have.

• If this second comparison matches, line 13 uses the Add method of the Documents object to create a new blank document. If not, the Else statement in line 14 is activated because the user must have chosen the Cancel button in the message box. The End statement in line 15 ends execution of the procedure.
• Line 16 ends the If statement. Line 17 is a spacer.

This example is a little unusual in that the Else statement is limited to three possible branches because that's the number of possible responses from a message box—Yes, No, and Cancel.

Because the If statement checks for the vbYes response and the ElseIf statement checks for the vbNo response, only the vbCancel response will trigger the Else statement.

In other circumstances, the Else statement can serve as a catchall for anything not caught by the If and ElseIf statements above the Else, so you need to make sure the If and ElseIf statements cover all the contingencies you want evaluated before the Else statement kicks in. So, put the Else statement at the bottom of the block. For example, if you quiz the reader about the colors of the US flag, you must provide If and ElseIf statements for red, white, and blue. If you omit, for example, white (one of the possibilities), and the user types in white, your code will fall through to the Else statement, which might display an incorrect message such as “The color you entered is not on the flag.”

An If…Then… ElseIf Statement without an Else Clause

You can use an If…Then… ElseIf statement without an Else clause when you don't need to take an action if none of the conditions in the If statement proves True. In the previous example, the situation had three clearly defined outcomes: the user could choose the Yes button, the No button, or the Cancel button in the message box. So you were able to use an If clause to test whether the user chose the Yes button, an ElseIf clause to test whether the user chose the No button, and an Else clause to test whether neither was chosen (meaning that the Cancel button was chosen). (Clicking the close button [x] on the title bar of a message box is the equivalent of choosing the Cancel button in the message box.)

As an example of a situation in which you don't need to take action if no condition is True, consider the If statement in the Check_Password procedure in Listing 11.3. This procedure checks to ensure that the password a user enters to protect an item is of a suitable length.

 1.  Sub Check_Password()
 2.
 3.    Dim strPassword As String
 4.
 5.  BadPassword:
 6.
 7.    strPassword = InputBox _
        ("Enter the password to protect this item from changes:", _
        "Enter Password")
 8.
 9.    If Len(strPassword) = 0 Then
10.      End
11.    ElseIf Len(strPassword) < 6 Then
12.      MsgBox "The password you chose is too short." & vbCr _
          & vbCr & "Please choose a password between " & _
          "6 and 15 characters in length.", _
         vbOKOnly + vbCritical, "Unsuitable Password"
13.      GoTo BadPassword
14.    ElseIf Len(strPassword) > 15 Then
15.       MsgBox "The password you chose is too long." & vbCr _
          & vbCr & "Please choose a password between " & _
          "6 and 15 characters in length.",
          vbOKOnly + vbCritical, "Unsuitable Password"
16.       GoTo BadPassword
17.    End If
18.
19.  End Sub

This procedure forces users to enter an acceptable password. Here's what happens:

• Line 1 starts the procedure, and line 19 ends it.
• Line 2 is a spacer, after which line 3 declares the String variable strPassword.
• Line 4 is a spacer. Line 5 contains a label, BadPassword, to which VBA will loop if the password the user enters proves to be unsuitable. Line 6 is another spacer.
• Line 7 displays an input box prompting the user to enter a password, which VBA stores in the variable strPassword. Line 8 is a spacer.
• Line 9 checks strPassword to see if its length is zero, which means it's an empty string. This could mean that either the user clicked the Cancel button in the input box or the user clicked the OK button without entering any text in the text box of the input box. Either of these actions causes VBA to branch to line 10, where it executes the End statement that ends execution of the procedure.
• If the length of strPassword isn't zero (that is, the user has entered text into the text box of the input box and clicked the OK button), the If clause in line 9 is False and VBA moves to line 11, where it checks to see if the length of strPassword is less than 6 characters.
• If the length of strPassword is zero, VBA executes the code in lines 12 and 13. Line 12 displays a message box telling the user that the password is too short and specifying the length criteria for the password. This message box contains only an OK button, so when the user clicks it to continue, VBA continues with line 13, which returns execution to the BadPassword label on line 5. From there the procedure repeats itself, redisplaying the input box so that the user can try again.
• If the length of strPassword isn't more than 15 characters, execution passes from line 11 to the second ElseIf clause in line 14, where VBA checks to see if the length of strPassword is more than 15 characters.
• If the length of strPassword is more than 15 characters, VBA executes the code in lines 15 and 16: Line 15 displays a message box (again, with only an OK button) telling the user that the password is too long, and line 16 returns execution to the BadPassword label, again displaying the input box.

There's no need for an Else statement in this case because once the user has supplied a password that doesn't trigger the If clause or either of the ElseIf clauses, execution moves out of the If block and continues at the line after the End If statement.

Creating Loops with If and GoTo

So far in this book, you've seen several examples of For… Next loops and For Each… Next loops. (Chapter 12, “Using Loops to Repeat Actions,” shows you how to construct these types of loops and other types, such as Do loops.) You can also create loops with If statements and the GoTo statement, as you did in the last example.

Many teachers and programmers frown upon making loops with If and GoTo. It's bad practice because If… GoTo loops can create “spaghetti code” (execution paths that jump around and are hard to visualize). Such paths can be not only grotesque in themselves, but also a nightmare to debug.

However, simple versions of If and GoTo loops can work perfectly well, so even if you choose not to use this technique yourself, you should at least know how such loops work. Whether or not to ban GoTo from your code is a matter of personal preference, company policy, or your teacher's beliefs.

If nothing else, you might one day be responsible for working with someone else's code—someone whose standards aren't as rigorous as yours regarding the notorious GoTo command. So let's take a brief look at how GoTo can be used.

SYNTAX

The GoTo statement is straightforward, and can be useful—it's already been used several times in the examples you've looked at so far in this book (in Listings 7.2 and 9.2, for example). The syntax is as follows:

GoTo line

Here, the line argument can be a line label (or, rarely these days, a line number) within the current procedure.

A line number is simply a number placed at the beginning of a line to identify it. For example, consider this demonstration of GoTo:

Sub Demo_of_GoTo()
1
  If MsgBox("Go to line 1?", vbYesNo) = vbYes Then
    GoTo 1
  End If
End Sub

The second line here contains only the line number 1, which identifies the line. The third line displays a message box offering the choice of going back to line 1; if the user chooses the Yes button, VBA executes the GoTo 1 statement and returns to the line labeled 1, after which it displays the message box again. (If the user chooses the No button, the If block is exited.)

However, it's usually better to use a line label than a line number. A line label is a name for a line. A label starts with a letter and ends with a colon. Between the letter and the colon, the label can consist of any combination of characters. For example, earlier in this chapter you saw the label BadPassword: used to loop back to an earlier stage in a procedure when certain conditions were met. Perhaps the quintessential example of a label is the Bye: label traditionally placed at the end of a procedure for use with this GoTo statement:

GoTo Bye

When this label is placed just above the End…Sub command, it simply exits the macro.

GoTo is usually used with a condition. If you use it without a condition to go back to a line earlier in the code than the GoTo statement, you're apt to create an infinite loop (this bug is discussed in Chapter 12). And if you were to use the GoTo Bye statement without a condition, you would guarantee that your procedure would stop executing—no statement after this line would ever be executed. You would be jumping to the end of the macro.

EXAMPLE

As an example of a GoTo statement with a condition, you might use a GoTo Bye statement together with a message box that makes sure that the user wants to run a certain procedure:

Response = MsgBox("Do you want to create a daily report for " & _
  "the head office from the current document?", _
  vbYesNo + vbQuestion, "Create Daily Report")
If Response = vbNo Then GoTo Bye

If the user chooses the No button in the message box that the first line displays, VBA executes the GoTo Bye statement, branching to the Bye: label located at the end of the subroutine.

Nesting If Blocks

You can nest If blocks (put one inside another) as needed to manage any contortions required in your code. Each nested If block must be complete in and of itself. (This means each nested block must start with an If and conclude with its own End…If.)

For example, if you nest one If block within another If block (but forget the End If that concludes the nested If), VBA assumes that the End If line for the outer If actually pairs with the nested If. That's so wrong.

To make your If blocks easy to read, indent them to different levels. This is particularly important when nesting If blocks. Indenting provides you with visual cues, making it clear which If line is paired with each End If line. In other words, indentation makes the various If blocks stand out.

To see how this is done, check out the following nested If statements:

 1. If condition1 Then        'start of first If
 2.   If condition2 Then     'start of second If
 3.     If condition3 Then   'start of third If
 4.       statements1
 5.     ElseIf condition4 Then 'ElseIf for third If
 6.       statements2
 7.     Else          'Else for third If
 8.       statements3
 9.     End If         'End If for third If
10.   Else            'Else for second If
11.     If condition5 Then   'start of fourth If
12.       statements4
13.     End If         'End If for fourth If
14.   End If           'End If for second If
15. Else              'Else for first If
16.   statements5
17. End If             'End If for first If

By following the layout, you can easily trace the flow of execution. For example, if condition1 in line 1 is False, VBA branches to the Else statement in line 15 and continues execution from there. If condition1 in line 1 is True, VBA evaluates the nested condition2 in line 2, and so on.

The indentation is for visual clarity only—VBA pays no attention to it—but it can be a great help to the human reader. The previous nested If commands are also annotated with comments so that you can see which Else, ElseIf, and End If line belongs with which If line. However, with the indentation, commenting is unnecessary.

By contrast, check out the unindented version of these nested blocks. This version is hard for the human eye to follow—and is even harder when it's buried in a morass of other code:

 1. If condition1 Then
 2. If condition2 Then
 3. If condition3 Then
 4. statements1
 5. ElseIf condition4 Then
 6. statements2
 7. Else
 8. statements3
 9. End If
10. Else
11. If condition5 Then
12. statements4
13. End If
14. End If
15. Else             '
16. statements5
17. End If

There's seldom a pressing need to nest multiple If blocks. Often, you'll need only to nest a simple If…Then statement within an If…Then… Else statement or within an If…Then… ElseIf… Else statement. Listing 11.4 shows an example using Word.

 1. Selection.HomeKey Unit:=wdStory
 2. Selection.Find.ClearFormatting
 3. Selection.Find.Style = ActiveDocument.Styles("Heading 5")
 4. Selection.Find.Text = " "
 5. Selection.Find.Execute
 6. If Selection.Find.Found Then
 7.   lngResponse = MsgBox("Make this into a special note?", _
       vbOKCancel, "Make Special Note")
 8.   If lngResponse = vbOK Then
 9.     Selection.Style = "Special Note"
10.   End If
11. End If

The code in Listing 11.4 searches through the active document for the Heading 5 style and, if it finds the style, displays a message box offering to make it into a special note by applying the Special Note style. Here's what happens:

• Line 1 starts by returning the insertion point to the beginning of the document.
• Line 2 clears formatting from the Find command (to make sure that it isn't searching for inappropriate formatting).
• Line 3 sets Heading 5 as the style for which the Find command is searching, and Line 4 sets the search string as an empty string (" ").
• Line 5 then runs the Find operation.
• Lines 6 through 11 contain the outer If…Then loop. Line 6 checks to see if the Find operation in line 5 found a paragraph in Heading 5 style. If it did, VBA runs the code in lines 7 through 10.
• Line 7 displays a message box asking if the user wants to make the paragraph into a special note.
• Line 8 begins the nested If…Then statement and checks the user's response to the message box.
• If the user's response is a vbOK—if the user chose the OK button—VBA executes the statement in line 9, which applies the Special Note style (which I'll assume is included in the styles available to the current document or template) to the paragraph.
• Line 10 contains the End If statement for the nested If…Then block, and line 11 contains the End If statement for the outer If…Then block.

If you expect a document to contain more than one instance of the Heading 5 style, use a Do While… Loop loop to search for each instance. See Chapter 12 for details on Do While… Loop loops.

Select Case Blocks

The Select Case block provides an effective alternative to complex multiple If…Then blocks or multiple ElseIf statements. Select Case combines the same decision-making capability of If constructions with tighter and more readable code.

Use the Select Case statement when the decision you need to make is complicated because it involves more than two or three different values that are being evaluated

Select Case blocks are easier to read than complex If…Then blocks, mostly because there's less code. This also makes Select Case blocks easier to modify: when you need to adjust one or more of the values used, you have less code to wade through.

Syntax

The syntax for Select Case is as follows:

Select Case TestExpression
  Case Expression1
Statements1
  [Case Expression2
Statements2]
  [Case Else
StatementsElse]
End Select

Here's how the syntax breaks down:

• Select Case starts the block, and End Select ends it.
• TestExpression is the expression that determines which of the Case statements executes.
• Expression1, Expression2, and so on are the expressions against which VBA matches TestExpression.

For example, you might test to see which of a number of buttons in a user form the user chose. The TestExpression would be tied to a button that's been chosen; if it were the first button, VBA would match that to Expression1 and would run the statements in the lines following Case Expression1; if it were the second button, VBA would match that to Expression2 and would run the statements in the lines following Case Expression2; and so on for the rest of the Case blocks.

Case Else is similar to the Else clause in an If block. Case Else is an optional clause that (if it's included) runs if none of the given expressions is matched.

Example

As an example of a Select Case block, consider Listing 11.5, which prompts users to enter their typing speed and then displays an appropriate response.

 1.  Sub Check_Typing_Speed()
 2.
 3.    Dim varTypingSpeed As Variant

 4.    Dim strMsg As String
 5.
 6.    varTypingSpeed = InputBox _
        ("How many words can you type per minute?", "Typing Speed")
 7.    Select Case varTypingSpeed
 8.      Case " "
 9.        End
10.      Case Is < 0, 0, 1 To 50
11.        strMsg = "please learn to type properly before " & _
           "applying for a job."
12.      Case 50 To 60
13.        strMsg = "Your typing could do with a little brushing up. "
14.      Case 60 To 75
15.        strMsg = "We are satisfied with your typing speed."
16.      Case 75 To 99
17.        strMsg = "Your typing is more than adequate. "
18.      Case 100 To 200
19.        strMsg = "You wear out keyboards with your blinding speed."
20.      Case Is > 200
21.        strMsg = "I doubt that's true."
22.    End Select
23.
24.    MsgBox strMsg, vbOKOnly, "Typing Speed"
25.
26.  End Sub

Here's what happens in the Check_Typing_Speed procedure in Listing 11.5:

• Line 1 starts the procedure, and line 26 ends it.
• Line 2 is a spacer. Line 3 declares the Variant variable varTypingSpeed, and line 4 declares the String variable strMsg. Line 5 is another spacer.
• Line 6 displays an input box prompting the user to enter their typing speed. It stores this value in the variable varTypingSpeed.
• Line 7 begins the Select Case block, predicating it on the variable varTypingSpeed.
• Next, VBA evaluates each of the Case clauses in turn until it finds one that proves True. The first Case clause, in line 8, compares varTypingSpeed to an empty string (" ") to see if the user chose the Cancel button in the input box or clicked the OK button without entering a value in the text box. If Case " " is True, VBA executes the End statement in line 9, ending the procedure.
• If Case " " is False, VBA moves execution to the next Case clause—line 10 in this example—where it compares varTypingSpeed to three items: less than 0 (Is < 0), 0, and the range 1 to 50 words per minute. Notice three things here:

  1. You can include multiple comparison items in the same Case statement by separating them from each other with commas.

  2. Using the Is keyword with the comparison operator (here, the less than operator) checks the relation of two numbers to each other.

  3. The To keyword denotes the range of values.

• If varTypingSpeed matches one of the comparison items in line 10, VBA assigns to the String variable strMsg the text on line 11 and then continues execution at the line after the End Select statement.
• If varTypingSpeed isn't within this range, VBA moves to the next Case clause and evaluates it in turn. When VBA finds a Case clause that's True, it executes the statement following that clause (in this case, assigning a text string to the strMsg variable) and then continues execution at the line after the End Select statement.
• For any case other than that in line 8 (which ends the procedure), line 24 displays a message box containing the text stored in the statement strMsg.

A Select Case block can be a good way of specifying which action to take based on the user's choice from a ListBox or ComboBox control (these controls are explored in Chapter 14, “Creating Simple Custom Dialog Boxes”). Typically, a list box or combo box displays a list of many different options, such as all the states in the USA. After the user clicks to select an item within a ListBox or ComboBox control, the chosen item appears in the control's Value property. Your macro could then check this Value property as the test expression in your Select Case block and take action accordingly.

When Order Matters

One final point about complex test structures. You need to ensure that your Select Case and If…Then… Else statements (or other multiple If structures) evaluate their test conditions in the appropriate order. This means that each condition to be evaluated must exclude all the conditions that follow it.

Let's say you're asking the user how old they are. And you set up your test cases like this:

1.  Age = InputBox ("How old are you?")
2.
3.  Select Case Age
4.
5.    Case < 50
6.      strMsg = "You're nearing retirement."
7.
8.    Case < 12
9.      strMsg = "Hello, youngster."

This is a logic bug. And a bad one. Line 8 can never execute because everyone under 50, including those younger than 12, will trigger line 5. (The expression “less than 50” includes “less than 12.")

To work properly, these tests must be reversed, like this:

Case < 12
   strMsg = "Hello, youngster."

Case < 50
   strMsg = "You're nearing retirement."

You can avoid this problem entirely by testing for equality or a range, as illustrated in Listing 11.5:

Case 50 To 60

The Bottom Line

Use comparison operators. Comparison operators compare items using such tests as greater than or not equal to.

Master It Write a line of code that uses a less than comparison to test whether a variable named Surplus is less than 1200.

Compare one item with another. You can compare strings using less than and more than comparison operators.

Master It What symbol do you use to determine if VariableA is lower in the alphabet than VariableB?

Test multiple conditions. To test multiple conditions, you use VBA's logical operators to link the conditions together.

Master It Name two of the most commonly used logical operators.

Use If blocks. If blocks are among the most common programming structures. They are often the best way to allow code to make decisions. To test two conditions, use If… Else… EndIf.

Master It Write an If… Else… End If block of code that displays two message boxes. If the temperature (the variable Temp) is greater than 80, tell the user that it's hot outside. Otherwise, tell the user that it's not that hot.

Use Select Case blocks. Select Case structures can be a useful alternative to If blocks.

Master It When should you use a Select Case structure?