Macros, worksheets and ranges

The Office object model, reading whole blocks into arrays instead of cell by cell, and where event handlers must live.

The object model

Everything you touch belongs to a hierarchy: Application contains Workbooks, a workbook contains Worksheets, a worksheet contains Range objects. Naming the object you mean at every level is the single biggest difference between a macro that works and one that depends on whatever sheet happened to be active.

Public Sub WorkingWithRanges()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")   ' explicit: never rely on ActiveSheet

    ' The five ways to reach a cell
    Debug.Print ws.Range("A1").Value
    Debug.Print ws.Cells(1, 1).Value           ' row, column, both Long
    Debug.Print ws.Range("A1:C3").Count        ' 9 cells
    Debug.Print ws.[A1].Value                  ' shorthand, read-only style
    Debug.Print ws.Rows(1).Cells(1).Value

    ' The last used row in column A: End works like Ctrl + Arrow
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Debug.Print lastRow, ws.UsedRange.Rows.Count

    ' Offset and Resize are relative and never select anything
    ws.Range("A1").Offset(1, 2).Value = "C2"
    ws.Range("A1").Resize(3, 2).Interior.Color = RGB(255, 235, 156)
    ws.Range("A1").EntireRow.AutoFit

    ' Value2 gives the raw number or string without currency or date types
    Debug.Print ws.Range("A1").Value2, ws.Range("A1").Text
    ws.Range("A1").NumberFormat = "0.00"
    ws.Range("A1").Formula = "=SUM(B1:B10)"
    ws.Range("A1").FormulaR1C1 = "=SUM(RC[1]:RC[10])"
    ws.Range("A1:C3").Copy Destination:=ws.Range("E1")

    ' Whole rows and columns, and the correct way to delete one
    ws.Range("A2").EntireRow.Delete
End Sub

• ThisWorkbook is the file holding the code, ActiveWorkbook is whichever file has focus, and Workbooks("name.xlsm") is a named file. Only the first is stable.
• Sheets includes chart sheets; Worksheets does not. Use Worksheets unless you really mean both.
• .Value converts to a Date or Currency where the format allows it; .Value2 returns the underlying Double and is faster.
• Cells(0, 1), a negative index, or a row past 1,048,576 raises a run-time error, so clamp lastRow before using it.
• Deleting while looping upward skips rows: loop from the last row to the first, or collect the rows to delete and remove them in one call.

Move data in blocks, not cells

Every property read is a call across a COM boundary, and a cell-by-cell loop pays that cost once per cell. Reading a whole range into a Variant array is one call, and writing it back is another, which routinely turns minutes into milliseconds.

Public Sub DoubleColumnA()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    ' One read: a 1-based 2-dimensional Variant array, always (1 To rows, 1 To cols)
    Dim data As Variant
    data = ws.Range("A2:A" & lastRow).Value2

    Dim i As Long
    For i = LBound(data, 1) To UBound(data, 1)
        If IsNumeric(data(i, 1)) Then
            data(i, 1) = data(i, 1) * 2
        End If
    Next i

    ' One write back to the same shape
    ws.Range("A2:A" & lastRow).Value2 = data
End Sub

Public Sub FastWrite()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    On Error GoTo Cleanup

    Dim block(1 To 10000, 1 To 3) As Variant
    Dim i As Long, j As Long
    For i = 1 To 10000
        For j = 1 To 3
            block(i, j) = i * j
        Next j
    Next i
    ThisWorkbook.Worksheets("Data").Range("A1").Resize(10000, 3).Value2 = block

Cleanup:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Events and automation

Event handlers are not standard procedures: they are procedures with fixed names that must live in the ThisWorkbook module (workbook events) or a sheet module (worksheet events). In a standard module they simply never run.

' ---------- ThisWorkbook module ----------
Private Sub Workbook_Open()
    MsgBox "Loaded " & Format$(Now, "yyyy-mm-dd hh:nn"), vbInformation
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If ThisWorkbook.Worksheets("Data").Range("A1").Value = "" Then
        MsgBox "A1 is empty: fill it before saving.", vbExclamation
        Cancel = True                     ' this is how you veto the save
    End If
End Sub

' ---------- The sheet module named Data ----------
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub

    On Error GoTo Cleanup
    Application.EnableEvents = False      ' writing here would re-enter this handler
    Target.Offset(0, 1).Value = Now
Cleanup:
    Application.EnableEvents = True
End Sub

• Worksheet_Change fires once per user edit but fires for every cell when a range is pasted, so Target can hold thousands of cells.
• Application.EnableEvents = False does not switch itself back on, and a crash mid-handler leaves events disabled for the rest of the session.
• Application.OnTime schedules a procedure for later, and returns a value you need to keep if you intend to cancel it.
• Use Application.Run "SomeMacro", arg to call a macro by name, which is how toolbar buttons and other workbooks reach your code.

FAQ

Related

The VBA editor and the language Error handling and the classic pitfalls

Last refreshed 2026-09-18.