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?

Microsoft's macrofun.hlp file documents the XLM function GET.CHART.ITEM, which is useful for finding the vertical and horizontal positions for selected chart items. This function is further explained in Professional Excel Development.

From Microsoft's macrofun.hlp, below is the documentation for GET.CHART.ITEM:

GET.CHART.ITEM returns the vertical or horizontal position of a point on a chart item. Use these position numbers with FORMAT.MOVE and FORMAT.SIZE to change the position and size of chart items. Position is measured in points; a point is 1/72nd of an inch.

Syntax

GET.CHART.ITEM(x_y_index, point_index, item_text)

X_Y_index is a number specifying which of the coordinates you want returned.

X_Y_index Coordinate returned:

• 1 - Horizontal coordinate.
• 2 - Vertical coordinate.

Point_index is a number specifying the point on the chart item. These indexes are described later. If point_index is omitted, it is assumed to be 1. If the specified item is a point, point_index must be 1. If the specified item is any line other than a data line, use the following values for point_index.

Point_index and Chart item position:

• 1 - Lower or left.
• 2 - Upper or right.

If the selected item is a legend, plot area, chart area, or an area in an area chart, use the following values for point_index.

Point_index and Chart item position:

• 1 - Upper left.
• 2 - Upper middle.
• 3 - Upper right.
• 4 - Right middle.
• 5 - Lower right.
• 6 - Lower middle.
• 7 - Lower left.
• 8 - Left middle.

If the selected item is an arrow in Microsoft Excel 4.0, use the following values for point_index. In Microsoft Excel 5.0, arrows are named lines, and the arrowhead position returned is equivalent to the end of a line where the arrowhead begins.

Point_index and Chart item position:

• 1 - Arrow shaft.
• 2 - Arrowhead.

If the selected item is a pie slice, use the following values for point_index.

Point_index and Chart item position:

• 1 - Outermost counterclockwise point.
• 2 - Outer center point.
• 3 - Outermost clockwise point.
• 4 - Midpoint of the most clockwise radius.
• 5 - Center point.
• 6 - Midpoint of the most counterclockwise radius.

Item_text is a selection code that specifies which item of a chart to select. See the chart form of SELECT for the item_text codes to use for each item of a chart.

If item_text is omitted, it is assumed to be the currently selected item.

If item_text is omitted and no item is selected, GET.CHART.ITEM returns the #VALUE! error value.

Remarks

If the specified item does not exist, or if a chart is not active when the function is carried out, the #VALUE! error value is returned.

Examples

The following macro formulas return the horizontal and vertical locations, respectively, of the top of the main-chart value axis:

GET.CHART.ITEM(1, 2, "Axis 1")

GET.CHART.ITEM(2, 2, "Axis 1")

You could then use FORMAT.MOVE to move a floating text item to the position returned by these two formulas.

In the previous post, I presented a macro that aligned embedded charts to a worksheet cell grid given certain criteria. The macro works OK if you're not concerned about the order in which any chart is placed on the worksheet. Andy Pope offered a more efficient version via comment. However, after more review I found that the macro would rearrange the original placement of one or more of the charts if one or more of the original charts were deleted and replaced. The root of the problem is that embedded charts are really shapes that are stored with an index number in the shapes collection. If a shape is deleted and a new shape created, the new shape is given the last index number in the collection. To place individual charts where you want, you need to somehow specify what index number of the shape you want to where.

The only way I could think of to get around this problem was to name each chart individually and the order the charts by name. Depending on how I wanted the charts to fall on the worksheet, I named each chart "Cht1", "Cht2", "Cht3", etc. I then modified Andy's macro to order the charts by the chart names:

Sub AlignCharts()

    Dim Cht As ChartObject
    Dim ChartsAcross As Integer
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer
    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("B2")
    ChartsAcross = 3
    ColumnsAcross = 4
    RowsDown = 10

    Set Rng2 = Rng1
    
    TotalCharts = 0
    
    For Each Cht In ActiveSheet.ChartObjects
        TotalCharts = TotalCharts + 1
    Next Cht
    
    For Cnt = 1 To TotalCharts
    
    Set Cht = ActiveSheet.ChartObjects("Cht" & Cnt)
        With Cht
            .Top = Rng1.Top
            .Left = Rng1.Left
            .Height = 94.5
            .Width = 144
        End With
        If Cnt Mod ChartsAcross = 0 Then
            Set Rng1 = Rng2.Offset(RowsDown, 0)
            Set Rng2 = Rng1
        Else
            Set Rng1 = Rng1.Offset(0, ColumnsAcross)
        End If
    
    Next

End Sub