Skip to content
Reading settings
ExcelAdvanced~55 min practiceLesson 25 of 34

Build a small report with VBA

Read a recorded action, run a report macro and test its cleanup path.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Copy an expense report into reserved cells and restore ScreenUpdating if the macro fails.

Example

A macro with a clear source and destination

Use a new practice workbook with Source and Report sheets. Enter Item and Amount in Source!A1:B1, then Travel 120, Supplies 45 and Training 80 in rows 2–4. Reserve Report!A1:B6 for the macro. Keep this exercise out of workbooks containing other Report data.

How it works

A recorded macro often uses the active sheet or selection. ThisWorkbook and named worksheets make the destination clear even when another workbook is active.

A macro can stop halfway through. Save application settings before changing them and restore those saved values on both success and failure.

Steps

  1. Create Source and Report and check source total 245. Save an XLSX backup, then save a separate XLSM practice copy for the macro.

  2. On desktop, enable Developer using your edition’s settings. Record an action that makes Report!A1 bold, stop recording and open the code in Visual Basic Editor to see how the action was recorded.

  3. Choose Insert > Module in the editor and paste the procedure below. Read the Source and Report targets and the cell ranges it writes before running it.

  4. Check that both sheets exist. The procedure copies values from A1:B4, writes a SUM formula and restores the earlier ScreenUpdating value through one cleanup path. Use it only in the reserved practice workbook.

    Option Explicit
Sub BuildExpenseReport()
    Dim src As Worksheet, rpt As Worksheet
    Dim values As Variant
    Dim previousScreenUpdating As Boolean
    previousScreenUpdating = Application.ScreenUpdating
    On Error GoTo Failed
    Set src = ThisWorkbook.Worksheets("Source")
    Set rpt = ThisWorkbook.Worksheets("Report")
    values = src.Range("A1:B4").Value
    Application.ScreenUpdating = False
    rpt.Range("A1:B4").Value = values
    rpt.Range("A6").Value = "Total"
    rpt.Range("B6").Formula = "=SUM(B2:B4)"
    rpt.Range("B2:B6").NumberFormat = "0.00"
CleanExit:
    Application.ScreenUpdating = previousScreenUpdating
    Exit Sub
Failed:
    MsgBox "Report not completed: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

  5. Run BuildExpenseReport through Developer > Macros. Check Report!B2:B4 for 120, 45 and 80 and B6 for 245. Compare Source with the XLSX backup.

  6. On a copy, rename Report to ReportMissing and run the macro again. Read the message and check ScreenUpdating is back to its prior value. Restore the sheet name before the next run.

  7. Change Source Training to 90 and rerun. Report!B6 should become 255. Note the input and output ranges, macro location and desktop requirement in Readme.

Try it yourself

  1. Add an explicit preflight check for the two required sheet names before any write, describing its intended behavior in plain English before modifying the code. Keep the original bounded output requirement.
  2. Record a test log for normal input, a missing output sheet and the changed Training amount. Do not mark the procedure production-ready merely because one run produced the expected total.
Show expected result
  • The original report totals 245 and the updated Training example totals 255. Source remains the input, while writes are limited to the named Report ranges.
  • A missing required sheet produces a reported failure, and the previous ScreenUpdating setting is restored through the common cleanup path.

If something goes wrong

The macro writes into the workbook currently in front.
Look for ActiveWorkbook, ActiveSheet or Selection in the code. This procedure uses ThisWorkbook and named sheets. Run the copy whose Report ranges you reserved for output.
The XLSM opens in a browser but the macro does not run.
Open it in supporting desktop Excel. Excel for the web can retain an XLSM file but cannot create, run or edit VBA.
The VBA module is gone after saving.
Check whether the file was saved as XLSX. Return to the backup or earlier macro-enabled copy and save the VBA version as XLSM.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. Which file format retains VBA in this task?
2. What should ScreenUpdating become after a failure?
3. What is the original Report total?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Review this VBA procedure before I run it. Check the workbook, worksheet names, output ranges and ScreenUpdating cleanup. Give me tests for a normal run and a missing sheet.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…