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