Vlookup closed workbook

[mukeshnic]

I've written this function to calculate premium amount based on common premium table

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.

 

[ravishankar]

HI
Assuming your common book is premium.xls and table is in sheet1, try these modifications

Set r = workbooks("Premium.xls").Worksheets("Sheet1").Range("premium_Table")
VLookup(Entitled, r, Age_Group,false) * 0.355

Ravi