Working with Charts in VBA

Analogy categoryAnalogies

ChartObjects, Charts, and Where They Live

Excel exposes charts through two related objects. A ChartObject is the container — the frame that floats on a worksheet, with position and size — while the Chart is the actual plot inside it, holding series, axes, and titles. When a chart is embedded on a sheet, you access the frame via ws.ChartObjects("Chart 1") and the plot via its .Chart property. A chart can also live on its own dedicated chart sheet, in which case it appears in the Charts collection and has no ChartObject wrapper. Knowing which object you hold determines which properties are available.

🏏

Cricket analogy: The ChartObject is the picture frame around a team photo, while the Chart is the photo itself — you move the frame on the wall but edit faces inside the picture, two distinct actions.

Creating a Chart and Setting Its Source Data

To add an embedded chart, call ws.ChartObjects.Add(Left, Top, Width, Height), which returns the new ChartObject positioned in points. You then reach its .Chart and call SetSourceData with a Range to bind the data, and set .ChartType using an XlChartType constant such as xlColumnClustered, xlLine, or xlPie. Capturing the returned reference lets you configure everything in one flow without selecting anything. Because coordinates are in points (1/72 inch), you can align charts precisely, and you can also snap a chart to a cell block by reading that range's .Left, .Top, .Width, and .Height.

🏏

Cricket analogy: SetSourceData is like assigning which batsmen's scores feed the scoreboard graphic — bind the wrong range and the display shows the opposition's totals.

vba

Sub CreateSalesChart()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")

    Dim co As ChartObject
    Set co = ws.ChartObjects.Add(Left:=300, Top:=20, Width:=400, Height:=250)

    With co.Chart
        .SetSourceData Source:=ws.Range("A1:B7")
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Monthly Sales"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Revenue (USD)"
    End With
End Sub

ChartObjects.Add expects dimensions in points, not pixels or cells. To anchor a chart neatly over a range, read the target block's geometry: with rng = ws.Range("E2:K18"), pass Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height so the chart snaps exactly to those cells.

Formatting Series, Axes, and Titles

Once a chart exists, you refine it through its member collections. SeriesCollection gives access to each plotted data series, letting you set a series .Name, its point colors via .Format.Fill.ForeColor.RGB, or add data labels with .HasDataLabels. The Axes method, Axes(xlValue) or Axes(xlCategory), reaches the value (y) and category (x) axes so you can set MinimumScale, MaximumScale, or number formats. Titles are toggled by boolean HasTitle properties before you set their text — attempting to set ChartTitle.Text without first setting HasTitle = True raises an error because the title object does not yet exist.

🏏

Cricket analogy: Setting each series colour is like assigning distinct jersey colours to teams on a Manhattan graphic so viewers instantly tell India's run rate from Australia's.

Setting ChartTitle.Text or AxisTitle.Text before enabling HasTitle = True (or HasDataLabels before it is on) throws a run-time error because the object does not exist yet. Always turn the corresponding Has... property on first. Likewise, deleting a ChartObject with .Delete removes the chart entirely and cannot be undone in code.

A ChartObject is the frame on a worksheet; the Chart is the plot inside, reached via the ChartObject's .Chart property.
Charts on dedicated chart sheets appear in the Charts collection and have no ChartObject wrapper.
ChartObjects.Add(Left, Top, Width, Height) uses points and returns a reference you should capture with Set.
SetSourceData binds a Range as the chart's data, and ChartType is set with XlChartType constants like xlColumnClustered.
SeriesCollection accesses individual data series for naming, coloring, and data labels.
Axes(xlValue) and Axes(xlCategory) reach the y and x axes to set scale and number formats.
Enable HasTitle/HasDataLabels before setting their text or values, or VBA raises an error.