Select Case Is statement in excel VBA

  • The CASE statement is a built-in function in Excel that is categorized as a Logical Function.
  • Executes one of several groups of statements, depending on the value of an expression.
  • You may also use ‘Is’ keyword in the Select Case statement. You may use ‘Is’ keyword with the comparison operator like =, >=, <= etc.

Syntax:-

Select Case test_expression

   Case Is condition_1
      statement_1
   Case Is condition_2
      statement_2
   Case Is condition_n
      statement_n
   Case Else
      statement_else
End Select

For Example

An Example to illustrate which grade students have got according to marks

Sub Select_Case_Is()
    Dim Score  As Integer
    Score = InputBox(\"Enter Student Score\")
    Select Case Score
        Case Is >= 85
MsgBox \"A Grade\"
        Case Is >= 69
MsgBox \" B Grade\"
        Case Is >= 55
MsgBox \"C Grade\"
        Case Is >= 45
MsgBox \"D  Grade\"
        Case Else
        MsgBox \"Pass Grade\"

    End Select
   
End Sub

An Example to illustrate how many days are in the given months

Sub testmonth()

Dim days As Integer
days = InputBox(\"Enter number like 28,29,30,31 to get respective month\")

Select Case days

Case Is = 28, 29
MsgBox \"28 or 29 days falls on February month only\"
Case Is = 30
MsgBox \"30 days falls on April, June, September, November\"
Case Is = 31
MsgBox \"January, March, May, July, August, October, December\"

End Select

End Sub