Reading and writing an item of an array is always slower than accessing a simple variable. Therefore, if you need to repeatedly use the same array item in a loop, you should assign it to a temporary variable and use that variable instead. I’ve included an example of this technique that scans an Integer arrays to see if there are any duplicate values:
Function AnyDuplicates(intArray() As Integer) As Boolean ' returns True if the array holds duplicate values Dim i As Long, j As Long, Dim lastItem As Long Dim value As Integer ' evaluate UBound() just once lastItem = UBound(intArray) For i = LBound(intArray) To lastItem ' storing intArray(i) into a non-array variable ' saves an indexing operation within the inner loop value = intArray(i) For j = i + 1 To lastItem If value = intArray(j) Then AnyDuplicates = True Exit Function End If Next Next ' no duplicates were found AnyDuplicates = FalseEnd Function
The routine is based upon two nested loops and saves CPU time by caching the value of intArray(i) into a regular, non-array variable. This simple trick makes the routine up to 80 percent faster.