Day 10 — Arrays, and why they make macros fast

This is the difference between a macro that takes four minutes and the same macro taking under a second.

An array is a variable with numbered compartments

Sub SimpleArray()
    Dim regions(1 To 4) As String
    Dim i As Long

    regions(1) = "North"
    regions(2) = "South"
    regions(3) = "East"
    regions(4) = "West"

    For i = 1 To 4
        Debug.Print i, regions(i)
    Next i
End Sub

LBound and UBound - never hard-code the size

These ask the array how big it is. Use them and your loop keeps working when the array changes size.

Sub Bounds()
    Dim a(1 To 4) As String
    Dim b(3) As String

    Debug.Print "a runs", LBound(a), "to", UBound(a)
    Debug.Print "b runs", LBound(b), "to", UBound(b)
End Sub

Dim b(3) gives you four slots Without 1 To, VBA starts at 0. So Dim b(3) is b(0), b(1), b(2), b(3). Write Dim a(1 To 4) and the numbering matches how you think. Arrays out of Split always start at 0, which is why UBound(parts) + 1 counts the pieces.

Now the important part

Every time VBA touches a cell it crosses from the code into Excel and back. That crossing is slow. Do it 100,000 times and you can watch the screen struggle.

The fix: cross once, bring the whole block into memory, work there, then cross once more to write it back.

arr = Range("A2:G17").Value → one trip out, everything in memory

Sub ReadTheBlock()
    Dim data As Variant

    data = Range("A2:G17").Value

    Debug.Print "Rows:", UBound(data, 1)
    Debug.Print "Columns:", UBound(data, 2)
    Debug.Print "Top left:", data(1, 1)
    Debug.Print "Row 1, region:", data(1, 3)
    Debug.Print "Row 16, product:", data(16, 5)
End Sub

The numbering restarts at 1 Range("A2:G17") becomes data(1, 1) to data(16, 7). Sheet row 2 is array row 1; sheet column A is array column 1. Forgetting the offset is the number one array bug - if everything is out by one row, this is why.
It is 1-based on both axes even though a plain Dim b(3) is 0-based. Yes, that is inconsistent. It is VBA.

Working entirely in memory

Sub TotalFromArray()
    Dim data As Variant
    Dim i As Long
    Dim total As Double

    data = Range("F2:G17").Value

    For i = 1 To UBound(data, 1)
        total = total + data(i, 1) * data(i, 2)
    Next i

    Debug.Print "Grand total:", total
End Sub

One visit to the sheet in that entire macro. The Day 5 version, reading two cells on every row, made thirty-two.

Write it back in one go

Assigning an array to a range of the same shape fills the block in a single trip.

Sub FastFill()
    Dim src As Variant
    Dim out As Variant
    Dim i As Long
    Dim n As Long

    src = Range("F2:G17").Value
    n = UBound(src, 1)

    out = Range("H2:H17").Value

    For i = 1 To n
        out(i, 1) = src(i, 1) * src(i, 2)
    Next i

    Range("H1").Value = "Total"
    Range("H2:H17").Value = out

    Debug.Print "Wrote", n, "totals in one go"
    Debug.Print "H2 =", Cells(2, 8).Value
    Debug.Print "H17 =", Cells(17, 8).Value
End Sub

Reading H2:H17 first is a neat trick - it hands you an array of exactly the right shape to fill in, so you never have to work out the dimensions yourself.

Building a summary in memory

Four regions, one pass, no repeated scanning of the sheet.

Sub RegionSummary()
    Dim data As Variant
    Dim names(1 To 4) As String
    Dim totals(1 To 4) As Double
    Dim i As Long
    Dim j As Long

    names(1) = "North": names(2) = "South"
    names(3) = "East":  names(4) = "West"

    data = Range("A2:G17").Value

    For i = 1 To UBound(data, 1)
        For j = 1 To 4
            If data(i, 3) = names(j) Then
                totals(j) = totals(j) + data(i, 6) * data(i, 7)
                Exit For
            End If
        Next j
    Next i

    For j = 1 To 4
        Cells(j + 1, 10).Value = names(j)
        Cells(j + 1, 11).Value = totals(j)
        Debug.Print names(j), totals(j)
    Next j

    Cells(1, 10).Value = "Region"
    Cells(1, 11).Value = "Value"
End Sub

Two colons on one line names(1) = "North": names(2) = "South" - a colon separates two statements on the same line. Use it sparingly, for short related pairs like this, and never to squeeze real logic onto one line.

Try these yourself

Declare an array of the four product names and print it with a loop.
Read A2:G17 into an array and print the salesperson from array row 5. Which sheet row is that?
Total the units column entirely in memory, with no Cells inside the loop.
Extend RegionSummary to also count the orders per region into column L.
Explain in your own words why reading a range into an array makes a macro faster.

← PreviousDay 9 — Subs, Functions and arguments Next →Day 11 — Formatting, With blocks and WorksheetFunction