Why Cell-by-Cell Loops Are Slow

Every cell you touch crosses a border

Your macro and the worksheet are two different pieces of software talking to each other. VBA runs in one place, the Excel calculation and display engine in another, and every single time your code reads or writes a cell a message has to travel between them. That trip is tiny, but it is roughly a thousand times more expensive than working with a number already sitting in memory. Do it once and you will never notice. Do it 60,000 times and you will go and make coffee.

Do not take my word for it — measure it

Paste the whole block below into a fresh module and run BenchmarkCellsVsArray. It builds its own sheet of 20,000 rows, multiplies quantity by price the cell-by-cell way, rebuilds the identical data, does the same job through an array, and shows you both timings and the factor between them. Both versions produce exactly the same numbers in column C, and both run with the screen and recalculation switched off, so what you are seeing is purely the cost of talking to the worksheet.

Option Explicit

Private Const DEMO_SHEET As String = "Speed Demo"
Private Const DEMO_ROWS As Long = 20000

Sub BenchmarkCellsVsArray()
    Dim slowSeconds As Double
    Dim fastSeconds As Double
    Dim previousUpdating As Boolean
    Dim previousCalc As XlCalculation
    Dim verdict As String

    previousUpdating = Application.ScreenUpdating
    previousCalc = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    MakeDemoData
    slowSeconds = Timer
    SlowVersion
    slowSeconds = Timer - slowSeconds

    MakeDemoData
    fastSeconds = Timer
    FastVersion
    fastSeconds = Timer - fastSeconds

    Application.Calculation = previousCalc
    Application.ScreenUpdating = previousUpdating

    If fastSeconds > 0 Then
        verdict = Format(slowSeconds / fastSeconds, "0") & " times faster"
    Else
        verdict = "too fast for Timer to measure"
    End If

    MsgBox DEMO_ROWS & " rows, same result both times" & vbCrLf & vbCrLf & _
           "Cell by cell: " & Format(slowSeconds, "0.000") & " s" & vbCrLf & _
           "Array:        " & Format(fastSeconds, "0.000") & " s" & vbCrLf & vbCrLf & _
           "The array is " & verdict & ".", vbInformation, "Cells versus array"
End Sub

Sub SlowVersion()
    Dim ws As Worksheet
    Dim r As Long

    Set ws = DemoSheet()

    For r = 2 To DEMO_ROWS + 1
        ws.Cells(r, 3).Value = ws.Cells(r, 1).Value * ws.Cells(r, 2).Value
    Next r
End Sub

Sub FastVersion()
    Dim ws As Worksheet
    Dim data As Variant
    Dim r As Long

    Set ws = DemoSheet()
    data = ws.Range("A2").Resize(DEMO_ROWS, 3).Value

    For r = 1 To UBound(data, 1)
        data(r, 3) = data(r, 1) * data(r, 2)
    Next r

    ws.Range("A2").Resize(DEMO_ROWS, 3).Value = data
End Sub

Private Sub MakeDemoData()
    Dim ws As Worksheet
    Dim block() As Double
    Dim r As Long

    Set ws = DemoSheet()
    ws.Cells.ClearContents
    ws.Range("A1:C1").Value = Array("Quantity", "Unit price", "Total")

    ReDim block(1 To DEMO_ROWS, 1 To 2)
    For r = 1 To DEMO_ROWS
        block(r, 1) = (r Mod 25) + 1
        block(r, 2) = 4.95 + (r Mod 40) / 10
    Next r

    ws.Range("A2").Resize(DEMO_ROWS, 2).Value = block
End Sub

Private Function DemoSheet() As Worksheet
    Dim ws As Worksheet
    Dim found As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = DEMO_SHEET Then Set found = ws
    Next ws

    If found Is Nothing Then
        Set found = ThisWorkbook.Worksheets.Add
        found.Name = DEMO_SHEET
    End If

    Set DemoSheet = found
End Function

Line by line

• SlowVersion reads two cells and writes one for every row: 60,000 separate crossings of that border.
• FastVersion does the same arithmetic with two crossings in total. data = ws.Range(…).Value pulls the whole block into a Variant array, the loop works purely in memory, and the last line pushes it all back in one assignment.
• MakeDemoData runs before each timing, never inside it, so neither version is charged for building the data.
• previousUpdating and previousCalc store the user's settings before changing them and put them back afterwards. Leaving calculation on manual is a rude thing to do to somebody's workbook.
• Timer returns the seconds since midnight as a decimal; subtract two readings and you have the elapsed time. If the array run reports zero, it simply finished below Timer's resolution.
• Raise DEMO_ROWS to 50,000 if your machine is quick. The array version barely notices; the other one grows in a straight line.

Where the time actually goes

• The crossing itself — the fixed cost of asking Excel a question, paid once per cell, and the only part the array removes.
• Recalculation — if any formula depends on the cell you just wrote, Excel may recalculate before your next line runs.
• Redrawing — by default Excel repaints the screen as values change, and painting is slow.
• Events — a Worksheet_Change handler, if one exists, fires on every single write you make.

The trap: Cells without a sheet in front of it

This one is worth an afternoon. Cells(r, 3) with nothing in front of it does not mean the sheet my data is on; it means the sheet that is active right now. Run such a macro from the VBA editor while a different sheet is on top, or let a user click another tab during a long run, and 20,000 values land in the wrong place — over real data, with no error at all. Qualify every reference with a worksheet variable, exactly as the benchmark does.

' Wrong: whichever sheet is active when this runs gets the values.
For r = 2 To DEMO_ROWS + 1
    Cells(r, 3).Value = Cells(r, 1).Value * Cells(r, 2).Value
Next r

' Right: every reference is tied to one worksheet.
For r = 2 To DEMO_ROWS + 1
    ws.Cells(r, 3).Value = ws.Cells(r, 1).Value * ws.Cells(r, 2).Value
Next r

How it goes on

The benchmark shows the payoff; the rest of the course shows how to get it on real, messy data. The next lessons cover reading a range into an array and the index rules that surprise everyone, doing the work in memory, writing back into a range of exactly the right shape, the Application switches that buy the last few per cent, measuring properly with Timer, and the traps — filtered rows, merged cells, one-column ranges — with a template worth keeping.