May 20, 2019

Efficiency of Arrays in Calculations

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!

2 thoughts on “Efficiency of Arrays in Calculations

  1. Dear Mr. Dinesh Kumar Takyar,
    I am really delighted with your tutorials more especially Efficiency of Arrays in Calculations.
    Thanks a lot
    My question is can we use this array method for copying cells from one sheet with several rows like this and paste to another sheet.
    I used your tutorial “copying and paste” which is very perfect but it takes a lot to loop through the data and paste it back to another sheet.
    Thanks a lot.
    jaakinye

  2. Respected Sir
    Your excel training videos are really appreciable. I have learned a lot from your videos.thanks very much sir

Comments are closed.