vba case code example
Example 1: excel vba case statement
v = "z"
Select Case v
Case 1
'code runs here if v = 1
Case 2
'code runs here if v = 2
Case 3 To 5
'code runs here if v = 3 or 4 or 5
Case Is < 10
'code runs here if v = 6 or 7 or 8 or 9
Case 10, 15, 20
'code runs here if v = 10 or 15 or 20
Case #12/25/2020#
'code runs here if v = #12/25/2020#
Case "abc"
'code runs here if v = "abc"
Case Is <= "m"
'code runs here if v <= "m"
Case True
'code runs here if v = True
Case Else
'Code runs here if v did not match any of the above cases.
'In this example, since v = "z", the 'Case Else' would match
'and only the code here would execute.
End Select
'Notes: The Select Case construct must be terminated by 'End Select'.
' Only one case (at most) will have its code run.
' Each case is evaluated in order, from the top down, until match found.
' No more cases are evaluated once a case matches the Select Expression.
' An execution speed optimization is to place more likely cases on top.
' The special 'Case Else' is optional.
' If no case matches the Select Expression, execution exits the construct.
'A very useful variation is to set the Select Expression to True (or False):
Select Case True
Case a <= b And b <= c: Debug.Print "b is between a and c"
Case a = b: Debug.Print "a = b"
Case e > f: Debug.Print "e > f"
Case InStr(x, "z"): Debug.Print "'z' is in x"
Case IsEmpty(m): Debug.Print "m is Empty"
End Select
'In this second example, only the firt True case will print.
'
'
'
Example 2: excel vba imitating the in operator from other languages
'VBA does not have an IN operator, but we can do this:
Function IsIn(a, b) As Boolean
IsIn = InStrB("," & b & ",", "," & a & ",")
End Function
'--------------------------------------------------------------------
MsgBox IsIn(2, "1,2,3") '<-- displays True
MsgBox IsIn("d", "a,b,c") '<-- displays False
Example 3: vba select case
Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5, inclusive.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case 9 To 10 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select