Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Which way is faster? If elseif or select case

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
  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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...