Select Case vs If/ElseIf/EndIf 2

[steve728]

Is there any speed advantage to using Select Case
Vs If/ElseIf/EndIf?

Steve

 

[PHV]

I don't know, but surely more flexibility and readability

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

If all else fails, perform a test:

Sub WhatsFaster()
  Dim j As Long
  Dim sngStart As Single
  Dim buffer As String
  
  sngStart = Timer()
  
  For j = 0 To 10000000
    If j Mod 1000 = 10 Then
      buffer = "10"
    ElseIf j Mod 1000 = 100 Then
      buffer = "100"
    ElseIf j Mod 1000 = 1000 Then
      buffer = "1000"
    Else
      buffer = "0"
    End If
  Next j
  
  Debug.Print "If Else elapsed: " & Format(Timer() - sngStart, "##.0000")
  sngStart = Timer()
  
  For j = 0 To 10000000
    Select Case (j Mod 1000)
      Case 10
        buffer = "10"
      Case 100
        buffer = "100"
      Case 1000
        buffer = "1000"
      Case Else
        buffer = "0"
    End Select
  Next j
  
  Debug.Print "Select Case elapsed: " & Format(Timer() - sngStart, "##.0000")
End Sub

Here's the output on my laptop (Intel P4 1.7 Ghz):

[u]Immediate Window                       [/u]
If Else elapsed: 5.6982
Select Case elapsed: 4.4668

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

Of course, to be fair to the If..Else construct, I should point out a flaw in the test. Without examining the actual low-level compilation generated by the VBA compiler, It appears that the Select...Case block only has to calculate its result once per loop, whereas the If...Else block has to calculate it 3 times per loop. If we modify the test so each construct only has to perform the calculation one time per loop, the outcome is decidely different:

Sub WhatsFasterNow()
  Dim j As Long
  Dim lngResult As Long
  Dim sngStart As Single
  Dim buffer As String
  
  sngStart = Timer()
  
  For j = 0 To 10000000
    lngResult = j Mod 1000   [green]'calculate once only[/green]
    Select Case lngResult
      Case 10
        buffer = "10"
      Case 100
        buffer = "100"
      Case 0
        buffer = "1000"
      Case Else
        buffer = "*"
    End Select
  Next j
  
  Debug.Print "Select Case elapsed: " & Format(Timer() - sngStart, "##.0000")
  sngStart = Timer()
  
  For j = 0 To 10000000
    lngResult = j Mod 1000   [green]'calculate once only[/green]
    If lngResult = 10 Then
      buffer = "10"
    ElseIf lngResult = 100 Then
      buffer = "100"
    ElseIf lngResult = 0 Then
      buffer = "1000"
    Else
      buffer = "*"
    End If
  Next j
  
  Debug.Print "If Else elapsed: " & Format(Timer() - sngStart, "##.0000")

End Sub

Output:

[u]Immediate Window                   [/u]
Select Case elapsed: 4.8086
If Else elapsed: 4.6367

So I guess the answer is: It depends on how you code them! (but IF...Else was faster on my box when the test was fair)

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[snayjay]

That's was a neat way to test the question. The only thing I would mention is also that Select Case statements can't be nested, So they are a kinda limited in that option whereas If...Else...End if statements can be nested infinitely I would imagine. But I would agree with PHV about the readability being better with Select Case. Again I really loved the test code!

 

[steve728]

Select Case statements CAN be nested. I do it when necessary
all the time.

Thanks for you input.

Steve

 

[snayjay]

Wow...I have always used If...End if Statements, until a friend showed me Select Case statements. I tried to nest Select case statements once before and I couldn't get it to work. So I asked my friend to check my code and he told me it didn't work that way. I never bothered to check online. So I've never tried since, and that was over a year ago. Hehe...I just never used em when I needed to nest them. Anyway, thanks for pointing my ignorance out...it will make my coding that much easier to read in the future. Sorry for misleading, I'll do more research in the future.