title: VBA Index function size limit
body: <p>In cells A1:A66000 I have the numbers 1, 2, ... 66000.</p>
  
  <pre><code>Sub addData()
      Application.ScreenUpdating = False
      Cells(1, 1) = 1
      Cells(2, 1).Formula = "=A1+1"
      Range(Range("A66000"), Range("A66000").End(xlUp)).Select
      Selection.FillDown
      Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>The following code loads the data into an array and finds the index of the number 2.  It returns the correct result, 2.</p>
  
  <pre><code>Sub test()
      Dim arr As Variant
      arr = ArrayFromRange(Range("A1:A65536"))
      MsgBox Application.WorksheetFunction.Match(2, Application.Index(arr, 0, 1), 0)
  End Sub
  </code></pre>
  
  <p>However, changing the array size causes a Type Mismatch error due to the Index function.</p>
  
  <pre><code>Sub test()
      Dim arr As Variant
      arr = ArrayFromRange(Range("A1:A65537"))
      MsgBox Application.WorksheetFunction.Match(2, Application.Index(arr, 0, 1), 0)
  End Sub
  </code></pre>
  
  <p>How can I get around this?  I'm using Excel 2007.</p>
  
  <p>EDIT: I forgot to include this handy function that I'm calling</p>
  
  <pre><code>Function ArrayFromRange(rg As Range) As Variant()
  '==============================================================================================
  'Returns an array from a given range
  '                                                                                   BG Feb 2013
  '==============================================================================================
  
      If (rg.Cells.Count = 1) Then
          Dim arr(1 To 1, 1 To 1) As Variant
          arr(1, 1) = rg.Value
          ArrayFromRange = arr
      Else
          ArrayFromRange = rg ' Arr is now an allocated array
      End If
  End Function
  </code></pre>