I've written this function to calculate premium amount based on common premium table
With this code I have to keep same Premium Table in each workbook . I want to keep a common workbook for premium table. How can I replace VLookup function so that I can Vlookup the table stored in a closed workbook. Suggestion for improvement also invited.
Rich (BB code):
Function Company_Cont(Basic As Currency, Class As Integer, DOB As Date, Date_effective As Date) As Currency
On Error GoTo FuncFail:
Dim Entitled As Double
Dim Age_Group As Integer
Dim Discount As Double
Dim Base_Premium As Double
Dim Loaded_Premium As Double
Dim r As Range
Set r = Range("premium_Table")
Entitled = Entitlement(Basic)
Age_Group = App_Group(Date_effective, DOB)
Select Case Class
Case 1
Discount = 2 / 3
Case Else
Discount = 3 / 4
End Select
Base_Premium = Application.WorksheetFunction.VLookup(Entitled, r, Age_Group) * 0.355
Loaded_Premium = Base_Premium + (Base_Premium * 1.2)
Company_Cont = 1 / 12 * (Discount * Base_Premium + (Loaded_Premium - Base_Premium))
Exit Function
FuncFail:
Company_Cont = 0
End Function
With this code I have to keep same Premium Table in each workbook . I want to keep a common workbook for premium table. How can I replace VLookup function so that I can Vlookup the table stored in a closed workbook. Suggestion for improvement also invited.