IF ELSE vs Select Case

Question

I wish to know how this would impact my coding when handling large volumes of data, and how I would construct my logic arguments

Two questions:

1) What are the main differences between IF-ELSE and Select CASE? Does select case operate by evaluating all cases at the same time?

If I have a situation where, by nature of its construction, need to fulfill two or more cases at the same time, for e.g.

int = 5
Select case int
Case >0
Case 5
Case <0

Where I would need to "trigger" both Cases "1" and "2" for action, do I use CASE over IF ELSE?

2) Now for the other case, if I have a variable that will trigger more one case but I would like to restrict to only one case by priority, say I would only like Case "1" for action and exclude the rest, am I right to say that IF-ELSE would triumph in this regard?

EDIT - I realized that my question was phrased poorly. I did some edits to the code

Answer 1

Some clarification on Select Case before actually answering your question:

• as you suggested, you can combine several values in one Case switch in VBA, e.g.: Case Is>40, 20, 10, 2 To 3, for more information see documentation on MSDN
• It'll execute at most one Case, it'll exit after finding a match (e.g. in the example execute Case >0 then exit without even evaluating Case 5)
• It'll only evaluate your variable / expression once and use this same value for all comparisons in contrary to nested if which evaluates multiple time

• if you want to execute "both" for a value you can nest if within case:

  Case Is > 0
      <Common code>
      If i = 5 Then
          <code only for  case 5>
      End If
  

Advantage of Select Case

• It's more efficient when your decision is about multiple values of a single variant / expression
• it also can be very useful when your expression is e.g. result of a time consuming function, in this case you really can save time compared to if

Advantage of If

• Basically it's the good option in all the cases which are not described for Select Case e.g.:
  • you have only two options to choose from (it's the same time with both if and select, but if is easier to read)
  • you have multiple expressions to consider during decision and you can't easily convert them to a single value
• Similarly to Select Case you can also use elseif to prepare multiple comparisons, it'll also stops at first fullfilled criteria, but here your expression will be evaluated every time until finding the match and of course you can use more complex criteria.