Case Statements- General Question

[alwaysXcel]

Hello Everyone,

I was curious about your opinion on using multiple case statements in a sub, I have about 8-10 different case statements in a sub. Is using case statements the best approach? I am not aware of any alternatives besides using if's. I would appreciate if someone can shed some light on both the approach of using excessive case statements or its alternatives.

Thank you!!!

:beerchug:

[Norie]

Could you explain what you want to do?

I would use the Select Case structure rather than lots of If...End Ifs.

It really depends on what you are trying tro do.

[Killian]

Hi,
It might be worth posting the code to see how/if it can be refined.
The basic idea when making the choice is to see if you are evaluating the same expression over and over. If your dealing with the same variable or expression then best to use select... case since its evaluated once then the result is held in memory and tested against each case statement.
If... then... else/elseif is best used for test different expressions to find which scenario you're trying to deal with in the same block of code.
Technically, there's no problem with nesting them and no real limit on how many times, it just becomes incredibly difficult to keep track of whats going on when you're writing/maintaining code.
On thing I've learned over the years is, if it's getting really complicated to follow multiple and/or nested if...then...else and Select..case structures, there's probably a more logical way or dealing with the logic of the routine as a whole. Most likely you can refine complex scenarios but writing a few functions that deal with some of the decision making in a more modular way.

[Jacob Hilderbrand]

I prefer to use Select Case most of the time. If there are only a few things to check I will use an If, ElseIf, Else construct. But usually I will not do this if I would need more than two ElseIfs.

Basically it comes down to what method you prefer.

[Cyberdude]

I read somewhere written by someone who had done testing, and this guy claims that if you have more than 3 conditions to test for, then clearly the CASE logic is preferable. As you might guess, it would be silly to use CASE logic for just 1 or 2 conditions.

[Jacob Hilderbrand]

Well I prefer to use Select Case for even 1 condition if there are multiple values.

Such as:

Case var1, var2, var3, var4

Instead of:

If ... Or ... Or ... Or ...

As for speed, realistically speaking, you would never be able to tell the difference.

[Zack Barresse]

Quite true Cyberdude. It's just as Jake says. They basically work in the same way, each Case, or If, will be checked until a suitable match has been made. Once everything is complete which sates the statement, the method is abandoned (code will skip all other conditions). To test this, run this code ...


Option Explicit

Sub fooo()
Dim i As Long, dummyStr Ss String
For i = 1 to 4 Step 1
Select Case i
Case 1
dummyStr = dummyStr & dummyStr
Case 2
dummyStr = dummyStr & dummyStr
Case 3
dummyStr = dummyStr & dummyStr
Case 4
dummyStr = dummyStr & dummyStr
Case Else
dummyStr = dummyStr & dummyStr
Next i
End Sub

As it starts out it will be fairly efficient; but by 3 or 4 you start getting the picture on how it must step through each Case statement to check for the value. Depending on the situation, it could be faster to do a Match method within an array, or in the right circumstance use the Find method.

Also remember that if you are testing for 2 conditions only and you only have one executable line of code for each condition, you can take care of it on one line. Example ...



Option Explicit

Sub fooo_test_toooo()
If ActiveCell.Address <> "$A$1" Then MsgBox "Here is a one-liner.", vbInformation
End Sub

[Cyberdude]

Sorry, Gang, I didn't express myself well. When I said 3 conditions, I should have said 3 or more values.

[Zack Barresse]

Select case would be the way to go then, definitely.

[TonyJollans]

Well I prefer to use Select Case for even 1 condition if there are multiple values.

Such as:

Case var1, var2, var3, var4

Instead of:

If ... Or ... Or ... Or ...

As for speed, realistically speaking, you would never be able to tell the difference.

As this is a general question let me throw my twopenn'orth into the pot.

There is an efficiency issue but it goes further than that. Let's say in Jake's example, var2 is true:



In the Case construct, var1 and var2 are checked and when the condition is met flow jumps to the action for that case
In the If .. Or .. Or scenario, all of var1, var2, var3 and var4 will be tested before anything else happens.
If these are just variables it probably doesn't matter much but if they are, say, function calls then you might be running a lot of unnecessary code or ,even worse, code which could error out when it needn't ever have been run.

[alwaysXcel]

Thank you guys!!! I think now I won't be as fearful of resource constraints when using case statements!!