Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 37 ยท Projects and Practice

Project: Sales Report Builder

A complete VBA project: read raw sales data, summarize by region with a dictionary, write a formatted report, add a chart and save a dated PDF, step by step.

In this project you build a macro that turns a raw list of sales into a finished report: totals per region, a formatted summary sheet, a chart and a PDF with today's date in the file name. It uses nearly everything from the Excel chapter: ranges and arrays, dictionaries, formatting, charts, plus error handling and file operations.

VBA cannot run on this site, so the VBA is read-only. The JavaScript TryIts reproduce the data-processing core (summing, sorting, formatting), so you can check the logic before you type the macro into Excel.

The goal and the plan

Input: a sheet Data with the header Date | Region | Rep | Amount and one row per sale. Output: a sheet Report with one row per region (total, share of overall, rank), a column chart and a PDF.

flowchart TD
  A["Read Data into an array"] --> B["Sum by region (Dictionary)"]
  B --> C["Sort regions by total"]
  C --> D["Write Report sheet"]
  D --> E["Format + chart"]
  E --> F["Export PDF"]

Breaking the job into small procedures keeps each one testable.

Structure: one job per procedure

VB
Option Explicit

Public Sub BuildSalesReport()
    Dim totals As Object
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set totals = SumByRegion(ThisWorkbook.Worksheets("Data"))
    If totals.Count = 0 Then
        MsgBox "No sales data found.", vbInformation
        GoTo CleanExit
    End If

    WriteReport totals, PrepareReportSheet()
    AddChart ThisWorkbook.Worksheets("Report"), totals.Count
    ExportPdf ThisWorkbook.Worksheets("Report")

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "Report failed: " & Err.Description, vbCritical
    Resume CleanExit
End Sub

The main procedure only orchestrates and handles errors; helpers do the work. That makes it easy to test each helper alone from the Immediate window.

Step 1: summarize

VB
Private Function SumByRegion(ws As Worksheet) As Object
    Dim d As Object: Set d = CreateObject("Scripting.Dictionary")
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Set SumByRegion = d: Exit Function

    Dim data As Variant: data = ws.Range("A2:D" & lastRow).Value2
    Dim i As Long, key As String
    For i = 1 To UBound(data, 1)
        If IsNumeric(data(i, 4)) And Len(data(i, 2)) > 0 Then
            key = Trim$(CStr(data(i, 2)))
            d(key) = d(key) + CDbl(data(i, 4))
        End If
    Next i
    Set SumByRegion = d
End Function

The loop reads from an array, skips blank regions and non-numeric amounts, and accumulates into the dictionary: no cell-by-cell access, so 100,000 rows run in a blink.

Step 2: write the report

VB
Private Function PrepareReportSheet() As Worksheet
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Report")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "Report"
    Else
        ws.Cells.Clear
        Dim co As ChartObject
        For Each co In ws.ChartObjects: co.Delete: Next co
    End If
    Set PrepareReportSheet = ws
End Function

Private Sub WriteReport(totals As Object, ws As Worksheet)
    Dim keys As Variant: keys = totals.Keys
    Dim n As Long: n = totals.Count
    Dim i As Long
    Dim grand As Double
    For i = 0 To n - 1: grand = grand + totals(keys(i)): Next i

    ws.Range("A1:D1").Value = Array("Region", "Total", "Share", "Rank")
    For i = 0 To n - 1
        ws.Cells(i + 2, 1).Value = keys(i)
        ws.Cells(i + 2, 2).Value = totals(keys(i))
        ws.Cells(i + 2, 3).Formula = "=B" & i + 2 & "/SUM($B$2:$B$" & n + 1 & ")"
        ws.Cells(i + 2, 4).Formula = "=RANK(B" & i + 2 & ",$B$2:$B$" & n + 1 & ")"
    Next i

    ws.Range("A2").Resize(n, 4).Sort Key1:=ws.Range("B2"), Order1:=xlDescending, Header:=xlNo
    ws.Range("B2:B" & n + 1).NumberFormat = "#,##0.00"
    ws.Range("C2:C" & n + 1).NumberFormat = "0.0%"
    With ws.Range("A1:D1")
        .Font.Bold = True: .Interior.Color = RGB(31, 78, 121): .Font.Color = vbWhite
    End With
    ws.Columns("A:D").AutoFit
End Sub

The share and rank columns are real formulas, so the report stays correct if someone edits a total later.

Step 3: chart and PDF

VB
Private Sub AddChart(ws As Worksheet, n As Long)
    Dim co As ChartObject
    Set co = ws.ChartObjects.Add(ws.Range("F2").Left, ws.Range("F2").Top, 380, 220)
    With co.Chart
        .SetSourceData ws.Range("A1:B" & n + 1)
        .ChartType = xlColumnClustered
        .HasTitle = True: .ChartTitle.Text = "Sales by region"
        .HasLegend = False
    End With
End Sub

Private Sub ExportPdf(ws As Worksheet)
    Dim path As String
    path = ThisWorkbook.Path & "\SalesReport_" & Format(Date, "yyyy-mm-dd") & ".pdf"
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path, OpenAfterPublish:=False
End Sub

Try it: the data core

JS Sum by region, share and rank

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Dated file name and money format

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Ideas to extend it

  • Add a filter by month using an input box or UserForm.
  • E-mail the PDF via Outlook (see Outlook automation).
  • Log every run (time, rows read, errors) to a Log sheet.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why is the main procedure kept short and split into helpers?

    Small procedures with one job are easier to test, debug and reuse.
  2. 2. Why read the Data sheet into an array?

    One bulk read replaces thousands of calls between VBA and Excel.
  3. 3. Why write Share and Rank as formulas?

    Cell formulas keep updating, which keeps the report auditable.

Summary

  • Plan the pipeline: read, summarize, write, format, chart, export.
  • Keep an orchestrating Sub with error handling and small helper procedures.
  • Use arrays and dictionaries for speed; use formulas where live updates matter.
  • Name outputs with dates and export PDFs with ExportAsFixedFormat.

Next: Project: Contact Manager with a UserForm