title: How to improve efficiency using arrays instead of Find in VBA
body: <p>I have a function that is used to find the information in a Excel worksheet knowing that:
  - The Key can be in a variable column
  - Variable fields can be searched
  Sheets usually have less than a hundred column, but can have anything from a few hundred to 100 000 rows to search.  In our biggest files, the function I'm trying to optimize can be used about a million times. </p>
  
  <p>After reading 
  <a href="https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/" rel="nofollow">https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/</a></p>
  
  <p>... and finding our function used Find (3 times), I tried using arrays.</p>
  
  <p>This is the code I wrote </p>
  
  <pre><code>Function getInfo(Key As String, NameField As String, NameKey As String, WksName As String) As Variant
  
  On Error GoTo Error
  
  Dim iColumnKEY As Integer
  Dim iColumnFIELD As Integer
  Dim i As Integer
  Dim ListFields, ListKeys As Variant
  
  ListFields = Worksheets(WksName).Range("A1:ZZ1")
  
  
  i = LBound(ListFields, 2)
  
  'To identify which column contains the Key and which one contains the  
  'information we are searching for
  Do While iColumnKEY=0 Or iColumnFIELD=0
      If i &gt; UBound(ListFields, 2) Then
          getInfo = "//error\\"
  
      ElseIf ListFields(1, i) = NameKey Then
          iColumnKEY = i
      ElseIf ListFields(1, i) = NameField Then
          iColumnFIELD = i
      End If
  i = i + 1
  Loop
  
  Dim iROW As Integer
  
  ListKeys = Worksheets(WksName).Columns(iColumnFIELD)
  
  i = LBound(ListKeys, 1)
  Do While iROW=0
      If i &gt; UBound(ListKeys,1) Then
          getInfo = "//error\\"
  
      ElseIf ListKeys(i,1) = Key Then
          iROW = i
      End If
      i = i + 1
   Loop
  
   getInfo = Worksheets(WksName).Cells(iROW, iColumnFIELD)
  
  Exit Function
  
  Error:
      getInfo = "//error\\"
  
  End Function
  </code></pre>
  
  <p>The code works, but is very slow.  What am I doing that is slowing things down?</p>
  
  <p>It is not in the code right now, but I did try turning the screen update down, as well as automatic calculation down.  I didn't see any difference in speed, which indicates me that the basic algorithm  is the main issue.</p>
  
  <p>Also, the article was in 2011.  Are arrays still a lot faster than Match/Find?</p>
  
  <p>As a side note: eventually, I'll suggest having a macro that search for a range of Keys in a batch, instead of calling the function for every single key.  This would means the first Do... While loop would be done only once for a macro, and only the Do_While for Rows would be run for every key.  However, this is not an option in the very short term.</p>
  
  <p>Thanks.  Any help or advice would be greatly appreciated.</p>