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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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

Regards,
Amit

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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.
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

"Amit Shanker" wrote in message
...
As a rule of thumb, usually Select Case if there are more than 3 cases.

Regards,
Amit

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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


"Syed Zeeshan Haider"
wrote in message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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?"


"Chip Pearson" wrote in message
...
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


"Syed Zeeshan Haider"
wrote in message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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"
wrote in message
...
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?"


"Chip Pearson" wrote in message
...
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


"Syed Zeeshan Haider"
wrote in message
...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Syed, you may want to take a look at the following urls for more on this
topic :

http://www.ozgrid.com/News/ExcelSele...Dfunctions.htm
http://www.ozgrid.com/News/SelectCase2.htm

Amit


"Chip Pearson" wrote 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"
wrote in message
...
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?"


"Chip Pearson" wrote in message
...
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


"Syed Zeeshan Haider"
wrote in message
...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Hi Syed,

But "xlbo" is saying something different. What do you think?

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?

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

There is no discussion of performance in those articles.


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

"Amit Shanker" wrote in message
...
Syed, you may want to take a look at the following urls for
more on this
topic :

http://www.ozgrid.com/News/ExcelSele...Dfunctions.htm
http://www.ozgrid.com/News/SelectCase2.htm

Amit


"Chip Pearson" wrote 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"
wrote in message
...
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?"


"Chip Pearson" wrote in message
...
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


"Syed Zeeshan Haider"

wrote in message
...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

I didn't see anything in those primer articles that had to do with the speed
of Select case vice using If then Else.

--
Regards,
Tom Ogilvy

"Amit Shanker" wrote in message
...
Syed, you may want to take a look at the following urls for more on this
topic :

http://www.ozgrid.com/News/ExcelSele...Dfunctions.htm
http://www.ozgrid.com/News/SelectCase2.htm

Amit


"Chip Pearson" wrote 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"
wrote in message
...
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?"


"Chip Pearson" wrote in message
...
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


"Syed Zeeshan Haider"
wrote in message
...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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

--
Regards,
Tom Ogilvy

"Dana DeLouis" wrote in message
...
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.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

but the limitation could be easily overcome with
res = Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2)

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

"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 (75)
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

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Dana DeLouis" wrote in message
...
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.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

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

"Dana DeLouis" wrote in message
...
but the limitation could be easily overcome with
res = Sin(x) + Log(x / 2) + Sqr(25.6) + Tan(3.2)

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

"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
(75)
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

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Dana DeLouis" wrote in message
...
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.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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