Case Select vs. Switch

[Aussiebear]

A recent comment about Case Select caused me to look a little deeper in to pool of knowledge.... and its far to profound for me.

When would you use Switch rather than Case Select given the following examples

Sub select_case_example()
'Greater 1000 case will not trigger since cases are done in order.
'Must change order if you want to check both conditions
'Include else statement at bottom to capture other conditions
Dim our_input As Integer
our_input = InputBox("Enter an integer")
Select Case our_input
Case Is < 500
    MsgBox ("Your input is less than 500")
Case Is > 500
    MsgBox ("Your input is greater than 500")
Case Is > 1000
    MsgBox ("Your input is greater than 1000")
End Select
End Sub

and

Sub using_switch_function_result()
'Greater 1000 case will not trigger since function resolves in order.
'Must change order if you want to check both conditions.
Dim our_input As Integer
Dim our_output As String
our_input = InputBox("Enter an integer")
our_output = Switch(our_input < 500, "Your input is less than 500", our_input > 500, "Your input is greater than 500", our_input > 1000, "Your input is greater than 1000")
MsgBox (our_output)
End Sub

[Paul_Hossler]

Interesting -- I never knew about 'Switch' -- I'll put that on the list of the other things I never knew about

It is probably a matter of personal preference. I find Select Case easier to follow, maybe since I'm used to it

[Aussiebear]

Its not like you see Switch used every day.

[Logit]

Clear as mud ? : https://wellsr.com/vba/2018/excel/vb...a-select-case/

[Bob Phillips]

Have to admit that Switch is not something I use very often, whereas Select Case is something that I use all the time.

As the article that Logit references, Switch is a function that returns a result, a bit like INDEX/MATCH in Excel proper, and is very limited in its actions. Select Case can set a value, or even many values, and can have many statements within each Case.

Comparing the two

    res = Switch(Player = "Phelps", "Swmming", Player = "Nadal", "Tennis", Player = "Messi", "Football")

and

 
    Select Case Player
    
        Case "Phelps":      res = "Swimming"
        
        Case "Nadal":       res = "Tennis"
        
        Case "Messi":       res = "Football"
    End Select

which is better? I would say that is a matter of preference in the example above, although I admit I do not like that with switch the condition is repeated (I accept it means you can have different conditions, but you get the point).

As said, Select Case can have many statements within each Case, even another Case (I guess you could have a Switch within a Switch also)

 
    Select Case Player
    
        Case "Phelps":     
 
              Select Case Forename

                  Case "Ian: res = "Swimming"
                  Case Brian: res = "Beer drinking"
              End Select
        
        Case "Nadal":       res = "Tennis"
        
        Case "Messi":       res = "Football"
    End Select

One thing I like about Select Case is that you can have the condition in the Case statement rather than the Select statement, by testing for True

    Select Case True
    
        Case Player = "Phelps":     res = "Swimming"
        
        Case Sport = "Tennis":      Player = "Phelps"
        
        Case Player = "Messi":      res = "Football"
    End Select

Personally, I like Select Case, but Switch may be better in some circumstances and I have used it, it is certainly a useful VBA function .

[Paul_Hossler]

Clear as mud ? : https://wellsr.com/vba/2018/excel/vb...a-select-case/

Actually, I thought it was interesting. I hadn't grasped the part about Switch being a Function. Almost 99% of my stuff works better using Select Case since it's usually multiple statements being selected (and habit )

The first, and most notable, difference between Select Case and Switch is that the former is a statement while the latter is a function. In esssence, in programming, a statement declares something, while a function calculates something.

ConclusionIf your code’s logic is relatively simple, you can probably get away with using either Switch or Select Case. If you just need a single output value, though, it could be better to use a VBA Switch function instead.
Conversely, if you have complex logic, you will not be able to implement it via the Switch function. Instead, Select Case (or switch case) may work out quite nicely for you. Keep in mind, complex means as little as just two lines of code, since the VBA Switch function can only handle one calculation per case.
The exception, of course, is if you want to step into the world of functional programming. Functional programming allows you to write functions as the output values in Switch, like we did on this tutorial. Just understand this could quickly make your programs cumbersome and more difficult to debug later.

[Aussiebear]

Thank you Bob & Paul. Your explanations are far better than the link that Logit suggested.

[Bob Phillips]

Jesus, I just looked at the Consulting page on that link that Logit gave. The guy charges $150 ph for personalised help, $180 ph for urgent VBA help, and $1380 per month as a retainer, 10 hours per month.

I am selling myself too cheaply, way too cheaply!!

[snb]

It's also dependent on how you structure your data:

Sub M_snb()
   c00 = "Nadal"
   sn = Split("Phelps_Swimming Nadal_Tennis Messi_Football")
   c01 = "Phelps Swimming Nadal Tennis Messi Football"

   MsgBox Split(Filter(sn, c00)(0), "_")(1)
   MsgBox Split(Split(c01, c00)(1))(1)
   
   With CreateObject("scripting.dictionary")
        sp = Split(c01)
        For j = 0 To UBound(sp) - 1 Step 2
           .Item(sp(j)) = sp(j + 1)
        Next
   
        MsgBox .Item(c00)
   End With
End Sub

[Bob Phillips]

It's also dependent on how you structure your data:

What is?

[Aussiebear]

Sorry snb, but what has your solution got to do with Select Case vs Switch?