In Combining Chart Types, Adding a Second Axis, Katherine Fifer of the Excel Team Blog describes in detail how to create the chart using Excel 2007.

It's been my experience that understanding the embedded chart object model can be difficult. Understanding how to declare variables based on that model can be even more difficult. Below is a guide that I refer to when attempting to isolate selected parts of the embedded chart object model. The guide uses a simple embedded line chart as an example. Each example can be put in a standard code module.

To name an embedded chart, first activate the chart (click on it once) and then run the following:

Sub NEC()
    ActiveChart.Parent.Name = "Cht1"
End Sub

The example below declares the variable called "Cht" as a ChartObject. To select an embedded chart:

Sub SEC()
    Dim ChtObj As ChartObject
    Set ChtObj = ActiveSheet.ChartObjects("Cht1")
    ChtObj.Select
End Sub

The following three examples isolate the first series for an embedded line chart:

Example 1 - declares the variable called "Cht" as a ChartObject. To select the source values for series one in an embedded line chart:

Sub SeriesEx1()
    Dim ChtObt As ChartObject
    Set ChtObj = ActiveSheet.ChartObjects("Cht1")
    ChtObj.Chart.SeriesCollection(1).Values = Range("A1:A5")
End Sub

Example 2 - declares the variable called "Cht" as a Chart. To select the source values for series one in an embedded line chart:

Sub SeriesEx2()
    Dim Cht As Chart
    Set Cht = ActiveSheet.ChartObjects("Cht1").Chart
    Cht.SeriesCollection(1).Values = Range("A1:A5")
End Sub

Example 3 - declares the variable called "Srs" as a SeriesCollection. To select the source values for series one in an embedded line chart:

Sub SeriesEx3()
    Dim Srs As SeriesCollection
    Set Srs = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection
    Srs(1).Values = Range("A1:A5")
End Sub

The three examples above show that the object model is built in a hierarchy, or levels, with ChartObject at the highest level i.e. ChartObject -> Chart -> SeriesCollection -> an so on.

The next two examples attempt to isolate the points for the first series for an embedded line chart. However, it's confusing because the first example refers to the series object while the second refers to the points object. When the macros are run, their results will appear in the immediate window of the visual basic editor.

Example 1 - declares the variable called "Srs" as a series object. To capture the value for the first point in series one in an embedded line chart (refers to the series object):

Sub PointsEx1()
    Dim Srs As Series
    Set Srs = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection(1)
    Debug.Print Srs.Values(1)
End Sub

Example 2 - declares the variable called "Pts" as a points object. To count the number of points in series one for an embedded line chart (refers to the points object):

Sub PointsEx2()
    Dim Pts As Points
    Set Pts = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection(1).Points
    Debug.Print Pts.Count
End Sub

Although not the same, the modeling for many of the other embedded chart types (column, bar, etc.) works very similar to the above.

A post sometime ago to the Microsoft Excel Charting Discussion Group asks . . . how can I automatically flag duplicate values on an XY scatter chart with a different colored point? My thought was to write a procedure that identified the duplicates via a helper column in the worksheet, accessed the chart, and then recolored the duplicate points. Assuming an XY scatter chart based on the values in the first and second columns, I added a third helper column. The helper column concatenates the X and Y values.

I renamed the chart "Cht1". Assuming a single X and Y series, the code below flags the duplicates. In the example, the marker size for each duplicated point is increased to 10 and the color of the marker is switched from blue to green.

Sub FlayXYDups()

    On Error Resume Next
    Dim Rng As Range
    Dim Cell As Range
    Dim Cnt As Integer
    Dim UniqueValues As New Collection
    Dim ErrColor As Integer
    Dim Cht As ChartObject

    Set Cht = ActiveSheet.ChartObjects("Cht1")

    ErrColor = 10

    Cnt = 0

    For Each Rng In Range("D3:D20")
        Cnt = Cnt + 1
        UniqueValues.Add Rng.Value, CStr(Rng.Value)
        If Err.Number = 457 Then
            With Cht.Chart.SeriesCollection(1).Points(Cnt)
                .MarkerSize = 10
                .MarkerForegroundColorIndex = ErrColor
                .MarkerBackgroundColorIndex = ErrColor
            End With
        Rng.Interior.ColorIndex = ErrColor
        End If
        Err.Number = 0
    Next Rng

End Sub

The chart showing duplicated values looks like this:

The procedure also flags the duplicates on the spreadsheet.

