[Excel VBA] Techniques for Faster Array Looping and Comparison

In Excel VBA optimization, the technique of reading cell range data into an “array” before processing is crucial. Furthermore, how you loop through that array and how you clear it significantly affects performance and code accuracy.

In this article, I will compare two basic looping methods and three clearing methods for handling arrays, explaining their pros, cons, and recommended usage.

Looping to Read Arrays: 2 Methods

There are two main ways to loop through the elements of an array you have created.

Method 1: For…Next Loop Using Index

This is the most basic method, accessing elements directly using the array’s index number (subscript).

Sub LoopWithForNext()
    Dim weekdays(1 To 7) As String
    Dim i As Long
    
    ' Set values in the array
    weekdays(1) = "Mon": weekdays(2) = "Tue": weekdays(3) = "Wed"
    weekdays(4) = "Thu": weekdays(5) = "Fri": weekdays(6) = "Sat": weekdays(7) = "Sun"

    ' Loop from LBound (Lower Bound) to UBound (Upper Bound)
    For i = LBound(weekdays) To UBound(weekdays)
        Debug.Print weekdays(i)
    Next i
End Sub

By using the LBound and UBound functions, you do not need to modify the code even if the array size changes. Generally, processing speed is slightly faster than the For Each method described below.

Method 2: For Each…Next Loop

This method stores each element in the array into a variable for each iteration of the loop.

Sub LoopWithForEach()
    Dim weekdays(1 To 7) As String
    Dim day As Variant
    
    ' Set values in the array
    weekdays(1) = "Mon": weekdays(2) = "Tue": weekdays(3) = "Wed"
    weekdays(4) = "Thu": weekdays(5) = "Fri": weekdays(6) = "Sat": weekdays(7) = "Sun"

    ' Process each element (day) in the array sequentially
    For Each day In weekdays
        Debug.Print day
    Next day
End Sub

The biggest advantage is that the code becomes simple and easy to read because you don’t need to worry about indices.

How to Clear (Initialize) an Array

There are several ways to empty a used array, but there is only one recommended method.

Method 1: Erase Statement (Strongly Recommended)

This is a dedicated command for clearing arrays. It is the fastest method and correct from a memory management perspective.

Sub ClearArrayWithErase()
    Dim weekdays(1 To 7) As String
    ' (Assume the array contains values)
    
    ' Initialize the array with Erase
    Erase weekdays
    
    ' For a string array, elements become empty strings ("")
    Debug.Print weekdays(1) ' -> Result is empty
End Sub

• Fixed-size array (e.g., Dim data(1 To 100)): Erase resets all elements to the default value for that data type (0 for numbers, “” for strings).
• Dynamic array (e.g., Dim data()): Erase completely releases the memory allocated to the array.

Method 2: Clearing Elements in a Loop (Not Recommended)

This method uses a For loop to assign empty values to all elements.

For i = LBound(weekdays) To UBound(weekdays)
    weekdays(i) = ""
Next i

This method is slower than Erase and requires more code, so it is usually not used.

[Warning] You Cannot Change (Clear) Array Values with For Each

If you try to clear an array using a For Each loop, it will not work as expected.

' !!! Example of Incorrect Code !!!
For Each day In weekdays
    day = "" ' <- Only the copy "day" becomes empty; the original array remains unchanged
Next day

The For Each loop variable (day) holds a copy of the array element. Therefore, changing the value of the variable day has absolutely no effect on the contents of the original weekdays array. This is a common mistake for beginners, so be careful.

Summary

The best practices for array manipulation are as follows:

• Reading Arrays:
  • Use For Each if you prioritize readability.
  • Use For i = LBound to UBound if you prioritize speed.
• Clearing Arrays:
  • Always use the Erase statement; it is the fastest and most reliable.
• Writing to Arrays:
  • Always use a For i loop specifying the index. You cannot write using For Each.

By understanding these characteristics and selecting the optimal method for the situation, you can improve the processing efficiency and code quality of your VBA.