Excel file doesn't close using .Close method

Question

I've created a UDF that uses source excel file, exctact the data as the UDF result.

However, the source file doesn't close even if I use the .close method insine the 'with' - code below.

Any help with closing the source file after using it would be much appriciated, many thanks!

sfilename = "R:\values only.xlsb"

Application.DisplayAlerts = False
With GetObject(sfilename)
    range1 = .Sheets("RawData").Range("A:B")
    If IsError(Application.VLookup(lookupval, range1, 2, False)) Then
        If IsError(Application.VLookup(lookupval2, range1, 2, False)) Then
            MsgBox "Pair or date does not exist!"
        Else

            getex = 1 / Application.VLookup(lookupval2, range1, 2, False)

        End If

    Else

        getex = Application.VLookup(lookupval, range1, 2, False)

    End If
    .Close
End With
Application.DisplayAlerts = True

Answer 1

As mentioned in comment above, you could open your file in another instance (in same instance your UDF-formula call won't work)

 Set objExcel = CreateObject("Excel.Application")
     With objExcel
            .Visible = False
            .DisplayAlerts = False
            With .Workbooks.Open(sfilename)
                range1 = .Sheets(2).Range("A:B")
                If IsError(Application.VLookup(lookupval, range1, 2, False)) Then
                    If IsError(Application.VLookup(lookupval2, range1, 2, False)) Then
                        MsgBox "Pair or date does not exist!" 'do not use msgbox but give getex a string => getex = "error"
                    Else
                        getex = 1 / Application.VLookup(lookupval2, range1, 2, False)
                    End If
                 Else
                        getex = Application.VLookup(lookupval, range1, 2, False)
                End If
            .Close
        End With
        .Quit
    End With

Another possibility is to place the outcome of your UDF by a Sub in the sheet (I would do that)