Chapter 11. Making Decisions in Your Code

Computers behave intelligently in large part because programming languages include commands that test conditions, then branch to appropriate sections of code based on the results of that test. It's similar to human decision making: if it's raining, then take an umbrella.

This chapter shows you the conditional expressions that VBA provides for creating 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 statements and Select Case statements. You'll find several types of If statements suitable for making typical decisions as well as the heavy-duty Select Case statement for efficiency 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 statements, which take up the bulk of the chapter. At the end of the chapter, you'll learn about Select Case statements.

In this chapter you will learn to do the following:

  • Use comparison operators

  • Compare one item with another

  • Test multiple conditions

  • Use If statements

  • Use Select Case statements

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.

Table 11.1. VBA's comparison operators

Operator

Meaning

Example

=

Equal to

If strMyString="Hello" Then

<>

Not equal to

If x <> 5 Then

<

Less than

If y < 100 Then

>

Greater than

If strMyString > "handle" Then

<=

Less than or equal to

If intMyCash <= 10 Then

>=

Greater than or equal to

If Time >= 12:00 PM Then MsgBox "It's afternoon." Else MsgBox "It's morning." End If

Is

Is the same object variable as

If Object1 Is Object2 Then

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. Mixed expressions (numbers and letters) are evaluated in alphabetical order as well: Office 97 is "greater than" Office 2010 because 9 is greater than 2.

Is, the seventh comparison operator, is less familiar. 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.

Table 11.2. VBA's logical operators

Operator

Meaning

Example

Comments

And

Conjunction

If ActiveWorkbook.FullName = "c: empExample.xlsm" And Year(Date) >= 2005 Then

If both conditions are True, the result is True. If either condition is False, the result is False.

Not

Negation

ActivePresentation.Saved = Not ActivePresentation.Saved

Not reverses the value of x (True becomes False; False becomes True). The Saved property used in this example is Boolean.

Or

Disjunction

If ActiveWindow.View = wdPageView Or ActiveWindow.View = wdOutlineView Then

If either the first condition or the second is True, or if both conditions are True, the result is True.

XOr

Exclusion

If Salary > 55000 XOr Experienced = True Then

Tests for different results from the conditions: Returns True if one condition is False and the other is True; returns False if both conditions are True or both conditions are False.

Eqv

Equivalence

If blnMyVar1 Eqv blnMyVar2 Then

Tests for logical equivalence between the two conditions: If both values are True, or if both values are False, Eqv returns True. If one condition is logically different from the other (that is, if one condition is True and the other is False), Eqv returns False.

Imp

Implication

If blnMyVar1 Imp blnMyVar2 Then

Tests for logical implication. Returns True if both conditions are True, both conditions are False, or the second condition is True. Returns Null if both conditions are Null or if the second condition is Null. Otherwise, returns False.

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 occasions. (If the Imp logical operator doesn't make sense to you at this point, you probably don't need to use it.)

VBA Doesn't Do Short-Circuit Evaluation

Here's something to beware of when evaluating multiple conditions: VBA doesn't do short-circuit evaluation in logical expressions (unlike other programming languages such as C and C++).

Short-circuit evaluation is the formal term for a simple logical technique most people use several times a day when making decisions in their daily lives: If the first of two or more complementary conditions is false, you typically don't waste time evaluating any other conditions contingent upon it.

For example, suppose your most attractive coworker says they'll take you to lunch if you get the product out on time and get a promotion. If you don't get the product out on time, you've blown your chances—it doesn't much matter if you get the promotion because even if you do, your lunch will still be that brown bag you forgot to put in the department fridge. There's no point in evaluating the second condition because it depends on the first, and the first condition wasn't met. You can just short-circuit any further condition testing.

VBA doesn't think that way. It evaluates the second condition (and any subsequent conditions) whether or not it needs to. Evaluating all conditions takes a little more time (which isn't usually an issue), but it can introduce unexpected complications in your code (which can be an issue). For example, the following snippet produces an error when the selection is only one character long. The error occurs because the code ends up running the Mid function on a zero-length string (the one-character selection minus one character)—even though you wouldn't expect this condition to be evaluated when the first condition is not met (because the length of the selection is not greater than 1):

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

To avoid problems such as this, use nested If statements. In the following code example, the first condition isn't met (again, for a one-character selection), so the second condition isn't evaluated. Notice that one If statement here is nested within (contained within) the other If statement:

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

Using Not to Toggle Boolean Properties

Here's a useful tip. The Not command is a handy way of turning True to False and False to True. By using Not with a Boolean variable or property, you can toggle the state of the variable or property without even needing to check what the current state is. For example, in Excel, you could create an If structure to toggle the value of the Boolean property Saved (which controls whether Excel thinks the document in question contains unsaved changes) by using code such as this:

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

But you can achieve the same toggling effect much more simply by using Not as shown in the following code:

ActiveWorkbook.Saved = Not ActiveWorkbook.Saved

If Statements

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

In the sections that follow, you'll look at the following types of If statements:

  • 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 block statement 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

If... Then statements can be laid out either on one line or on multiple lines. A one-line If... Then statement looks like this:

If condition Then statement[s]

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 those statement or statements.

A multiple-line If... Then statement (more properly known as a block If statement) looks like this:

If condition Then
statement
    [statements]
End If

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

Single-Line If Statements Don't Use End If

