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
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 |
|
| Not equal to |
|
| Less than |
|
| Greater than |
|
| Less than or equal to |
|
| Greater than or equal to |
|
| Is the same object variable as |
|
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.
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 |
---|---|---|---|
| Conjunction |
| If both conditions are |
| Negation |
|
|
| Disjunction |
| If either the first condition or the second is |
| Exclusion |
| Tests for different results from the conditions: Returns |
| Equivalence |
| Tests for logical equivalence between the two conditions: If both values are |
| Implication |
| Tests for logical implication. Returns |
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.)
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
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.)
If... Then
statements can be laid out either on one line or on multiple lines. A one-line If... Then
statement looks like this:
Ifcondition
Thenstatement[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:
Ifcondition
Thenstatement
[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.
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.
Here's an example of a one-line If
statement in context:
Dim bytAge As Byte bytAge = InputBox("Enter your age.", "Age")If
bytAge < 21Then
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:
First, it evaluates the condition.
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.
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 < 21Then If
bytAge > 18Then
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 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
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.
The syntax for the If... Then... Else
statement is as follows:
Ifcondition
Thenstatements1
Elsestatements2
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.
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.
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.
The syntax for If... Then... ElseIf... Else
is as follows:
Ifcondition1
Thenstatements1
ElseIfcondition2
Thenstatements2
[ElseIfcondition3
Thenstatements3
] [Elsestatements4
] 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.
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, 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."
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.
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.
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.
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.
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.
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.
The syntax for Select Case
is as follows:
Select CaseTestExpression
CaseExpression1
Statements1
[CaseExpression2
Statements2
] [Case ElseStatementsElse
] 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.
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:
You can include multiple comparison items in the same Case
statement by separating them from each other with commas.
Using the Is
keyword with the comparison operator (here, the less than operator) checks the relation of two numbers to each other.
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.
Comparison operators compare items using such tests as greater than or not equal to.
Write a line of code that uses a less than comparison to test whether a variable named Surplus
is less than 1200.
You can compare strings using less than and more than comparison operators.
What symbol do you use to determine if VariableA
is lower in the alphabet than VariableB
?
To test multiple conditions, you use VBA's logical operators to link the conditions together.
Name two of the most commonly used logical operators.
If
statementsIf
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
.
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.
Select Case
statementsSelect Case
structures can be a useful alternative to If
blocks.
When should you use a Select Case
structure?
3.138.37.151