Select Case

[Spencer.Sadkin]

Hi,
I am having some trouble with Select Case. I have a bunch of ifelse
statements that would be run faster as a select case, but i cant get
the syntax. I want something like:

Function range(incw) as string

Select Case incw
Case is 0
range="None"
Case is >0 And <=5 ' This is the part im having
trouble with
range="Moderate"
Case is >5
range="Extreme"
End Select
End Function

Thanks

 

[JP]

Try this:

Function range(incw) As String


Select Case incw
Case 0
range = "None"
Case incw > 0 And incw <= 5
range = "Moderate"
Case Is > 5
range = "Extreme"
End Select
End Function


HTH,
JP

 

[Per Jessen]

Hi,
I am having some trouble with Select Case.  I have a bunch of ifelse
statements that would be run faster as a select case, but i cant get
the syntax.  I want something like:

Function range(incw) as string

 Select Case incw
      Case is 0
                range="None"
      Case is >0 And <=5           ' This is the part imhaving
trouble with
                range="Moderate"
      Case is >5
                range="Extreme"
 End Select
 End Function

Thanks

Hi


Function range(incw As Long) As String

Select Case incw
Case Is = 0
range = "None"
Case Is > 5
range = "Extreme"
Case Else
range = "Moderate"
End Select
End Function

BTW: I wouldn't use range as function name as it also refers to an
object in VBA

Regards,

Per

 

[JP]

Tell me about it, I pasted the function into my VBE and it
(temporarily) broke all my macros! ;-)


--JP

 

[Don Guillett]

Select Case incw
Case is >5:s="Extreme"
Case is >0:s="M"
case else:x="N"
end select
mr=x

range="Extreme

Case is 0
range="None"
Case is >0 And <=5 ' This is the part im having
trouble with
range="Moderate"
Case is >5
range="Extreme"
End Select

 

[Spencer.Sadkin]

Select Case incw
Case is >5:s="Extreme"
Case is >0:s="M"
case else:x="N"
end select
mr=x

ha ha sorry about the range object name, thats not actually the name
of my function nor is the code what my function does, its just a
simplified version. I will Try JP's suggestion again but i think i
tried it earlier and it did not work, didnt really understand Don's
coding, is that VBA?

 

[JP]

Yes, he just grouped the "Case" statements on one line. You can do
that by using ":"


HTH,
JP

 

[Don Guillett]

Correction and clarifications

Sub docase()
If Not IsNumeric(ActiveCell) Then Exit Sub
Select Case ActiveCell
Case Is > 5: s = "Extreme"
Case Is > 0: s = "M"
Case Else: s = "N"
End Select
mr = s
MsgBox mr
End Sub

Function mc(c)'c in the cell such as a1
If IsNumeric(c) Then
Select Case c
Case Is > 5: s = "Extreme"
Case Is > 0: s = "M"
Case Else: s = "N"
End Select
mc = s
End If
End Function