Remember that a single-line If... Then statement has no End If to end it, whereas the block If statement requires an End If. VBA knows that a single-line If condition will end on the same line on which it starts, whereas a block If statement needs to have its end clearly specified. Block If statements tend to be easier for humans to read.

Examples

In the previous chapters, you've already encountered a number of If statements—they're so necessary in VBA 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 in context:

Dim bytAge As Byte
bytAge = InputBox("Enter your age.", "Age")
If bytAge < 21 Then MsgBox "You may not purchase alcohol.",, "Underage"

The first line declares the Byte 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 bytAge and displays an Underage message box if bytAge is less than 21.

A single-line If statement can sometimes be a good candidate for including multiple statements in the same line of code (by separating them with a colon).

For example, if you wanted to end the procedure 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.) 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 Visual Basic 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.

  • 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 block If statements rather than complex one-line If statements.

Block If Statements

Block If statements work the same way as one-line If statements except they're laid out on 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 a block If:

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

If the condition in the first line is True, VBA executes the statements within the block If, first displaying the message box and then executing the End statement.

As you can see from this example, block If statements 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 often need to do.

To make block If statements 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 statements, 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 Statements" 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 usage, such as If you're good you'll get presents, else be prepared to get nothing. For example, If... Then... Else statements are a great way to deal with two-button message boxes. If the user clicks the OK button, do one thing. If they click the Cancel button, do something different.

Use If... Then... Else with Clear-Cut True/False Situations

The If... Then... Else statement is best used with clear-cut binary conditions—those that lend themselves to a true/false analysis. (Recall that a binary condition is like a two-position switch—if it's not switched on, it must be switched off.) For more complex conditions, such as those that can have three or more positions, you need to use a more complex logical statement, such as If... Then... ElseIf... Else or Select Case. We'll get to these structures later in this chapter. Note also that you need to set up If... Then... Else statements so that they evaluate the conditions in the appropriate order. This means that each condition to be evaluated must exclude all the conditions that follow it. More on this later as well.

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. In almost all circumstances, 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 more likely to produce 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.

Example 11.1. A Simple If...Then example

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 If block you'll look at here is If... Then... ElseIf... Else, 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 block If... Then... ElseIf... Else statements. However, in almost all cases, block If... Then... ElseIf... Else statements 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 block If... Then... ElseIf... Else statements 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 a block If statement, 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 statement 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.

Example 11.2. Understanding the If...Then...ElseIf...Else structure

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 Thenfff
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 by the number of possible responses from the 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. For example, if you quiz the reader about the colors of the U.S. 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 for the user's having chosen the Yes button, an ElseIf clause to test for the user's having chosen the No button, and an Else clause to take action if 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.

Example 11.3. Taking no action when no condition is true

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 a suitable password for the item they're supposed to protect. (The procedure doesn't actually protect the item.) 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 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 programmers frown upon creating loops with If and GoTo because there are neater ways to create loops and because If... GoTo loops can create "spaghetti code" that is not only grotesque to contemplate but a nightmare to debug. But loops using If and GoTo work perfectly well, so even if you choose not to use this technique yourself, you should at least know how such loops work.

Syntax

The GoTo statement is straightforward, and it's so useful that it's already come up a number of 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 either a line number or a line label 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 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 statement ends.)

However, it's usually easier 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

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 end at this statement—no statement after this line would ever be executed.

Example

As an example of a GoTo statement with a condition, you might use the 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 Statements

You can nest If statements (put one inside another) as necessary to produce the logical contortions you need in your code. Each nested If statement must be complete in and of itself. (This means each nested statement must start with an If and conclude with its own End If.)

For example, if you nest one block If statement within another block If statement and forget the End If line for the nested If, VBA assumes that the End If line for the outer If belongs to the nested If.

To make your block If statements easy to read, indent them to different levels. This is particularly important with nesting If statements, when you need to make it clear which If line is paired with each End If line. 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—it makes no difference to VBA—but it can be a great help to the human reader. The previous nested If statement is annotated with comments so that you can see which Else, ElseIf, and End If line belongs with which If line, although with the indentation, doing so is unnecessary. On the other hand, check out the unindented version of this nested statement. 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       '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

There's seldom a pressing need to nest many levels of If statements. 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.

Example 11.4. Nesting an If...Then statement

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 statement, and line 11 contains the End If statement for the outer If... Then statement.

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 Statements

The Select Case statement provides an effective alternative to multiple ElseIf statements, combining the same decision-making capability with tighter and more efficient code. Many people also find Select Case easier to read.

Use the Select Case statement when the decision you need to make in the code depends on one variable or expression that has more than two or three different values that you need to evaluate. This variable or expression is known as the test case.

Select Case statements are easier to read than complex If... Then statements, mostly because there's less code. This also makes them 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 statement, and End Select ends it.

  • TestExpression is the expression that determines which of the Case statements runs.

  • 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 statements.

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 statement, consider Listing 11.5, which prompts users to enter their typing speed and then displays an appropriate response.

Example 11.5. Working with a Select Case structure

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 statement, 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 statement can be a good way of specifying which action to take based on the user's choice in a ListBox or ComboBox control (these controls are explored in Chapter 14). Typically, a list box or combo box displays a list of many different items, such as all the countries of the world. After the user clicks to select an item within a ListBox or Combo control, the selected item appears in the control's Value property. You could check the Value property of one of these controls as the test case in your Select Case block and take action accordingly.

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 statements

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... End If.

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 statements

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

Master It

When should you use a Select Case structure?

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
13.59.55.72