Concise use of Select Case

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Concise use of Select Case

Post by ChrisGreaves »

I read this idea this morning in Dick Kusleika's Daily Dose of Excel

I did not grow up with SELECT statements, and they still seem foreign and cumbersome to me.
Nonetheless, that just means I need more practice.
Dick's example (the link above) is more practical than mine; I wanted to test the idea out with a simple string example, reproduced below.

Code: Select all

Function lngLivedThere(strIn As String) As Long
    Select Case True
        Case strIn = "WA", strIn = "SA", strIn = "NSW"
            lngLivedThere = 1
        Case Else
            lngLivedThere = 0
    End Select
'Sub TESTlngConciseCase()
'    MsgBox lngLivedThere("WA")
'    MsgBox lngLivedThere("SA")
'    MsgBox lngLivedThere("NSW")
'    MsgBox lngLivedThere("QLD")
'End Sub
End Function
As usual, drag the commented macro outside the function, de-comment it and enjoy.
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Concise use of Select Case

Post by HansV »

Your example is better with the "standard" use of Select Case":

Code: Select all

  Select Case strIn
    Case "WA", "SA", "NSW"
      lngLivedThere = 1
    Case Else
      lngLivedThere = 0
  End Select
Select Case True should be used if you have disparate conditions, especially if they would otherwise lead to nested or repeated If statements, as in the example on Daily Dose of Excel.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Concise use of Select Case

Post by ChrisGreaves »

HansV wrote:Your example is better with the "standard" use of Select Case":
Thanks Hans. It's a fair verification of my statement of unease with Select!
I did try at first with just the commas, but that caused a syntax error. I was muddling around and forgot to tidy up after myself.
In the past I'd have coded it

Code: Select all

Select Case strIn
    Case "WA"
    Case "SA"
    Case "NSW"
    Case Else
End Select
An expensive day out: Wallet and Grimace