what is wrong with my custom function?

[greegan]

Function ShowPayPeriod(PayPeriod)

Tier1 = 240
Tier2 = 52
Tier3 = 26
Tier4 = 24
Tier5 = 12
Tier6 = 10
Tier7 = 13
Tier8 = 22
Tier9 = 53
Tier10 = 27
Select Case PayPeriod
Case "Daily"
ShowPayPeriod = 1 * Tier1
Case "Weekly"
ShowPayPeriod = 1 * Tier2
Case "Biweekly"
ShowPayPeriod = 1 * Tier3
Case "Semi - monthly"
ShowPayPeriod = 1 * Tier4
Case "Monthly"
ShowPayPeriod = 1 * Tier5
Case "Other 10"
ShowPayPeriod = 1 * Tier6
Case "Other 13"
ShowPayPeriod = 1 * Tier7
Case "Other 22"
ShowPayPeriod = 1 * Tier8
Case "Weekly 53"
ShowPayPeriod = 1 * Tier9
Case "Biweekly 27"
ShowPayPeriod = 1 * Tier10
End Select
 
End Function

I'm getting "variable not defined" highlighting Tier1
or I'm getting the macro prompt screen as if i selected Alt+F8
One of these errors appear when I try to run this... I enter =ShowPayPeriod(B3) in any cell and the error comes up.

What am I missing?

Thanks

-- g

 

[Brian Walters]

It looks like you need to declare your variables at the start of the function, i.e. Dim Tier1 as Integer, Tier2 as Integer, ...

 

[Scott Huish]

Because you probably have Option Explicit turned on and you haven't dimmed your variables.

But I have some questions:

1) Why not use an array?
2) Why are you multiplying 1 times anything? That won't change the result.

This also could be done with a VLOOKUP formula instead of a UDF.

 

[jimrward]

it is always good programming practice to define variables, constants, functions and data types in general

 

[greegan]

it is always good programming practice to define variables, constants, functions and data types in general

I should have known. I'm new to custom functions and it didn't occur to me this is what I needed to do. Works like a charm. Thank you (all)

-- g

 

[Scott Huish]

Function ShowPayPeriod(PayPeriod) As Long
Dim Tier(1 To 10) As Long
Tier(1) = 240
Tier(2) = 52
Tier(3) = 26
Tier(4) = 24
Tier(5) = 12
Tier(6) = 10
Tier(7) = 13
Tier(8) = 22
Tier(9) = 53
Tier(10) = 27
Select Case PayPeriod
    Case "Daily"
        ShowPayPeriod = Tier(1)
    Case "Weekly"
        ShowPayPeriod = Tier(2)
    Case "Biweekly"
        ShowPayPeriod = Tier(3)
    Case "Semi - monthly"
        ShowPayPeriod = Tier(4)
    Case "Monthly"
        ShowPayPeriod = Tier(5)
    Case "Other 10"
        ShowPayPeriod = Tier(6)
    Case "Other 13"
        ShowPayPeriod = Tier(7)
    Case "Other 22"
        ShowPayPeriod = Tier(8)
    Case "Weekly 53"
        ShowPayPeriod = Tier(9)
    Case "Biweekly 27"
        ShowPayPeriod = Tier(10)
End Select
End Function

 

[greegan]

This is great. how do i get it to work within a code rather than in a cell?

-- g

 

[Scott Huish]

Function ShowPayPeriod(PayPeriod) As Long
Dim Tier(1 To 10) As Long
Tier(1) = 240
Tier(2) = 52
Tier(3) = 26
Tier(4) = 24
Tier(5) = 12
Tier(6) = 10
Tier(7) = 13
Tier(8) = 22
Tier(9) = 53
Tier(10) = 27
Select Case PayPeriod
    Case "Daily"
        ShowPayPeriod = Tier(1)
    Case "Weekly"
        ShowPayPeriod = Tier(2)
    Case "Biweekly"
        ShowPayPeriod = Tier(3)
    Case "Semi - monthly"
        ShowPayPeriod = Tier(4)
    Case "Monthly"
        ShowPayPeriod = Tier(5)
    Case "Other 10"
        ShowPayPeriod = Tier(6)
    Case "Other 13"
        ShowPayPeriod = Tier(7)
    Case "Other 22"
        ShowPayPeriod = Tier(8)
    Case "Weekly 53"
        ShowPayPeriod = Tier(9)
    Case "Biweekly 27"
        ShowPayPeriod = Tier(10)
End Select
End Function

Sub Test()
MsgBox ShowPayPeriod("Daily")
End Sub

 

[greegan]

This is great. how do i get it to work within a code rather than in a cell?

-- g

Thank you for all of your help.
I should ask that last question a little differently.

instead of
entering =showpayperiod(B8) in a cell (where B8 is a validation list)
I want to have the code tell a specific cell to show the numeric value of the description in the drop list (manually selected)
ie Biweekly is selected, the code would enter 26 in whatever cell I specified.
In my code P= range("B12") and is where I want 26 to show up.


Thank you again

-- g

 

[greegan]

I could be doing this al wrong, or the above might work in the end but I have a question (hopefully you can answer my previous one...)

The point of the above function is to simulate a vlookup. I don't want the range to appear on my worksheet. With that said, is the above custom function the most efficient way to duplicate the vlookup worksheet function or can it be done with an array (vlookup, not the custom function)?

ie
I would like the following in an array
52,weekly
26,biweekly
24,semi-monthly

and can I have a vlookup formula work with this array?

I'm not sure if there's a better way to ask.

-- g