vba 哪种方式更快？如果 elseif 或选择大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11640413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Which way is faster? If elseif or select case
提问by user1543250
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?
1）我想知道哪种方式会更快。如果可能的话，可以解释技术细节吗？
2) Regardless the efficiency, which method should I use in this case, for the better coding.
2）不管效率如何，在这种情况下我应该使用哪种方法，以获得更好的编码。
3) Any other "simple" way to circle value from array?
3）从数组中圈出值的任何其他“简单”方法？
回答by jtimperley
- 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.
- I lean towards case statements because they are easier to read. (less to read => easier to read)
- 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.
- Case 语句应该尽量减少处理器尝试更改其命令位置的次数。这样做会导致它浪费时钟周期，直到引用正确的命令。除非您正在编写需要极其优化的内容，否则您不会注意到差异。
- 我倾向于案例陈述，因为它们更容易阅读。（更少阅读 => 更容易阅读）
- 如果这是您正在使用的确切数据，您可以拆分“_”上的值并将最后一位数字“mod”增加到可能的最高值。将字符串重新组合在一起以获得结果。
回答by Bazinga
For just a few items, it doesn't matter. For larger arrays, use switch. More of the technical details here.
对于少数项目，这无关紧要。对于较大的阵列，请使用 switch。 更多技术细节在这里。
回答by aprados
As Bazinga says, for just a few items, it doesn't matter. Anyway, you should do add With...end With statements in this case:
正如 Bazinga 所说，对于少数项目，这并不重要。无论如何，在这种情况下，您应该添加 With...end With 语句：
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.
这应该更快，更易读。
回答by Khalid Saifaldeen
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.
我运行了多个场景，使用简单的“for 循环”和“mod”函数将“IF”语句与“Case”语句进行比较。场景在检查的记录数量和条件数量（ifElse 数量/案例数量）方面有所不同。下表显示了场景和每个场景的结果。请注意，表中提到的速度是 100 次运行的平均值。
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).
我们可以看到“IF”语句和“Case”语句在性能上几乎相似。“IF”语句略胜于“Case”语句（快约 5%）。
Excel Version: Office 365 version 1908 build 11929.20300
Excel 版本：Office 365 版本 1908 内部版本 11929.20300
Processor Used: Intel i9-9900K
使用的处理器：Intel i9-9900K
回答by Slai
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_#.
有点晚了，但在特定示例中，最快的方法应该是将选项存储为数字，并在需要时增加它。可以将单元格的自定义数字格式更改"option_"0;;;为数字以显示为 option_#。
In almost all cases I expect the Select Caseto be a just a tiny bit slower and to be compiled to something very similar to If Else statements.
在几乎所有情况下，我希望它Select Case会稍微慢一点，并被编译为与 If Else 语句非常相似的东西。
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 Caseexample gets that value only once in the beginning and then compares that value. This should cause the Select Caseexample to be few times faster when the cell value is not "option_1".
两个示例中的情况并非如此，因为它们做的事情略有不同。在第一个示例中，每个 If 语句将查找 Sheet "sheet1" 并获取 Range "A1" 的值，但该Select Case示例仅在开头获取该值一次，然后比较该值。Select Case当单元格值不是“option_1”时，这应该会导致示例快几倍。
回答by Robert
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)
而不是必须输入所有的可能性，为什么不这样做呢。（假设下一项总是1加上最后一项）
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.
这更可靠、更高效，并且将无限运行，而无需无限行代码来完成。
