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
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’.
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& Else Me.lblZone.Visible = True Me.lblZone.Caption = "Zone 3" Me.lblZone.ForeColor = &HFF& For x = 1 To 4 Me.lblZone.Visible = False Me.Repaint Sleep (500) Me.lblZone.Visible = True Me.Repaint Sleep (500) Next x End If End If End Sub