Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a "switch p, case 1 2 3 ...." flow control in Excel?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a "switch p, case 1 2 3 ...." flow control in Excel?
Check out Select Case statements in VBA help.
Gord Dibben MS Excel MVP On Mon, 17 Dec 2007 12:28:02 -0800, 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a "switch p, case 1 2 3 ...." flow control in Excel?
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 -- Dana DeLouis "sweder" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a "switch p, case 1 2 3 ...." flow control in Excel?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a "switch p, case 1 2 3 ...." flow control in Excel?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut to switch from "fill down" to "copy" with mouse drag | Excel Discussion (Misc queries) | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
Why does excell switch my stock symbol "ACN" to "CAN"? | Setting up and Configuration of Excel | |||
Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | Excel Programming | |||
how do I count only lower case "x" and exclude upper case "X" | Excel Worksheet Functions |