ChartObjects and the Chart Inside Them

A chart on a sheet is two objects

This catches everyone once. The box floating on your worksheet is a ChartObject — a container, like a picture frame, with a position, a size, a name and a border. The picture inside the frame is the Chart, and that is what owns the series, the axes, the title and the type.

• ChartObject — .Left, .Top, .Width, .Height, .Name, .Delete.
• Chart — .ChartType, .SetSourceData, .SeriesCollection, .Axes, .ChartTitle, .Export.
• The bridge — co.Chart gets from the frame to the picture, ch.Parent back again.
• The rule — where the chart sits is the ChartObject, what the chart shows is the Chart. co.ChartType fails and so does ch.Left.

First, something to look at

Run this once to get a sheet with data and two badly placed charts on it. Lessons 2 and 3 explain these four chart lines properly; here they are only there to give the next macro some work.

Option Explicit

Sub MakeChartDemo()
    Dim ws As Worksheet
    Dim co As ChartObject
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = "Chart Demo " & Format(Now, "hhmmss")

    ws.Range("A1:B1").Value = Array("Month", "Revenue")
    For r = 2 To 13
        ws.Cells(r, 1).Value = Format(DateSerial(2026, r - 1, 1), "mmm")
        ws.Cells(r, 2).Value = 1000 + r * 137
    Next r

    Set co = ws.ChartObjects.Add(Left:=200, Top:=20, Width:=300, Height:=180)
    co.Chart.SetSourceData Source:=ws.Range("A1:B13")
    co.Chart.ChartType = xlColumnClustered

    Set co = ws.ChartObjects.Add(Left:=260, Top:=90, Width:=250, Height:=150)
    co.Chart.SetSourceData Source:=ws.Range("A1:B13")
    co.Chart.ChartType = xlLine
End Sub

The macro: line up every chart in the workbook

Dragging charts until they are the same size is a job nobody should do twice. This walks every worksheet, gives each chart a predictable name, snaps it to the same width, height and left edge, stacks the charts down the sheet and prints an inventory.

Sub TidyAllCharts()
    Dim ws As Worksheet
    Dim co As ChartObject
    Dim ch As Chart
    Dim anchor As Range
    Dim n As Long
    Dim total As Long

    For Each ws In ThisWorkbook.Worksheets
        Set anchor = ws.Range("H2")
        n = 0

        For Each co In ws.ChartObjects
            n = n + 1
            total = total + 1
            Set ch = co.Chart

            co.Name = "cht" & Format(ws.Index, "00") & "_" & Format(n, "00")
            co.Left = anchor.Left
            co.Top = anchor.Top + (n - 1) * 280
            co.Width = 440
            co.Height = 260

            Debug.Print ws.Name & " | " & co.Name & _
                        " | type " & ch.ChartType & _
                        " | series " & ch.SeriesCollection.Count & _
                        " | has title " & ch.HasTitle
        Next co
    Next ws

    MsgBox total & " chart(s) aligned. Details are in the Immediate window, " & _
           "which you open with Ctrl+G in the VBA editor.", vbInformation
End Sub

Line by line

• For Each co In ws.ChartObjects — ChartObjects is a worksheet collection. Charts on their own full-page tab are not in it: those sit in ThisWorkbook.Charts, and this macro leaves them alone.
• Set ch = co.Chart is the single line that crosses from the frame to the picture. Everything above it is geometry, everything below it is content.
• co.Name = "cht" & ... gives every frame a predictable name, so later code can say ws.ChartObjects("cht01_01") instead of trusting an index that shifts as soon as somebody deletes a chart. If code in your workbook already refers to charts by name, comment this line out first.
• co.Left = anchor.Left and co.Top = anchor.Top position the frame against cell H2 rather than at a guessed number. Reading geometry off a cell is the trick worth stealing here.
• + (n - 1) * 280 stacks the charts downwards, leaving 20 points of air between two charts 260 points high.
• co.Width and co.Height are in points, not pixels — 72 to the inch, identical on every screen. Sizing in code is the only way to make a report look the same on two machines.
• ch.ChartType, ch.SeriesCollection.Count and ch.HasTitle are all read from the Chart. Ask co for any of them and you get error 438.
• Debug.Print writes to the Immediate window; the MsgBox reports only the count, because a message box holding forty charts helps nobody.

The trap: ActiveChart is usually Nothing

Start the macro recorder, click a chart, change its title, and the recording hands you ActiveChart.ChartTitle.Text = "Revenue". It works while the chart is selected. Assign the very same macro to a button and it stops at run-time error 91, "Object variable or With block variable not set" — clicking the button deselected the chart, so ActiveChart is Nothing. Same code, same workbook, and nothing on screen explains the difference.

' Recorded: depends on whatever happens to be selected
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Revenue"

' Written: depends on nothing at all
Dim ch As Chart

Set ch = ThisWorkbook.Worksheets(1).ChartObjects(1).Chart
ch.HasTitle = True
ch.ChartTitle.Text = "Revenue"

Select and Activate are the recorder's way of pointing at things, because pointing is all it can see you do. A variable is better in every way: set the object once, use it as often as you like, and the macro stops caring what the user clicked last — and runs faster, because Excel is not redrawing a selection each time.

How this course goes on

This lesson takes stock of charts that already exist; lesson 2 creates them from nothing, comparing the old ChartObjects.Add with the newer Shapes.AddChart2. Lesson 3 feeds a chart with SetSourceData, including the PlotBy setting that decides whether twelve months become twelve series or one — the difference between a readable chart and an absurd one. After that come chart types, titles, axes and the legend, formatting individual series, and exporting a finished chart as an image file.