is there a "switch p, case 1 2 3 ...." flow control in Excel?

[sweder]

I want to execute one out of 5 formulas depending on whether a switch
variable takes the value 1, 2, 3, 4 or 5. Most programming languages have a
"switch p, case 1 F1, 2 F2, etcetera construct. Does that also exist in
Excel? I am using office 2003.

 

[Gord Dibben]

Check out Select Case statements in VBA help.


Gord Dibben MS Excel MVP

 

[Dana DeLouis]

Most programming languages have a

"switch p, case 1 F1, 2 F2, etcetera construct. Does that also exist in
Excel?

Hi. Excel vba's Switch command is "a little different" then everyone else.
"Usually" better to use something like Select Case

Sub Demo()
Dim x, s
x = 2
s = Switch(x = 1, "One", x = 2, "Two")
End Sub

 

[ilia]

I want to execute one out of 5 formulas depending on whether a switch
variable takes the value 1, 2, 3, 4 or 5. Most programming languages have a
"switch p, case 1 F1, 2 F2, etcetera construct. Does that also exist in
Excel? I am using office 2003.

There is Select Case statement, kind of like so:

Public Sub switchDemo()
Dim p As Integer

p = Int(Rnd() * 10)
Debug.Print "P = " & p

Select Case p
Case 0:
Debug.Print "Failure!"
Case 1, 2, 3:
Debug.Print "Between 1 and 3"
Case 4, 5, 6:
Debug.Print "Between 4 and 6"
Case Else:
Debug.Print "Too high"
End Select
End Sub

However, I am under the impression that this is less efficient than a
multiple If-Then-Else structure, so unless you have many cases and
resulting readability issues, I would probably go with this:

Public Sub multiIf()
Dim p As Integer

p = Int(Rnd() * 10)
Debug.Print "P = " & p

If (p) Then
If (p < 4) Then
Debug.Print "Between 1 and 3"
ElseIf (p < 7) Then
Debug.Print "Between 4 and 6"
Else
Debug.Print "Too high"
End If
Else
Debug.Print "Failure!"
End If
End Sub

 

[ilia]

Just for sake of curiosity, try this code to see the performance
difference. The second If procedure - altIf() - does not use a nested
If statement.

Private Declare Function GetTickCount Lib "kernel32" () As Long

Public Sub testSpeed()
Dim i As Long
Dim t1 As Long
Const runs As Long = 10000000#

t1 = GetTickCount

For i = 1 To runs
switchDemo
Next i

Debug.Print "Select Case run time = " & GetTickCount - t1

t1 = GetTickCount

For i = 1 To runs
multiIf
Next i

Debug.Print "If-Then-Else run time = " & GetTickCount - t1

t1 = GetTickCount

For i = 1 To runs
multiIf
Next i

Debug.Print "Alt If-Then-Else run time = " & GetTickCount - t1
End Sub

Private Sub switchDemo()
Dim p As Integer
Dim s As String

p = Int(Rnd() * 10)

Select Case p
Case 0:
s = "Failure!"
Case 1, 2, 3:
s = "Between 1 and 3"
Case 4, 5, 6:
s = "Between 4 and 6"
Case Else:
s = "Too high"
End Select
End Sub

Private Sub multiIf()
Dim p As Integer
Dim s As String

p = Int(Rnd() * 10)

If (p) Then
If (p < 4) Then
s = "Between 1 and 3"
ElseIf (p < 7) Then
s = "Between 4 and 6"
Else
s = "Too high"
End If
Else
s = "Failure!"
End If
End Sub

Private Sub altIf()
Dim p As Integer
Dim s As String

p = Int(Rnd() * 10)

If (p > 6) Then
s = "Too high"
ElseIf (p > 3) Then
s = "Between 4 and 6"
ElseIf (p) Then
s = "Between 1 and 3"
Else
s = "Failure!"
End If
End Sub