title: Excel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
body: <p>I want to write a user defined function that wraps VLOOKUP. All it requires is a reference to the column that data should be imported from, and it will execute a VLOOKUP assuming that the IDs are in column A and there are fewer than 3000 rows to search.</p>
  
  <pre><code>Function AutoVlookup( importFrom As Range) As Variant
      Dim arg1, arg2, arg3, arg4 As Variant
      Dim arg1Str, arg2Str As String
  
      arg1Str = "$A" &amp; Application.Caller.row 'get ID
      arg1 = Application.Caller.Parent.Range(arg1Str)
      arg2Str = "$A$1:$" &amp; Split(cells(1, importFrom.column).Address, "$")(1) &amp; "$3000"
      arg2 = importFrom.Parent.Range(arg2Str) 'get range to search in (in other workbook)
      arg3 = importFrom.column 'get column to return
      arg4 = False 'exact match
  
      AutoVlookup = Application.WorksheetFunction.VLookup(arg1, arg2, arg3, arg4)   
  End Function
  </code></pre>
  
  <p>I am running into two problems. </p>
  
  <p>Firstly, the execution time is terrible. It takes several minutes to run this formula 1000 times, whereas the same VLOOKUP not wrapped in a UDF is very fast. </p>
  
  <p>Secondly, when I first fill a column with <code>=AutoVLookup(&lt;column in other workbook&gt;)</code> every row will incorrectly show the same result until something triggers them to recalculate.</p>
  
  <p>What am I doing wrong?</p>
  
  <hr>
  
  <p>edit, answer:</p>
  
  <p>Here is the code I made using advice from Santosh and Charles:</p>
  
  <pre><code>Function EasyLookup(importFrom As Range) As Variant
      Application.Volatile False 'does not recalculate whenever cells on sheet change
  
      Dim Id As String
      Dim match As Integer
      Dim importColumnAddress As String
      Dim initialCalculationSetting As XlCalculation
      Dim initialScreenUpdateMode As Boolean
      Dim initialEnableEventsMode As Boolean
  
      'saving the settings, to be reverted later
      initialScreenUpdateMode = Application.ScreenUpdating
      initialCalculationSetting = Application.Calculation
      initialEnableEventsMode = Application.EnableEvents
      'changes screen update and calculation settings for performance
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Application.EnableEvents = False
  
      'find ID on formula's sheet
      Id = Application.caller.Parent.Cells(Application.caller.row, 1).value
      'find row with ID on column A of data source sheet
      match = Application.WorksheetFunction.match(Id, importFrom.Parent.Range("$A$1:$A$4000"), 0) 'assumes no more than 4000 rows.
  
      'retrieve value from importFrom's column, on the row where ID was found
      importColumnAddress = Split(Cells(1, importFrom.column).Address, "$")(1)
      importColumnAddress = importColumnAddress &amp; ":" &amp; importColumnAddress
      EasyLookup = Application.WorksheetFunction.Index(importFrom.Parent.Range(importColumnAddress), match)
  
      'revert performance tweaks
      Application.ScreenUpdating = initialScreenUpdateMode
      Application.Calculation = initialCalculationSetting
      Application.EnableEvents = initialEnableEventsMode
  End Function
  </code></pre>
  
  <p>It is much faster because it does not read in as much data, as it uses INDEX/MATCH rather than VLOOKUP. It also does not recalculate every time a cell in the sheet changes.</p>