VBA Brush Up 05：Decision Making with VBA

Technorati 標籤: VBA,if then,select case

來自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p76-p90

（1）if then結構：單行的不需要end if，多行的需要；單行的如果需要執行多個語句，則加冒號

1. Sub SimpleIfThen2()
3.     Dim weeks As String
5.     On Error GoTo VeryEnd 
7.     weeks = InputBox("How many weeks are in a year:", "Quiz") 
8.     If weeks <> 52 Then MsgBox "Try Again":SimpleIfThen2 
9.     If weeks = 52 Then MsgBox "Congratulations!"
11. VeryEnd: 
12. End Sub 

If condition Then statement1 Else statement2

（2）elseif以及Nested If…Then Statements的用法

1. Private Sub cmdOK_Click()
3.     If txtPwd = "FOX" Then 
4.         MsgBox "You are not authorized to run this report." 
5.     ElseIf txtPwd = "DOG" Then 
6.         If txtUser = "John" Then
7.             MsgBox "You are logged on with restricted privileges." 
8.         ElseIf txtUser = "Mark" Then 
9.             MsgBox "Contact the Admin now." 
10.         ElseIf txtUser = "Anne" Then 
11.             MsgBox "Go home." 
12.         Else 
13.             MsgBox "Incorrect User name."
14.         End If 
15.     Else 
16.         MsgBox "Incorrect password or user name" 
17.     End If 
19.     Me.txtUser.SetFocus
21. End Sub

Here’s the syntax of the If…Then…ElseIf statement:

1. If condition1 Then 
2.     statements to be executed if condition1 is True 
3. ElseIf condition2 Then 
4.     statements to be executed if condition2 is True 
5. ElseIf condition3 Then 
6.     statements to be executed if condition3 is True 
7. ElseIf conditionN Then 
8.     statements to be executed if conditionN is True 
9. Else 
10.     statements to be executed if all conditions are False 
11. End If 

The Else clause if optional; you can omit it if there are no actions to be executed when all conditions are false. If the condition is true, Visual Basic will execute the statements between Then and Else, and will ignore the statement between Else and End If. If the condition is false, Visual Basic will omit the statements between Then and Else, and execute the statement between Else and End If.

（3）Select Case Statement：To avoid complex nested If statements that are difficult to follow, you can use the Select Case statement instead. The syntax of this statement is as follows, The Case Else clause is optional. VB執行完match的case之後就立刻退出select.

1. Select Case testExpression 
2.     Case expressionList1 
3.         statements if expressionList1 matches testExpression 
4.     Case expressionList2 
5.         statements if expressionList2 matches testExpression 
6.     Case expressionListN 
7.         statements if expressionListN matches testExpression 
8.     Case Else 
9.         statements to be executed if no values match testExpression 
10. End Select

（4）在case裡用Is做判斷、以及Specifying a Range of Values in a Case Clause。once Visual Basic locates a Case clause with a true condition, it doesn’t bother to look at the remaining Case clauses. It jumps over them and continues to execute the procedure with the instructions that may follow the End Select statement.

1. Select Case unitsSold 
2.     Case 1 To 100 
3.         Discount = 0.05 
4.     Case Is <= 500 
5.         Discount = 0.1 
6.     Case 501 To 1000 
7.         Discount = 0.15 
8.     Case Is >1000 
9.         Discount = 0.2 
10. End Select

（5）Specifying Multiple Expressions in a Case Clause。You may specify multiple conditions within a single Case clause by separating each condition with a comma。The commas used to separate conditions within a Case clause have the same meaning as the OR operator used in the If statement. The Case clause is true if at least one of the conditions is true.

1. Select Case myMonth 
2.     Case "January", "February", "March" 
3.         Debug.Print myMonth & ": 1st Qtr." 
4.     Case "April", "May", "June" 
5.         Debug.Print myMonth & ": 2nd Qtr." 
6.     Case "July", "August", "September" 
7.         Debug.Print myMonth & ": 3rd Qtr." 
8.     Case "October", "November", "December" 
9.         Debug.Print myMonth & ": 4th Qtr." 
10. End Select

（6）Here are a few guidelines to help you determine what kind of conditional statement you should use:

• If you want to supply only one condition, the simple If…Then statement is the best choice.
• If you need to decide which of two actions to perform, use the If…Then…Else statement.
• If your procedure requires two or more conditions, use the If…Then…ElseIf or Select Case statements.
• If your procedure has many conditions, use the Select Case statement. This statement is more flexible and easier to comprehend than the If…Then…ElseIf statement.