VBA/Excel/Access/Word/Language Basics/Select

A nested Select Case example

 
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

Choosing Actions with Select Case

 
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

Convert number to String by using Select statement

 
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

Select Case Else

 
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

Select Case Statements with comparison operator

 
 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

Specify a range in select case statement

 
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

Specifying a Range of Values in a Case Clause by using the To keyword between the values of expressions

 
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

Specifying Multiple Expressions in a Case Clause: specify multiple conditions within a single Case clause by separating each condition with a comma

 
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

Three-level nested Select Case statement

 
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

Use Integer as the switch value for Select Case Structure

 
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

Use select case statement

 
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

Use string text as the select case statement control value

 
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

Using Is with the Case Clause

 
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