Which one is faster?

  • Thread starter Thread starter Syed Zeeshan Haider
  • Start date Start date
S

Syed Zeeshan Haider

Hello Everybody,
I have Excel 97 Pro on Win98SE.

For a certain situation, I have two options:

1. If Else
2. Select Case

I just want to ask which one of these is faster?

Thank you,
 
As a rule of thumb, usually Select Case if there are more than 3 cases.

Regards,
Amit
 
There is going to be a readability problem as I already have too many Select
Case in one procedure. If it were in VB (not VBA) I would have preferred
"Select Case".

If both "Select Case" and "If Else" are same in speed then I will write my
code regarding its readability.
 
AFAIK, Select Case is slightly faster although the difference could be negligable unless you have many, many logic trees
 
Syed,

Some quick and dirty testing shows that If/Then/Else is about
twice as fast as Select Case.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
But "xlbo" is saying something different. What do you think?
--
Syed Zeeshan Haider.
http://szh.20m.com/


-----------------------------------
Allah says to Mankind:
"Then which of the favours of your Lord will ye deny?"


in message
 
I'd be interested why he came to his conclusion. I do agree,
however, with his statement that the difference is negligible
unless you have many, many, logic tree.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Syed Zeeshan Haider" <[email protected]>
wrote in message
 
Hi Syed,
But "xlbo" is saying something different. What do you think?
Click to expand...

It all depends on what you include in the comparisons. Take the seemingly equivalent code:

Select Case a
Case b
Case c
Case d
End Select

If a = b Then
ElseIf a = c Then
ElseIf a = d Then
End If

If a is a constant or a simple variable, a simple timing test shows that the If..ElseIf is faster. However, if a is an expression
to evaluate, that evaluation is done once in the Select Case and multiple times in the If..ElseIf, which will probably negate the
difference in speed between the two constructs.

Personally, I find Select case easier to read and prefer it, but as ever with performance tuning, the only solution is to test it
using your own code and data.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Hi Syed,
But "xlbo" is saying something different. What do you think?
Click to expand...

It all depends on what you include in the comparisons. Take the seemingly equivalent code:

Select Case a
Case b
Case c
Case d
End Select

If a = b Then
ElseIf a = c Then
ElseIf a = d Then
End If

If a is a constant or a simple variable, a simple timing test shows that the If..ElseIf is faster. However, if a is an expression
to evaluate, that evaluation is done once in the Select Case and multiple times in the If..ElseIf, which will probably negate the
difference in speed between the two constructs.

Personally, I find Select case easier to read and prefer it, but as ever with performance tuning, the only solution is to test it
using your own code and data.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
I didn't see anything in those primer articles that had to do with the speed
of Select case vice using If then Else.
 
I don't consider myself in anywhere near the same league as Messrs Pearson & Bullen - I use their sites all the time so I would happily bow to their knowledge on this. Select case vs IF is not something I have tested much but I was informed (a long time ago) that select case was quicker - I can't remember who but it was probably someone on the PeachEase Excel-L list - maybe Bob Umlas or Dave Hawley.......
 
I think it depends on what you are doing. I think the main advantage of
Select Case is that a calculation is only performed once. If you have a If
Else, then the same calculation has to be performed more than once.
Here is an example of using the same "complex" formula using an If Else

If Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2) < 5 Then
'do somthing
ElseIf Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2) < 10 Then
' something else
ElseIf Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2) < 20 Then
' etc
End If


On the other hand, by using Select Case, the complex calculation is only
perfomed once.

Select Case Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2)
Case Is < 5: 'something
Case Is < 10 'etc
Case Is < 20 'etc
End Select

End Sub


This is why the syntax is pretty strict on Select Case. It does not want to
introduce further calculations. For example, you can not do the following
Case is >=5 and <=10. (or something similar). It would require further
calculations. The proper syntax would be 5 to 10. This allows the function
to work without having to do any more math.
 
I wished to point out additional material for the original poster's review,
not to say that it proves that select case is faster. Apologies if I was
misunderstood !

Amit


xlbo said:
I don't consider myself in anywhere near the same league as Messrs Pearson
Click to expand...
& Bullen - I use their sites all the time so I would happily bow to their
knowledge on this. Select case vs IF is not something I have tested much but
I was informed (a long time ago) that select case was quicker - I can't
remember who but it was probably someone on the PeachEase Excel-L list -
maybe Bob Umlas or Dave Hawley.......
 
but the limitation could be easily overcome with

res = Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2)
if res < 5 then

Elseif res < 10 then


And select case does allow more complex and further calculations in the case
expression.

Sub TestCase()
x = 7
Select Case Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2)
Case Is >= 5 And Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2) <= 10
MsgBox "Between 5 and 10"
Case Else
MsgBox "Less than 5 or greater than 10"
End Select

End Sub
 
but the limitation could be easily overcome with
res = Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2)
Click to expand...

Yes. That's true. Select Case is just another way to do it. One advantage
to Case is that it does not require a variable, and the result of the
calculation is internal.

I may be wrong, but are you sure about
Case Is >= 5 And Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2) <= 10
Click to expand...

"Is >= 5 " gets the left hand side from the internal results. There is
nothing further calculated. This part returns True, and the remaining
longer calculation is not performed. I could have sworn this generated an
error long ago, but it doesn't in Excel XP.

Looked at another way. Here we use "Or" instead of "And." Here, we do not
get the correct msgbox because Is <3 is False, and the remaining part (7>5)
is not used (It's a calculation).
Hope I said this correctly. :>)

Sub TestCase()
Dim x
x = 7
Select Case x * 1
Case Is < 3 Or 7 * 1 > 5 ' Or use x > 5
MsgBox "Between 5 and 10"
Case Else
MsgBox "This is not correct"
End Select
End Sub
 
I believe you are correct, but the point is still that Select Case does
allow calculation in the conditions which was my point (even if my example
was incorrect).

Select Case True
Case long complex expression that evaluates to true or false

case another long complex expression that evaluates to true or false


Regards,
Tom Ogilvy
 
Back
Top