12

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?

Jon Taylor
  • 7,865
  • 5
  • 30
  • 55
user1543250
  • 173
  • 2
  • 2
  • 9
  • 2
    An interesting read http://social.msdn.microsoft.com/Forums/eu/vbgeneral/thread/14ff805c-6e8f-465f-aed2-7ff6cccf73c7 – Siddharth Rout Jul 24 '12 at 23:04
  • Note that in the specific example given, the greatest improvement might be to extract `Sheets("sheet1").Range("A1")` into a `With...End With` clause... – Mike Woodhouse Jul 25 '12 at 07:31
  • Just noting you could probably use just `Case "Option_1"` instead of `Case Is = "Option_1"` for conciseness. – Oneide Jul 25 '12 at 13:40

6 Answers6

17
  1. Case statements are supposed to minimize the number of times the processor attempts to change its command location. Doing so will cause it to waste clock cycles until the correct commands are referenced. Unless you're writing something that needs to be extremely optimized you won't notice the difference.
  2. I lean towards case statements because they are easier to read. (less to read => easier to read)
  3. If this is the exact data you are using, you can split the value on '_' and increment the last digit 'mod' the highest value possible. Combine the strings back together to get your result.
jtimperley
  • 2,494
  • 13
  • 11
  • 6
    + 1 I agree. The difference is almost negligible. Even I prefer `Select Case` because of point 2 – Siddharth Rout Jul 24 '12 at 23:02
  • 6
    running a loop 1,000,000 times that had `i Mod 3` as the selection: `if elseif` ran a whole 3 seconds faster than a `case` statement. This would not be a good place to optimize - go for readability – SeanC Jul 25 '12 at 15:07
4

For just a few items, it doesn't matter. For larger arrays, use switch. More of the technical details here.

Community
  • 1
  • 1
Bazinga
  • 994
  • 4
  • 14
  • 42
  • 2
    This ideally should be a comment :) – Siddharth Rout Jul 24 '12 at 23:00
  • 2
    @DougGlancy: Which Excel version are you using? I have Switch Function in VBA Excel 2010. Though I find Switch Function more complicated then Select Case. All these versions of Excel 2000/XP/2003/2007/2010 have it. – Siddharth Rout Jul 25 '12 at 00:08
  • 2
    @SiddharthRout I learn something new every day on SO! Today I learned to check before I assert... I hope. I thought switch was just an Access SQL thing, Office-wise. Thanks. – Doug Glancy Jul 25 '12 at 00:29
  • @DougGlancy I thought switch was only in languages like PHP, I believe I heard about it here also. – JimmyPena Jul 25 '12 at 02:03
  • 2
    swith is for replace multiple `If`statements, but only for single value answer, wheras `select case` can do code in each answer. An other option is `choose`, wich is not appropriate for this question, but still good to know of. – Patrick Lepelletier May 31 '17 at 22:56
  • As to the "technical details" link it refers to c and C#. Is 'switch' also preferable for VBA, which it appears that O.P. is asking about? – MicrosoftShouldBeKickedInNuts Aug 25 '19 at 10:12
3

As Bazinga says, for just a few items, it doesn't matter. Anyway, you should do add With...end With statements in this case:

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

This should be faster and more readable.

aprados
  • 374
  • 4
  • 16
3

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

Khalid Saifaldeen
  • 147
  • 5
0

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

Slai
  • 22,144
  • 5
  • 45
  • 53
0

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.

Robert
  • 1