VBA Select Case Statement

I believe the easiest way to understand a VBA Select Case Statement is through an example. In the Code below the VBA Select Case is evaluating the integer i.  It reads the first line and evaluates that i is not between 1 to 5.  It then moves down to the next line and evaluates that i is equal to 6, 7 or 8 so it executes the Debug.Print “Between 6 and 8, inclusive” (see the immediate window results below).  The case else acts like a catch all for anything that didn’t evaluate to true in the preceding cases.   

Sub CaseTest()
'Select Case tutorial ...

Dim i As Integer
i = 8

Select Case i
     Case 1 To 5
          Debug.Print "Between 1 and 5, inclusive"
     Case 6, 7, 8
          'evaluates to True.
           Debug.Print "Between 6 and 8, inclusive"
     Case 9 To 10
          Debug.Print "Equal to 9 or 10"
     Case Else
          Debug.Print "Not between 1 and 10, inclusive"
End Select

End Sub

select case example #1 results

VBA Select Case Statement a Practical Example
Anthony’s Plumbing started as a 1 truck shop 25 years ago.  His dedication to customer service, honesty and good work did its magic and Anthony expanded over the years. He now employs 5 Master Plumbers and 3 Plumbing Apprentices and has 5 trucks.  The reputation for good honest work, however, had some unexpected problems.  Even though both his web site and his yellow pages ad indicate what towns he covers- out of towners continue to call.   Anthony has asked you to create a VBA custom function that will allow his dispatcher to input the customer’s zip code and immediately identify them as ‘outside normal service area’.            

Custom Function

This is the Appointment screen that Anthony’s Plumbing uses to schedule home appointments.  We use a VBA custom function “iZone” to set the zone color to:
Zone 1 Green – Normal Service Area
Zone 2 Brown – Travel Surcharge Area
Zone 3 Red Flashing – Not  in Service Area

When the dispatcher enters the customer’s zip code, the TxtZip_Change event is fired and the zip code is evaluated by the custom function, and the color of the Zone label is adjusted accordingly.  The flashing red zone 3 is added to be sure the dispatcher is alerted the customer is out of Anthony’s service area.  


Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function iZone(sZipcode As String) As Integer
' the customer provides their zip code Anyone in zone 1
' gets a normal service of $65.  Those in zone 2 are charged
' $90 for the additional travel time.  Anyone else, those
' in zone 3, are not offered service.

Select Case sZipcode
     Case 19060, 19061, 19014, 19810, 19317, 19809
          iZone = 1
     Case 19382, 19348, 19707, 19807, 19803
          iZone = 2
     Case Else
          iZone = 3
End Select

End Function

Private Sub txtZip_Change()

' when the dispatcher enters the zip code the izone
' custom function is called and assigned to the variable i
' based on the value of i the label lblZone color is updated
' if the zone is 3 the red label is flashed a couple of times

Dim i As Integer, x As Integer
If Len(Me.txtZip.Text) = 5 Then
i = iZone(Me.txtZip.Text)

If i = 1 Then
     Me.lblZone.Visible = True
     Me.lblZone.Caption = "Zone 1"
     Me.lblZone.ForeColor = &H8000&
ElseIf i = 2 Then
     Me.lblZone.Visible = True
     Me.lblZone.Caption = "Zone 2"
     Me.lblZone.ForeColor = &H40C0&
    Me.lblZone.Visible = True
    Me.lblZone.Caption = "Zone 3"
    Me.lblZone.ForeColor = &HFF&

    For x = 1 To 4
       Me.lblZone.Visible = False
       Sleep (500)
      Me.lblZone.Visible = True
      Sleep (500)
 Next x
End If
End If

End Sub

Comments are closed.