Menu
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?
474
case should be faster, because it's a lookup table (as most often implemented by the compiler). However, if you're worried about which of these runs faster, and it's really the bottleneck in your program, you have a phenomenally-well-behaved project.
Learn MySQL from scratch for Data Science and Analytics The MySQL CASE statement is faster in comparison to PHP if statement.
If both statements are applicable, then SELECT CASE is more clear because it applies in only a subset of the cases for which IF/ELSEIF applies.
Case statement is useful to execute a single case statement from the group of multiple case statements based on the value of a defined expression. By using Select... Case statement in Visual Basic, we can replace the functionality of if…else if statement to provide better readability for the code.
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.
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
