Huge difference in VLOOKUP speed between formula and VBA code

[Aristide]

I'm currently trying out something with VLOOKUP. The situation:

1. Workbook to search for information is closed
   
2. Workbook to search for information has the extension .xlsm, not .xls
3. Workbook to search for is quite huge (sheet that contains the information has approx. 200 columns and approx. 2000 rows) but for testing purposes I'm only using a small range of that (see VLOOKUP formula below)
4. Active workbook has a cell with the following formula:
   

   =VLOOKUP(L11;'E:\AppData\Excel\[InputWorkbook.xlsm]Input_Sheet'!$C$7:$S$30;17;FALSE)

5. Whenever I change the information in cell L11, the data in the cell that contains the formula changes instantaneously (that means: fast)
6. Next, I'm trying to achieve the same through a VBA macro using the following code:
   

   Option Explicit
   
   Public Sub lookuptest()
       Dim sFilename As String
       Dim RangeOtherWorkbook As Range
       
       sFilename = "E:\AppData\Excel\InputWorkbook.xlsm"
       
       With GetObject(sFilename)
           Set RangeOtherWorkbook = .Sheets("Input_Sheet").Range("$C$7:$S$30")
           Range("L18").Value = Application.WorksheetFunction.VLookup( _
               Range("L11").Value, _
               RangeOtherWorkbook, _
               17, _
               False _
           )
           .Close
       End With
   End Sub

   
   The macro works, but it takes approx. 20 times more to execute.

Any idea why the formula version is so much more quick compared to the macro code? Looks a bit weird to me, since in both cases I don't have the workbook to retrieve the information from, open. I agree that the macro is effectively opening the workbook in the background (the user doesn't see the workbook open) but how does the formula version manage to be so much more quick?

Or is there a much more efficient (and quick) way to do VLOOKUP's in a macro on a closed workbook? I read on the net that with the old file extension .xls it was not possible to access a closed workbook with VLOOKUP but with .xlsx (or .xlsm) file extensions - which I'm using - that should be possible.

Puzzled...

Best,
--Aristide