Which way is faster? If elseif or select case

user_1543250 0
Viewed 39962

For the following code,

If Sheets("sheet1").Range("A1").Value = "option_1" Then
    Sheets("sheet1").Range("A1").Value = "option_2"
ElseIf Sheets("sheet1").Range("A1").Value = "option_2" Then
    Sheets("sheet1").Range("A1").Value = "option_3"
ElseIf Sheets("sheet1").Range("A1").Value = "option_3" Then
    Sheets("sheet1").Range("A1").Value = "option_4"
...
End IF

and

Select Case Sheets("sheet1").Range("A1").Value
    Case Is = "option_1"
        Sheets("sheet1").Range("A1").Value = "option_2"
    Case Is = "option_2"
        Sheets("sheet1").Range("A1").Value = "option_3"
    Case Is = "option_3"
        Sheets("sheet1").Range("A1").Value = "option_4"
    ...
End Select

Questions:

1) I am wondering which way would be faster. And if possible, tech detail could be explained?

2) Regardless the efficiency, which method should I use in this case, for the better coding.

3) Any other "simple" way to circle value from array?

vba excel
6 Answers

I ran multiple scenarios comparing the "IF" statement with the "Case" statement using a simple "for loop" and "mod" function. The scenarios vary in the number of records checked and the number of conditions (# of ifElse/# of cases). The table below shows the scenarios and the results for each scenario. Note that the speed mentioned in the table is the average of 100 runs.

We can see that the "IF" statement and "Case" statement are almost similar in performance. The "IF" statement slightly beats the "Case" statement (~5% faster).

enter image description here

Excel Version: Office 365 version 1908 build 11929.20300

Processor Used: Intel i9-9900K

user_102668000

A bit too late, but in the specific example the fastest should be to store the option as a number and just increment it when needed. The Custom Number Format of the cell can be changed to "option_"0;;; for the number to show as option_#.

In almost all cases I expect the Select Case to be a just a tiny bit slower and to be compiled to something very similar to If Else statements.

That is not the case in the two examples because they do slightly different things. In the first example, each of the If statements will look for Sheet "sheet1" and get the value of Range "A1", but the Select Case example gets that value only once in the beginning and then compares that value. This should cause the Select Case example to be few times faster when the cell value is not "option_1".

user_13831680

Rather than having to type in all the possibilities why not do it like this. (Assuming that the next item is always 1 plus the last item)

Public Sub Test()
    With ThisWorkbook.Worksheets("Sheet1").Range("A1")
        .Value = "option_" & Val(Mid(.Value, InStrRev(.Value, "_") + 1)) + 1
    End With
End Sub

This is more reliable, efficient and will go to infinity, without the infinite lines of code to do it.

user_91910850
Related