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>
