Is there a better way than to use multiple ElseIf?

[JeffGrant]

Hi All,

I have a vba module that looks like this without the actual test conditions & code. This is the actual module with 17 elseif's, and several sub-if's. Each of these conditions are independant and are being tested for separately.
With Sheet1
If Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Or Or Or Then
If And Then
End If
If And Then
End If
If Then
End If
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
ElseIf Then
If Then
End If
ElseIf Then
End If
End With

I have looked at the arguments for and against multiple ElseIf's and Case, and it just seems to come down to personal preference. Given the spagetti code structures above, does anybody have an option on what would be a better way to test all these scenrios to give me better performance from the PC?

chat soon.

Jeff.

 

[My Aswer Is This]

I would use case:
But to give you the code to use I would need specific details of what you're attempting to do.

 

[bebo021999]

Have no idea if I dont know exactly what kind of If, else if are using.
Normally, there are several alternative solutions for nested if:
* use a worksheet table then vlookup
* use case select
* Or store all condition-result into an array
Try to give more information for us to see what we can help.

 

[Peter_SSs]

With that many options I would also choose the Case option. However, whichever method you choose, there may be something that you can do to improve the speed.
How much improvement depends on your circumstances.

Take this code example

Sub Test1()
  Dim a As Long, i As Long
  Dim t As Single
  t = Timer
  
  Randomize
  For i = 1 To 1000000
    a = 1 + Int(1000 * Rnd())
    If a = 1 Then
    ElseIf a = 2 Then
    ElseIf a = 3 Then
    ElseIf a = 4 Then
    ElseIf a = 5 Then
    Else
      'This code section will be used most of the time
    End If
  Next i
  Debug.Print "Code 1 took " & Format(Timer - t, "0.000 secs")
End Sub

Here we are choosing a random number from 1 to 1000 and we are doing that a million times.
Clearly, the vast majority of times the random number will be greater than 5 yet for every on of the million loops the code first checks if the number is 1 & if not checks for 2 etc.
In most cases these first 5 check are wasting time.

So, if you can order your If/ElseIf statements so that the most likely ones come first you don't waste so many checks.

Sub Test2()
  Dim a As Long, i As Long
  Dim t As Single
  t = Timer
  
  Randomize
  For i = 1 To 1000000
    a = 1 + Int(1000 * Rnd())
    If a > 5 Then
      'This code section will be used most of the time
    ElseIf a = 5 Then
    ElseIf a = 4 Then
    ElseIf a = 3 Then
    ElseIf a = 2 Then
    Else
    End If
  Next i
  Debug.Print "Code 2 took " & Format(Timer - t, "0.000 secs")
End Sub