• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

VBA Excel Question: If then Else VERSUS select case

Are the "If then else" and "Select Case" interchangable?  It seems that they can both be used in the same applications.  Or are one of them more appropriate/efficient/etc.  at some tasks?

0
brothertruffle880
Asked:
brothertruffle880
  • 3
3 Solutions
 
GrahamSkanRetiredCommented:
In a simple either/or situation  "If Then Else" is probably bes, but if can have multiple outcomes, then Select case is probably better.

It is especially powerful because it can accept multiple values in a list and effectively 'OR' them

Select case MyInteger
    Case 1 to 5, 8 to 9
         ...
    Case 12 to 17
          ...
    Case 29, 31, 33
        ...
    case else
        ...
End select
         
0
 
GrahamSkanRetiredCommented:
You can create a structure similar to Select case with If and ElseIf. This is good for testing different items

If a=1 then
   ...
Elseif b=2 then
   ...
Elseif c = "Some text" then
  ...
End if

 
0
 
GrahamSkanRetiredCommented:
Corrected first sentence of my penultimate comment (37269054)

In a simple either/or situation  "If Then Else" is probably best, but if you need multiple outcomes, then Select Case is probably better.
0
 
NorieVBA ExpertCommented:
They both have advantages - Select Case is easier to read and manage.

If (ElseIf) End If can work with multiple values and use logical expressions/operators, but can be difficult to follow.
0
 
Martin LissOlder than dirtCommented:
Select Case can be a lot easier to write. For example

If x = 1 or x = 5 or x = 12 or x > 20 Then…
    'do something

Open in new window

compared to
Select Case x
    Case 1, 5, 12, > 20
        'do something
    

Open in new window



-------------------------------------------------------------------------------------------------------------------------------
My Articles:
Using the VB6 DebuggerAutomatic Insertion of Procedure Names
A Textbox ActiveX Control That Limits Input to NumbersSpell Check a Textbox
Improved Formatting TagsConditional Compilation

Marty - MVP 2009, 2010, 2011
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now