Custom functions references to other files.

Hi all, I have created a few "Public Functions" that are stored in a master control file ( a file whose name changes with each new version). These functions need to reference named ranges in various files, and I do not wish to "hard code" these file names into the function nor do I want to have too many arguments when entering the function on the spread sheet. How can I get the function code to automatically look up the required file name irrespective to which workbook is "active" at the time of recalculation?

in the code below the file name is hardcoded in "MastFile" this works very well but I would like to remove the hardcoded statement so as to make the code more portable between different files.

Please help.

Public Function VehChoice(RouteType As String, MaxVol_1 As Double, Maxvol_2 As Double)

'Calculates the type of vehicle required based on route type and the expected Max Vol on the route

    Dim Vol  As Double
    Dim Choice As String
    Dim Trunk6m As Double
    Dim Trunk9m As Double
    Dim Trunk12m As Double
    Dim Feeder6m As Double
    Dim Feeder9m As Double
    Dim Feeder12m As Double
    Dim MastFile As String
    
'Set MastFile name
        MastFile = "Costing_model_BP_2032_V1_2.xlsm"     '    Worksheets("Parameters").Range("Mast_File").Value '  sets the name of the Master file.


'Set the bus type values from the assumptions sheet
    Trunk6m = Workbooks(MastFile).Worksheets("Assumptions & Rates").Range("Choice_6m").Value
    Trunk9m = Workbooks(MastFile).Worksheets("Assumptions & Rates").Range("Choice_9LERH").Value
    Trunk12m = Workbooks(MastFile).Worksheets("Assumptions & Rates").Range("Choice_12LERH").Value
    Feeder6m = Workbooks(MastFile).Worksheets("Assumptions & Rates").Range("ChoiceF_6m").Value
    Feeder9m = Workbooks(MastFile).Worksheets("Assumptions & Rates").Range("ChoiceF_9m").Value
    Feeder12m = Workbooks(MastFile).Worksheets("Assumptions & Rates").Range("ChoiceF_12m").Value

'Determine the maximum MaxVol value
    Vol = WorksheetFunction.Max(MaxVol_1, Maxvol_2)
    
'Determine if route is Feeder or Trunk

    Select Case RouteType
        Case Is = "BRT - full dedicated lanes":
            Select Case Vol
                Case Is <= Trunk6m: Choice = "6m"
                Case Is <= Trunk9m: Choice = "9LERH"
                Case Is <= Trunk12m: Choice = "12LERH"
                Case Is > Trunk12m: Choice = "18LERH"
            End Select
        Case Is = "Feeder routes":
            Select Case Vol
                Case Is <= Feeder6m: Choice = "6m"
                Case Is <= Feeder9m: Choice = "9LERH"
                Case Is <= Feeder12m: Choice = "12LERH"
                Case Is > Feeder12m: Choice = "12LERH"
            End Select
    End Select

VehChoice = Choice

End Function

And how would the function or for that matter the user know which is the required file? You could add another argument to pass the filename but in that case the user would have to know the correct filename of all the possible names.

I have tried to create a named range in the "Client file" that calculates the Master File's name (as can be seen in the comment section following the "MastFile" line but this only works if the client file is the active file. If the model is recalculated from any other file this does not work and returns #Value.
I do not want to create another argument for the user to enter as they can make mistakes.

the issue is that every time a new version is created the user must remember to go and change the hardcoded file name in this module and in other modules, I want to eliminate this.

one of the other functions already has 8 arguments and I want to reduce this.