VBA automation quickstart

Goal

Use this note as a test document for the docs pipeline and as a compact VBA reference for spreadsheet automation.

The examples focus on predictable workbook changes, clear error handling and macros that are easy to review before running.

Workbook setup

Create a dedicated macro-enabled workbook:

File -> Save As -> Excel Macro-Enabled Workbook (*.xlsm)
Developer -> Visual Basic -> Insert -> Module

Keep reusable code inside standard modules, not inside worksheet events. That makes the macro easier to audit, copy and run manually.

Basic macro

Option Explicit

Public Sub BuildStatusReport()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Report")

    ws.Range("A1").Value = "Status"
    ws.Range("B1").Value = "Updated"
    ws.Range("A2").Value = "Ready"
    ws.Range("B2").Value = Now

    ws.Columns("A:B").AutoFit
End Sub

Use Option Explicit in every module so undeclared variables fail fast instead of creating silent bugs.

Read rows safely

Option Explicit

Public Sub NormalizeInventory()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowIndex As Long

    Set ws = ThisWorkbook.Worksheets("Inventory")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowIndex = 2 To lastRow
        ws.Cells(rowIndex, "A").Value = Trim$(ws.Cells(rowIndex, "A").Value)
        ws.Cells(rowIndex, "B").Value = UCase$(Trim$(ws.Cells(rowIndex, "B").Value))
    Next rowIndex
End Sub

Avoid selecting cells in automation code. Work directly with worksheet and range objects.

Error handling

Option Explicit

Public Sub RefreshReport()
    On Error GoTo Fail

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ThisWorkbook.RefreshAll

CleanExit:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Exit Sub

Fail:
    MsgBox "Refresh failed: " & Err.Description, vbExclamation, "VBA"
    Resume CleanExit
End Sub

Always restore application state before exiting. This prevents a failed macro from leaving Excel slow or partially disabled.

Review checklist

• The macro has Option Explicit.
• The macro does not use hidden auto-run events.
• Workbook, worksheet and range references are explicit.
• External files, links and network locations are documented before execution.
• The code restores ScreenUpdating, Calculation and other global settings.
• The macro has a manual test path and a clear rollback plan.

Upload test

This file can be used to test the Notion upload helper:

npm run auto:notion:dry -- --input ./src/content/docs/vba-automation-quickstart.md --type docs --paginate h2
npm run auto:notion -- --input ./src/content/docs/vba-automation-quickstart.md --type docs --paginate h2 --force

With --paginate h2, the uploader creates one parent page and one child page for each ## section. After syncing back, the document should appear under /docs without exposing the hidden /web root.

- EOF -