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


On Dec 19, 3:05 pm, ilia wrote:
On Dec 17, 3:28 pm, sweder wrote:

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