VBA/Excel/Access/Word/Language Basics/Select
Содержание
- 1 A nested Select Case example
- 2 Choosing Actions with Select Case
- 3 Convert number to String by using Select statement
- 4 Select Case Else
- 5 Select Case Statements with comparison operator
- 6 Specify a range in select case statement
- 7 Specifying a Range of Values in a Case Clause by using the To keyword between the values of expressions
- 8 Specifying Multiple Expressions in a Case Clause: specify multiple conditions within a single Case clause by separating each condition with a comma
- 9 Three-level nested Select Case statement
- 10 Use Integer as the switch value for Select Case Structure
- 11 Use select case statement
- 12 Use string text as the select case statement control value
- 13 Using Is with the Case Clause
A nested Select Case example
<source lang="vb">
Sub CheckCell()
Dim Msg As String Select Case IsEmpty(ActiveCell) Case True Msg = "is blank." Case Else Select Case ActiveCell.hasFormula Case True Msg = "has a formula" Case False Select Case IsNumeric(ActiveCell) Case True Msg = "has a number" Case Else Msg = "has text" End Select End Select End Select MsgBox "Cell " & ActiveCell.Address & " " & Msg
End Sub
</source>
Choosing Actions with Select Case
<source lang="vb">
Sub TestChooseActivity()
Debug.Print ChooseActivity(25) Debug.Print ChooseActivity(34) Debug.Print ChooseActivity(35) Debug.Print ChooseActivity(65) Debug.Print ChooseActivity(66) Debug.Print ChooseActivity(75) Debug.Print ChooseActivity(95)
End Sub Function ChooseActivity(Temperature As Integer) As String
Dim sActivity As String Select Case Temperature Case Is < 32 sActivity = "S" Case 33, 35 To 45 sActivity = "H" Case 34 sActivity = "S" Case 46 To 50, 65, 70 To 72 sActivity = "C" Case 75 To 80 sActivity = "G" Case 80 To 100 sActivity = "W" Case Else sActivity = "T" End Select ChooseActivity = sActivity
End Function
</source>
Convert number to String by using Select statement
<source lang="vb">
Function NumberToString(lngNumber As Long) As String
Dim strNumber As String Dim intLoop As Integer Dim strRV As String Dim strTemp As String strNumber = lngNumber For intLoop = 1 To Len(strNumber) Select Case Mid$(strNumber, intLoop, 1) Case "0" strTemp = "Zero" Case "1" strTemp = "One" Case "2" strTemp = "Two" Case "3" strTemp = "Three" Case "4" strTemp = "Four" Case "5" strTemp = "Five" Case "6" strTemp = "Six" Case "7" StrTemp = "Seven" Case "8" strTemp = "Eight" Case "9" strTemp = "Nine" End Select strRV = strRV & strTemp & " " Next NumberToString = strRV
End Function
</source>
Select Case Else
<source lang="vb">
Public Function AssignGrade(studScore As Single)
Select Case studScore Case Is >= 70 AssignGrade = "C" Case Is >= 80 AssignGrade = "B" Case Is >= 90 AssignGrade = "A" Case Else AssignGrade = "F" End Select
End Function
</source>
Select Case Statements with comparison operator
<source lang="vb"> Sub Check_Typing_Speed() Dim varTypingSpeed As Variant Dim strMsg As String varTypingSpeed = 35 Select Case varTypingSpeed Case "" End Case Is < 0, 0, 1 To 50 strMsg = "1" Case 50 To 60 strMsg = "2." Case 60 To 75 strMsg = "3." Case 75 To 99 strMsg = "4." Case 100 To 200 strMsg = "5." Case Is > 200 strMsg = "6." End Select Debug.Print strMsg End Sub </source>
Specify a range in select case statement
<source lang="vb">
Function Fare(Age As Integer) As Variant
Select Case Age Case 0 To 3, Is > 65 Fare = 0 Case 4 To 15 Fare = 10 Case 16 To 65 Fare = 20 Case Else Fare = CVErr(xlErrNA) End Select
End Function Sub test()
MsgBox CStr(Fare(32))
End Sub
</source>
Specifying a Range of Values in a Case Clause by using the To keyword between the values of expressions
<source lang="vb">
Sub caseTo()
Dim unitsSold As Integer unitsSold = 44 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 MsgBox Discount
End Sub
</source>
Specifying Multiple Expressions in a Case Clause: specify multiple conditions within a single Case clause by separating each condition with a comma
<source lang="vb">
Sub caseClause()
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
End Sub
</source>
Three-level nested Select Case statement
<source lang="vb">
Sub CheckCell()
Dim Msg As String Select Case IsEmpty(ActiveCell) Case True Msg = "is blank." Case Else Select Case ActiveCell.hasFormula Case True Msg = "has a formula" Case False Select Case IsNumeric(ActiveCell) Case True Msg = "has a number" Case Else Msg = "has text" End Select End Select End Select MsgBox "Cell " & ActiveCell.Address & " " & Msg
End Sub
</source>
Use Integer as the switch value for Select Case Structure
<source lang="vb">
Sub ifTest5()
Dim intNum As Integer intNum = 2 Select Case intNum Case 1 Debug.Print "This is the lowest number" Case 15 Debug.Print "This is the highest number" Case Else Debug.Print "The number is between 1 and 15" End Select
End Sub
</source>
Use select case statement
<source lang="vb">
Function Price(Product As String) As Variant
Select Case Product Case "Apples" Price = 12.5 Case "Oranges" Price = 15 Case "Pears" Price = 18 Case "Mangoes" Price = 25 Case Else Price = CVErr(xlErrNA) End Select
End Function
Sub test()
MsgBox CStr(Price("Oranges"))
End Sub
</source>
Use string text as the select case statement control value
<source lang="vb">
Function Price(Product As String) As Variant
Select Case Product Case "Apples": Price = 12.5 Case "Oranges": Price = 15 Case "Pears": Price = 18 Case "Mangoes": Price = 25 Case Else: Price = CVErr(xlErrNA) End Select
End Function
Sub test()
MsgBox CStr(Price("Oranges"))
End Sub
</source>
Using Is with the Case Clause
<source lang="vb">
Sub selectCase()
Dim myNumber As Integer myNumber = 9 Select Case myNumber Case Is <= 10 MsgBox "The number is less than or equal to 10." Case 11 MsgBox "You entered 11." Case Is >= 100 MsgBox "The number is greater than or equal to 100." Case Else MsgBox "The number is between 12 and 99." End Select
End Sub
</source>