User-Defined Function in a Function

[mllex]

I have created a function to calculate a fee based upon 2 criteria (market
value and discount). Each fee schedule has a numerical assignment: 2, 9,
22, etc and it's own tiered fee structure.

If I have an extract with information as follows:

Fee Schedule Market Value Discount My Formula
9 2,000,000 .15 =fee9(mv,discount)
2 8,000,000 .00 =fee2(mv,discount)
22 1,675,000 .10 =fee22(mv,discount)


I want to be able to have a function that will read the fee schedule number
(like a vlookup) and then choose the appropriate function to calculate the
fee. Each fee number would have it's own tiered fee structure. I would
rather just have one function, fee(mv,discount), and have it accomplish any
fee calculation, regardless of the fee schedule number.

Can you put a vlookup or a loop in a function or does anyone have any
thoughts on how to accomplish this? Any assistance would be greatly
appreciated.


Public Function Fee9(MarketValue, Discount)

'Calculates fee based on schedule 9 and principal market value and applies
any discount

Const Tier1 = 0.0125
Const Tier2 = 0.0065
Const Tier3 = 0.005
Const Tier4 = 0.004

Select Case MarketValue
Case 0 To 1000000: Fee9 = MarketValue * Tier1
Case 1000000.01 To 2000000: Fee9 = ((MarketValue - 1000000) * Tier2) +
(1000000 * Tier1)
Case 2000000.01 To 5000000: Fee9 = ((MarketValue - 2000000) * Tier3) +
(1000000 * Tier2) + (1000000 * Tier1)
Case Is > 5000000.01: Fee9 = ((MarketValue - 5000000) * Tier4) +
(3000000 * Tier3) + (1000000 * Tier2) + (1000000 * Tier1)
End Select

Fee9 = Fee9 * (1 - Discount)

End Function

Also, is there a convenient way for users to see the user-defined functions without having to seek out "user-defined" in the function list?