VBA Brush Up 05：Decision Making with VBA

最新推荐文章于 2024-08-17 09:05:33 发布

转载最新推荐文章于 2024-08-17 09:05:33 发布 · 668 阅读

文章标签：

#vba #up #nested #basic #vb #each

VBA Brush Up 专栏收录该内容

15 篇文章

订阅专栏

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，多行的需要；单行的如果需要执行多个语句，则加冒号

Sub SimpleIfThen2()
    Dim weeks As String
    On Error GoTo VeryEnd
    weeks = InputBox("How many weeks are in a year:", "Quiz")
    If weeks <> 52 Then MsgBox "Try Again":SimpleIfThen2
    If weeks = 52 Then MsgBox "Congratulations!"
VeryEnd:
End Sub

If condition Then statement1 Else statement2

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

Private Sub cmdOK_Click()
    If txtPwd = "FOX" Then
        MsgBox "You are not authorized to run this report."
    ElseIf txtPwd = "DOG" Then
        If txtUser = "John" Then
            MsgBox "You are logged on with restricted privileges."
        ElseIf txtUser = "Mark" Then
            MsgBox "Contact the Admin now."
        ElseIf txtUser = "Anne" Then
            MsgBox "Go home."
        Else
            MsgBox "Incorrect User name."
        End If
    Else
        MsgBox "Incorrect password or user name"
    End If
    Me.txtUser.SetFocus
End Sub

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

If condition1 Then
    statements to be executed if condition1 is True
ElseIf condition2 Then
    statements to be executed if condition2 is True
ElseIf condition3 Then
    statements to be executed if condition3 is True
ElseIf conditionN Then
    statements to be executed if conditionN is True
Else
    statements to be executed if all conditions are False
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.

 
 Select Case testExpression 
    Case expressionList1 
        statements if expressionList1 matches testExpression 
    Case expressionList2 
        statements if expressionList2 matches testExpression 
    Case expressionListN 
        statements if expressionListN matches testExpression 
    Case Else 
        statements to be executed if no values match testExpression 
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.

 
 Select Case unitsSold 
    Case 1 To 100 
        Discount = 0.05 
    Case Is <= 500 
        Discount = 0.1 
    Case 501 To 1000 
        Discount = 0.15 
    Case Is >1000 
        Discount = 0.2 
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.

 
 Select Case myMonth 
    Case "January", "February", "March" 
        Debug.Print myMonth & ": 1st Qtr." 
    Case "April", "May", "June" 
        Debug.Print myMonth & ": 2nd Qtr." 
    Case "July", "August", "September" 
        Debug.Print myMonth & ": 3rd Qtr." 
    Case "October", "November", "December" 
        Debug.Print myMonth & ": 4th Qtr." 
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.