~/docs /VBA automation quickstart

VBA automation quickstart

VBA automation quickstart

Imported from Notion: VBA automation quickstart

updated:: 2026.07.10

Goal

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

The examples focus on:

  • predictable workbook changes,

  • clear error handling,

  • macros that are easy to review before running,

  • and a structure that is simple to split into paginated subpages when needed.

validated

If you use this note as a real onboarding or internal docs page, keep each ## section focused on one task. That makes --paginate h2 produce cleaner child pages later.

Suggested page structure

For readability and easier pagination, this page is organized into short, self-contained sections:

  1. Goal

  2. Workbook setup

  3. Basic macro

  4. Read rows safely

  5. Error handling

  6. Review checklist

  7. Common pitfalls

  8. Upload test

important

The page is intentionally written so each ## heading can become a separate child page if your Notion upload pipeline paginates by h2.

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 macros easier to audit, copy, test, and run manually.

  • Use a separate workbook for automation tests.

  • Save the file as .xlsm before adding code.

  • Keep one macro per procedure when possible.

  • Document any external dependencies before execution.

  • Test on a copy of the workbook first.

warning

Macros can change data quickly and sometimes irreversibly. Always validate the target workbook and keep a backup before running automation code.

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.

What this macro does

  • Opens the Report worksheet in the current workbook.

  • Writes a small status block into cells A1:B2.

  • Inserts the current timestamp with Now.

  • Adjusts column width for readability.

validated

If a worksheet may not exist yet, add a validation step before referencing it. That avoids runtime errors and makes the macro more resilient.

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.

Practical notes

  • lastRow is calculated from column A, so the loop stops at the last non-empty row in that column.

  • The code assumes row 1 contains headers.

  • Trim$ removes leading and trailing spaces.

  • UCase$ normalizes text to uppercase.

important

If your source data can contain blanks in column A, consider a more robust last-row strategy or validate the input layout before processing.

Safer variant for larger sheets

For larger workbooks, you may want to reduce repeated cell-by-cell writes by loading data into memory, transforming it, and writing it back in one operation. That is usually faster and easier to scale.

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.

Why this pattern matters

  • On Error GoTo Fail routes unexpected errors to a controlled handler.

  • ScreenUpdating = False reduces visual flicker during automation.

  • Calculation = xlCalculationManual prevents unnecessary recalculation while the macro runs.

  • The CleanExit block restores Excel settings even when an error occurs.

critical

If you change other global settings such as EnableEvents, DisplayAlerts, or StatusBar, restore them in the same cleanup block so Excel returns to a safe state.

Safer cleanup pattern

Option Explicit

Public Sub RefreshReportSafe()
    Dim oldCalculation As XlCalculation
    Dim oldScreenUpdating As Boolean

    On Error GoTo Fail

    oldCalculation = Application.Calculation
    oldScreenUpdating = Application.ScreenUpdating

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ThisWorkbook.RefreshAll

CleanExit:
    Application.Calculation = oldCalculation
    Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

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

Review checklist

Use this checklist before running or publishing a macro:

  • 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 any other global settings it changes.

  • The macro has a manual test path and a clear rollback plan.

  • The macro has been tested on a copy of the workbook.

  • Any assumptions about worksheet names, headers, or data layout are documented.

Common pitfalls

1. Implicit references

Avoid code that depends on the active workbook, active sheet, or selected cells unless that behavior is intentional.

2. Missing error cleanup

If a macro stops after changing Excel settings, later workbooks may behave unexpectedly. Always centralize cleanup.

3. Hard-coded structure assumptions

If the workbook layout can change, validate sheet names, headers, and expected ranges before writing data.

4. Silent type coercion

VBA can coerce values in ways that are hard to spot. Use Option Explicit and keep data transformations simple and explicit.

validated

A small validation step at the top of the macro often saves more time than debugging the failure later.

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.

Expected behavior

  • The page is split at each ## heading.

  • Each section becomes a child page.

  • The parent page remains a compact index.

  • The resulting structure stays under /docs.

tip

If the pipeline changes in the future, update this section with the current pagination rule so the test remains trustworthy.

- EOF -