A recent post to the Microsoft Excel Charting Discussion Group asks . . . I have a series of embedded charts in a workbook. Some contain data, some do not. How can I automatically print only those charts with data? The following macro loops through each embedded chart in the workbook. For each embedded chart, it loops through each series recording the point values for those series. After looping through all of the series, if the total point values are not equal to zero, the chart prints. If the total point values are equal to zero, the macro proceeds to the next chart without printing.

Sub PrintChartsWithValues()

    Application.ScreenUpdating = False

    Dim hSheet As Worksheet
    Dim Ws As Worksheet
    Dim Cht As ChartObject
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long
    Dim iPt As Long

    Set hSheet = ActiveSheet

    For Each Ws In Worksheets
        For Each Cht In Ws.ChartObjects
            Y = 0
            For Each Srs In Cht.Chart.SeriesCollection
                With Srs
                    nPts = .Points.Count
                    For iPt = 1 To nPts
                        X = Srs.Values(iPt)
                        Y = X + Y
                    Next
                End With
            Next Srs
            If Y <> 0 Then Cht.Chart.PrintOut Copies:=1
        Next Cht
    Next Ws

    hSheet.Select
    Range("A1").Select

End Sub

A recent post to the Microsoft Excel Charting Discussion Group asks . . . how can I change the point size of all XY chart values that are duplicates? Assuming an embedded XY chart, I responded with the procedure below. Activate (click on) the embedded chart and run the macro:

Sub ShowXYDuplicatePoints()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long, iPt As Long
    Dim Test As Variant
    Dim UniqueValues As New Collection

    Set Cht = ActiveChart

    For Each Srs In Cht.SeriesCollection
        With Srs
            nPts = .Points.Count
            For iPt = 1 To nPts
                Test = Srs.XValues(iPt) & Srs.Values(iPt)
                UniqueValues.Add Acct, CStr(Test)
                On Error GoTo ErrHandler:
                If iPt + 1 > nPts Then
                    ActiveChart.Deselect
                    Exit Sub
                End If
                Label1:
            Next
        End With
    Next Srs

ErrHandler:
    Srs.Points(iPt).MarkerSize = 10
    Srs.Points(iPt).MarkerBackgroundColorIndex = 3
    Srs.Points(iPt).MarkerForegroundColorIndex = 3
    If iPt + 1 > nPts Then
        ActiveChart.Deselect
        Exit Sub
    End If
    Resume Label1:

End Sub

The procedure works by looping through all of the point values within each series in the chart. As the macro loops, each point value is loaded into the UniqueValues array. Because the UniqueValues array only accepts unique values, any duplicates are automatically kicked out and run through the error handling procedure.

The formatting can be reset with the following procedure.

Sub ResetXY()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long, iPt As Long
    Set Cht = ActiveChart

    For Each Srs In Cht.SeriesCollection
        With Srs
            nPts = .Points.Count
            For iPt = 1 To nPts
                Srs.Points(iPt).MarkerSize = 5
                Srs.Points(iPt).MarkerBackgroundColorIndex = 11
                Srs.Points(iPt).MarkerForegroundColorIndex = 11
            Next
        End With
    Next Srs

    ActiveChart.Deselect

End Sub

A recent post to the Microsoft Excel Charting Discussion Group asks . . . how can I change the pattern of a column based on that column's value. Assuming an embedded chart, I responded with the following procedure:

Sub ChangePatterns()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pts As Points

    Set Cht = ActiveChart
    Set Srs = Cht.SeriesCollection(1)
    Set Pts = Srs.Points

    Cnt = 1

    For Each Pt In Srs.Values

        Srs.Points(Cnt).Select

        'Sales greater than 10000
        If Pt > 10000 Then

            With Selection
                .Fill.Visible = True
                .Fill.Patterned Pattern:=msoPatternWideUpwardDiagonal
                .Fill.ForeColor.SchemeColor = 42
                .Fill.BackColor.SchemeColor = 34
            End With

        'Sales less than or equal to 10000
        ElseIf Pt <= 10000 Then

            With Selection
                .Fill.Visible = True
                .Fill.Patterned Pattern:=msoPatternLightHorizontal
                .Fill.ForeColor.SchemeColor = 43
                .Fill.BackColor.SchemeColor = 22
            End With

        End If

        Cnt = Cnt + 1

    Next Pt

    ActiveChart.Deselect

End Sub

-- Just thought I would document it for my own reference. Is there a better way to write this?