Thread: What to Avoid in VBA

Originally Posted by pierre08

Then why using Select Case in better Than If..End If???

This was covered vaguely from around post #72 ... ie efficiency versus practicality.

The point is somewhat academic unless you've got the Select statement inside a loop executing a zillion times. It will take the next programmer more time to figure out what efficient but weird code does than time saved in execution for the user.

Originally Posted by Paul

Hearing that If...ElseIf...Else is more efficient than Select Case is interesting, as every one of my programming teachers in college said the exact opposite. They're reasoning was that Select Case structures only check for the variable's value once and then perform each of the cases in order, whereas an If/ElseIf has to test it for every "case" as it were. If you have a Select Case with 20 cases and one variable test at the beginning vs If..ElseIf..Else with those same 20 tests done in order, one (ok, me) would think the Select Case is faster.

Have I been led astray?!?

Since it came up again I thought I'd re-ask (since I didn't get a response)... why would all my instructors tell me what I've stated above? Are they stupid, hence making me stupid through having listened to them?

Not sure about the instructors, probably just teaching what they've been taught.

But when I'm using F8 to step through my code, Select Case and If...ElseIf...Else...End If both do the same thing...they go through the options in order listed in the code and as soon as a TRUE evaluation occurs...it skips out thus not testing any IFs or CASEs that followed.

So I wonder where the increased efficiency for one over the other even comes from?

I always assumed the the difference was that "Case" exits when the first true statment is found
and If...ElseIf...Else... evaluates all the statments ..

No, I just ran this and it skipped to the end when it found "2" in [A1]

Sub test()

    If Range("A1") = 1 Then
        Range("B1") = 2
    ElseIf Range("A1") = 2 Then
        Range("B1") = 3                      'stopped here
    ElseIf Range("A1") = 3 Then
        Range("B1") = 4
    ElseIf Range("A1") = 4 Then
        Range("B1") = 5
    End If

End Sub


Sub test()
Select Case Range("A1").Value
    Case 1
        Range("B1") = 2
    Case 2
        Range("B1") = 3         'stopped here
    Case 3
        Range("B1") = 4
    Case 4
        Range("B1") = 5
    Case 5
        Range("B1") = 6
End Select

End Sub

I always assumed ...

Both evaluate only to the first True condition. You know that, you just didn't stop to think it through.

Hard to say without seeing the generated code, but in both statements you have to evaluate dynamic expressions. Hard to say why either one would have a significant advantage. I suspect if there's a difference it would be somewhat academic. The IF statement require it to evaluate one expression per branch, but the Select requires it to evaluate one expression on the Select line, plus one expression per branch, so it has to do a little more evaluation.

(You compile VBA so I assume it must generate code, though I have this nagging feeling it's interpreted.)

A nice simple illustration here. If you step through it you will notice that the function myF() gets called twice in the "If ... elseif" and only once in the case statement. Which must mean more work is being done in the "if ... elseif". This will be compounded by more complex conditions and increasing numbers of elseif.

But of potentially greater concern is that the value of test maybe different at each elseif. For example a time based test will have a different value for the comparison at each point. Whereas the "case" statement will only use one value for comparison.

Additionally there is always the possibility that the elseif is not testing exactly the same condition a myF1("0") and myF1("O") may easily be overlooked. Using a select case ensures that only the one value is being used as the source.

Sub main()

Dim sI As String
Dim sC As String


    Do
        Range("a1") = "B"
        If myF() = "A" Then
            sI = "if 1  "
        ElseIf myF() = "B" Then
            sI = "if 2  "
        Else
            sI = "IF is not matched    "
        End If
        
        Range("a1") = "B"
        Select Case myF()
        Case "A"
            sC = "case 1"
        Case "B"
            sC = "case 2"
        Case Else
            sC = "case not matched"
        End Select
    Loop While MsgBox(sI & sC, vbOKCancel) = vbOK

End Sub

Function myF() As String
    myF = Range("a1")
    Range("a1") = InputBox("enter value")
End Function

just in case (excuse the pun) it needs saying: it is also obvious that the conditions in the "case" statements could give rise to further dynamic conditions. These should, in my view, be avoided.

My main point was to illustrate that the myF function gets evaluated on each line of the "IF" but only once in the "case"

A slightly contrived example, IMO, since in reality you would store the value in a variable at the start then compare the variable in the If statements, just as in the Select Case, unless you actually had a need to re-evaluate it each time.
Note: in languages like C++, I believe that switch statements are more efficient due to the way the compiler optimises them.

don't eat yellow snow !

A slightly contrived example, IMO ...

And if the return value of myF() could vary within the same code block (e.g., Rnd()), it would be a very wacky piece of code.

Originally Posted by shg

And if the return value of myF() could vary within the same code block (e.g., Rnd()), it would be a very wacky piece of code.

I have seen it happen

Romperstomper : you are right I would store the value in a variable, but not everyone else does.

That's true, but it's not really a demonstration of the performance of Select Case versus If... Else.