title: Fastest way to detect duplicate values in array? (VBA)
body: <p>To detect duplicate values in a VBA array, I am currently using a function which checks whether, for each value in an array, the subsequent values in the array are different:</p>
  
  <pre><code>Public Function ContainsDuplicateKeys() As Boolean
  
      Dim inputKeyArray As Variant
      inputKeyArray = MyWorksheet.Range("MyTable[InputKey]")
  
      Dim i As Long
      Dim j As Long
  
      For i = LBound(inputKeyArray) To UBound(inputKeyArray)
          For j = i + 1 To UBound(inputKeyArray)
              If inputKeyArray(i, 1) = inputKeyArray(j, 1) Then
                  ContainsDuplicateKeys = True
                  Exit Function
              End If
          Next
      Next
  
      ContainsDuplicateKeys = False
  
  End Function
  </code></pre>
  
  <p>To check whether an array of n values are unique, this method involves doing 1 + 2 + ... + (n-1) comparisons. So checking the uniqueness of 10,000 inputs requires up to 49,995,000 comparisons, which takes about 13 seconds.</p>
  
  <p>On the contrary, the time taken for Excels 'Remove Duplicates' functionality to complete for the same number of inputs is near instantaneous. This implies that there must be a far more efficient way of achieving what I am attempting to do. Can anyone suggest what this might be?</p>