How do I refer to a named range  within a User-Defined Function?

I am developing a user-defined function (UDF) that needs to switch between six possible named ranges:

GROUP A
"Bids_A" (on worksheet "Bids-A")
"Bids_A_GID" (on worksheet "Bids-A")
"Bids_A_Officials" (on worksheet "Bids-A")

GROUP B
"Bids_B" (on worksheet "Bids-B")
"Bids_B_GID" (on worksheet "Bids-B")
"Bids_B_Officials" (on worksheet "Bids-B")

I'm able to make it work perfectly as long as I bring the named ranges into the UDF through six respective arguments:

Private Function Assignment_Bids(bidchoice As Range, gid As Range, officials As Range, _
                bids_a As Range, bids_a_gid As Range, bids_a_officials As Range, _
                bids_b As Range, bids_b_gid As Range, bids_b_officials As Range) As Variant
    With Application
        Select Case bidchoice
            Case "Bids-A"
                Assignment_Bids = .Index(bids_a, .Match(gid, bids_a_gid, 0), .Match(officials, bids_a_officials, 0))
            Case "Bids-B"
                Assignment_Bids = .Index(bids_b, .Match(gid, bids_b_gid, 0), .Match(officials, bids_b_officials, 0))
        End Select
    End With
End Function

But this appears to be very cumbersome way to handle this. I would like to refer to the six named ranges above within the body of the UDF (and reduce the number of arguments from 9 to 3):

Private Function Assignment_Bids(bidchoice As Range, gid As Range, officials As Range) As Variant
    Dim bids As Range
    Dim bids_gid As Range
    Dim bids_officials As Range
    Select Case bidchoice
        Case "Bids-A"
            bids = Sheets("Bids-A").Range("Bids_A")
            bids_gid = Sheets("Bids-A").Range("Bids_A_GID")
            bids_officials = Sheets("Bids-A").Range("Bids_A_Officials")
        Case "Bids-B"
            bids = Sheets("Bids-B").Range("Bids_B")
            bids_gid = Sheets("Bids-B").Range("Bids_B_GID")
            bids_officials = Sheets("Bids-B").Range("Bids_B_Officials")
    End Select
    With Application
        Assignment_Bids = .Index(bids, .Match(gid, bids_gid, 0), .Match(officials, bids_officials, 0))
    End With
End Function

But I keep getting an error which I assume has to do with the way I'm trying to assign the various name ranges to the 3 noted variables (bids, bids_gid & bids_officials).

Any suggestions?

Thanks,
MarkStart Free Trial