Practical VBA workshop

Goal: build a readable report header and apply consistent number formats without selecting cells.

A monthly report arrives as plain values. The title, headers, dates and amounts must look consistent before the workbook is shared.

What you will build

• A complete procedure that works without Select or Activate.
• Test data and expected behaviour you can verify before using real files.
• Error cases and production checks that prevent silent damage.

Complete VBA example

Option Explicit
Public Sub FormatReportPart1()
    Dim ws As Worksheet, lastRow As Long
    Set ws = ThisWorkbook.Worksheets("Report")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    With ws.Range("A1:D1")
        .Merge
        .Value = "Monthly Sales Report"
        .Font.Name = "Aptos Display"
        .Font.Size = 16
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    With ws.Range("A2:D2")
        .Font.Bold = True
        .Borders(xlEdgeBottom).Weight = xlMedium
    End With
    ws.Range("A3:A" & lastRow).NumberFormat = "dd-mmm-yyyy"
    ws.Range("C3:D" & lastRow).NumberFormat = "₹#,##0.00;[Red]-₹#,##0.00"
    ws.Columns("A:D").AutoFit
End Sub

How the solution works

• Qualify every Range through a worksheet variable so the active sheet cannot receive accidental formatting.
• NumberFormat changes presentation, not the stored value; calculations continue to use the underlying number.
• A single With block avoids repeated object lookups and makes the intended range obvious.

Run it step by step

1. Download and unzip the practice pack below. Open the matching CSV or copy it into a new macro-enabled workbook.
2. Press Alt + F11, choose File → Import File, and import this chapter’s .bas module.
3. Rename the sample worksheet to the name used in the code, or change the worksheet name in one clearly marked line.
4. Run the procedure with F5. Compare the result with the expected values and boundary cases in the CSV.
5. Undo is not available after a macro changes a workbook. Test on a copy, save only after checking totals, and keep the original source file.

Common mistakes

• Using Selection or ActiveCell makes the macro depend on where the user clicked.
• Merging data rows breaks sorting; merge only a title area when it is genuinely needed.
• Applying formatting to whole columns can enlarge the used range and workbook size.

Make it production-ready

Add Option Explicit, qualify every workbook and worksheet, validate the last row and input types, and use a single cleanup section to restore ScreenUpdating, EnableEvents and calculation settings after an error. Avoid hard-coded regulatory rates or colour-only business rules. Record the source, effective date and assumptions in a visible Notes sheet.

Practice challenge

Add a fifth column named Variance and display positive amounts in green, negative amounts in red, and zero as a dash.

Test normal data, blanks, text in numeric columns, duplicate values and the exact threshold boundaries. A useful macro should fail clearly rather than produce a plausible but wrong answer.