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:
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.
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.)
Real World Scenario
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 dependent 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 blocks. 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 of the If blocks here is nested within (contained within) the other If block:
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
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 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.)
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.
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 If block requires an End If. VBA knows that a single-line If condition will end on the same line on which it starts. But an If block needs to have its end clearly specified so VBA knows which code to skip over if the condition evaluates to False. If blocks tend to be easier for humans to read.
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.
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:
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:
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 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.
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 light switch—if it's not switched on, it must be switched off.) For more complex conditions, such as switches 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.
The syntax for the If…Then… Else statement is as follows:
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.
As a straightforward example of an If…Then… Else statement, consider the Electronic_Book_Critic procedure shown in Listing 11.1.
LISTING 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:
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.
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.
This section shows you two examples of If…Then… ElseIf… Else statements:
A simple If…Then… ElseIf… Else statement, as used in Listing 11.2, is perfect for dealing with a three-button message box.
LISTING 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 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:
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.
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.”
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.
LISTING 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 an acceptable password. Here's what happens:
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.
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.
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.
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.
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.
LISTING 11.4: Nesting an If…Then block
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:
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 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.
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:
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.
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.
LISTING 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:
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.
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.
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:
You can avoid this problem entirely by testing for equality or a range, as illustrated in Listing 11.5:
Case 50 To 60
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?
3.145.77.21