Help with VBA for simple scatter chart

[Linus_99]

Hi,

Wonder if someone can help with code for the following:

Using Excel 2007, I want to plot 2 points on a scatter chart (x,y for BEFORE and x,y for AFTER). Then to join the points with a line (so far OK for a standard scatter chart), however the line needs to be an arrow with the arrowhead pointing to the AFTER data point.

This needs to be generated by VBA, since it's part of another reporting routine.

Any help appreciated. Thanks.

 

[Andy Pope]

Assuming your xy data is in A1:B3, series name in B1. Before X in A2, Before Y in B2 and After values in row 3.

Sub Macro1()
'
    Dim objSeries As Series
    Dim objFmt As LineFormat
'
    Range("A1:B3").Select
    With ActiveSheet.Shapes.AddChart.Chart
        .SetSourceData Source:=Range("'Sheet1'!$A$1:$B$3")
        .ChartType = xlColumnClustered
        .ChartType = xlXYScatterLinesNoMarkers
        Set objSeries = .SeriesCollection(1)
        Set objFmt = objSeries.Format.Line
        With objFmt
            .EndArrowheadWidth = msoArrowheadWidthMedium
            .EndArrowheadLength = msoArrowheadLong
            .EndArrowheadStyle = msoArrowheadTriangle
        End With
    End With
End Sub