VBA Select Case Function

I believe the easiest way to understand a VBA Select Case Function 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.function
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 Function

VBA Select Case Function 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’.            

VBA Select Case 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&
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
If you were able to utilize what you learned here or improve the code,
please leave a comment Here

 

If you enjoyed this post or found it helpful andl would like to say hello
and buy me a cup of coffee Click here

Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here:
Contact Me

Comments are closed.