Create Pareto Chart with Excel VBA

To create a Pareto chart in Excel using VBA, we first need to define the data and then set up a combination chart with a bar chart for the frequencies and a line chart for the cumulative percentage. Below is a detailed VBA code example to help you create a Pareto chart from scratch, followed by an explanation of each part of the code.

VBA Code to Create a Pareto Chart

Sub CreateParetoChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range
    Dim cumulativeRange As Range
    Dim lastRow As Long
    Dim i As Long
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change this to your sheet name
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Define the data range (Assumes data starts from A2 and B2)
    Set dataRange = ws.Range("A2:B" & lastRow)
    ' Sort data by frequency in descending order (Column B)
    dataRange.Sort Key1:=ws.Range("B2"), Order1:=xlDescending, Header:=xlNo
    ' Add a new column for cumulative percentage (Column C)
    ws.Cells(1, 3).Value = "Cumulative Percentage"
    ws.Cells(2, 3).Value = ws.Cells(2, 2).Value / Application.WorksheetFunction.Sum(ws.Range("B2:B" & lastRow))
    For i = 3 To lastRow
        ws.Cells(i, 3).Value = ws.Cells(i - 1, 3).Value + ws.Cells(i, 2).Value / Application.WorksheetFunction.Sum(ws.Range("B2:B" & lastRow))
    Next i
    ' Create the Pareto Chart (Combination Chart)
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=50, Height:=300)
    With chartObj.Chart
        .SetSourceData Source:=ws.Range("A1:C" & lastRow)
        .ChartType = xlColumnClustered ' Set column chart for frequency       
        ' Add the line chart for the cumulative percentage
        .SeriesCollection.NewSeries
        .SeriesCollection(2).XValues = ws.Range("A2:A" & lastRow)
        .SeriesCollection(2).Values = ws.Range("C2:C" & lastRow)
        .SeriesCollection(2).ChartType = xlLine
        .SeriesCollection(2).AxisGroup = 2 ' Use secondary axis for cumulative percentage       
        ' Set the secondary axis to percentage format
        .Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "0%"      
        ' Set chart title and axes labels
        .HasTitle = True
        .ChartTitle.Text = "Pareto Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Categories"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Frequency"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Text = "Cumulative Percentage"
    End With
End Sub

Detailed Explanation:

1. Set the Worksheet and Data Range:
   • The code starts by defining the worksheet (ws) where your data is located.
   • The dataRange is set from columns A and B (categories and frequencies). Ensure your data starts from row 2 (after the header).
2. Sorting Data by Frequency:
   • The dataRange.Sort function sorts the data in descending order based on the frequencies in column B. This step is essential for the Pareto principle (80/20 rule), where you want the most frequent categories to appear first.
3. Calculating Cumulative Percentages:
   • In column C, a cumulative percentage is calculated. For the first row, it’s just the frequency divided by the total sum of all frequencies.
   • For subsequent rows, the cumulative percentage is the sum of the previous cumulative percentage and the new category’s percentage.
4. Creating the Chart:
   • A new chart is created using ChartObjects.Add with specific dimensions.
   • The data range (A1:C & lastRow) is set as the source for the chart, which includes both the frequency and cumulative percentage columns.
   • The chart type is initially set as a clustered column chart (xlColumnClustered) for the frequency data.
   • A secondary line chart is added using SeriesCollection.NewSeries, representing the cumulative percentage. This line chart uses a secondary axis (xlSecondary), which is formatted as a percentage.
5. Setting Chart Titles and Axis Labels:
   • The chart is given a title: « Pareto Chart. »
   • Both the primary axis (representing the frequency) and the secondary axis (representing the cumulative percentage) are labeled.
   • The secondary axis is formatted to display values as percentages (0% format).

How the Code Works:

• This VBA code automates the creation of a Pareto chart. It first sorts your data by frequency, then calculates cumulative percentages, and finally generates the chart.
• The chart has a primary axis for frequencies and a secondary axis for cumulative percentages, so you can easily visualize both the frequencies and the cumulative effect of the categories.