Naming a range from another workbook

[grexcelman]

Having some difficulty naming the ranges in my code. Ultimately trying to run a Vlookup but I've tried breaking up the pieces of the range location to help simplify and find the error in my range value.

Function ann_calc(acct As String, func As Long, term As Variant)
Dim driveloc As String
driveloc = "Z:\Management\Expense Project\2015_figures\"
filename1 = driveloc & "Jan Only File.xlsm"
filename2 = driveloc & "Feb Only File.xlsm"
tab1 = "Expenses by Function'!"

Dim datarangegb1 As Range

Set datarangegb1 = Range(filename1 & tab1 & "$B$9:$K$44")

total1 = Application.WorksheetFunction.VLookup(acct, datarangegb1, func, False)

ann_calc.value = total1

Can't seem to isolate where I'm going wrong here.

 

[RoryA]

You can't do that in VBA - the source workbooks have to be open for you to assign their ranges to a Range object.