How to measure the efficiency of arrays in calculations and actions versus the looping process.

Watch this video on YouTube.

Here’s the complete VBA code:

Sub myLoop()
Dim i As Long
Dim StartTime As Single

StartTime = Timer

For i = 2 To 100000
If Cells(i, 4) >= 90 Then
Cells(i, 5) = “A”
Cells(i, 6) = “Promoted to Class XII A”
Else
Cells(i, 5) = “B”
Cells(i, 6) = “Promoted to Class XII B”
End If

Next i

MsgBox Timer – StartTime & ” seconds”

End Sub

Sub myForEachLoop()
Dim i As Long
Dim StartTime As Single

StartTime = Timer

For Each cell In Range(“D2:D100000”)
If cell.Value >= 90 Then
cell.Offset(0, 1) = “A”
cell.Offset(0, 2) = “Promoted to Class XII A”
Else
cell.Offset(0, 1) = “B”
cell.Offset(0, 2) = “Promoted to Class XII B”
End If

Next cell

MsgBox Timer – StartTime & ” seconds”

End Sub

Sub calculateUsingArray()
Dim marks() As Variant
Dim results() As Variant
Dim mydimension As Long, counter As Long
Dim StartTime As Single
Sheet1.Activate

marks = Range(“D2”, Range(“D2”).End(xlDown))

mydimension = UBound(marks, 1)

ReDim results(1 To mydimension, 1 To 2)

StartTime = Timer

For counter = 1 To mydimension
If marks(counter, 1) >= 90 Then
results(counter, 1) = “A”
results(counter, 2) = “Promoted to Class XII A”
Else
results(counter, 1) = “B”
results(counter, 2) = “Promoted to Class XII B”
End If

Next counter

Range(“E2”, Range(“E2”).Offset(mydimension – 1, 1)).Value = results

MsgBox Timer – StartTime & ” seconds”

End Sub

How the array code works: In the marks array we get the data from the range D containing the marks of the students. Now we can determine the UBOUND range of the marks dynamic array. We use this UBOUND value to populate the results dynamic array. The data in the results dynamic array is then placed in the E and F columns. Finally we use the Timer to compare the speeds of the ‘for next’ looping process with the Array method. The ‘for loop’ process takes about 60-90 seconds whereas the Array method does the same job in 0.3 seconds!