Select Case Function

[WindsorKnot]

Hi,

I'm trying to learn how to write functions with Select Case and am having trouble with my code below. Basically, it returns 0 when any of the three plans below are selected. Basically there if for example Health - Option 1 is selected I want the function to return "High". I know how to do this using the if-then arguements but want to learn how to do this using select case. Any input would greatly appreciated.

Function plan(tier)
    Dim High As String
    Dim Mid As String
    Dim Low As String
    Dim Opt1 As String
    Dim Opt2 As String
    Dim Opt3 As String
    Opt1 = "Health - Option 1"
    Opt2 = "HDHP Lower Deductible"
    Opt3 = "HDHP High Deductible"
    Select Case tier
        Case Opt1
        plan = High
        Case Opt2
        plan = Mid
        Case Opt3
        plan = Low
        Case Else
         MsgBox "Select a valid plan"
         Exit Function
    End Select
End Function

 

[Weaver]

Try plan = "High"

currently it's looking for the value of a variable called high

 

[jasonb75]

Hi again

Try changing

plan = High

to

plan = "High"

Repeat for the other case lines

I haven't looked at your code properly but that's the problem that stands out.

Jason

 

[onlyadrafter]

Hello,

if you are trying to get the plan variable to be either HIgh , mID or Low, you need to put them in quotes i.e. plan="High" etc.

 

[VoG]

Try

Function plan(tier)
Dim Opt1 As String
Dim Opt2 As String
Dim Opt3 As String
Opt1 = "Health - Option 1"
Opt2 = "HDHP Lower Deductible"
Opt3 = "HDHP High Deductible"
Select Case tier
    Case Opt1
        plan = "High"
    Case Opt2
        plan = "Mid"
    Case Opt3
        plan = "Low"
    Case Else
        MsgBox "Select a valid plan"
    End Select
End Function

 

[WindsorKnot]

Thank you, this did the trick