Connect Two XY Series with Arrows (VBA)

In Connect Two XY Series I showed how to use an extra XY series to connect two existing XY series in a chart. This shows how points in the first series may have changed to produce the data in the second series. A reader asked in a comment how to use arrows to more visually show the direction of change.

In Excel 2007 drawing these arrows is easy, because one of the formatting changes allows the lines in a chart series to be formatted as arrows. When used judiciously, that is rarely and only for truly informative purposes, the features I and others have derided as “eye candy” can actually be used for good, not just for evil.

In Excel 2003 and earlier, getting these arrows is not as easy, but I have provided a means to do so, and in fact the chart above was produced in Excel 2003.

For users of Excel 2003 and earlier, there are two options for getting arrows to connect the XY points. One option involves constructing complicated sets of data points for the connecting lines, which when connected take on the appearance of data points. This is tedious, especially given unequal X and Y scales and orientation of the line segment. If the chart changes in any way, it is likely that the extra data point will have to be recomputed.

The second option involves drawing autoshapes, line segments with arrowheads, from one point to the other. These look nice, but they are tricky to align with the data points, and if the chart changes, they will have to be realigned. This is a tedious process, but I have covered precision drawing of shapes on charts in Line and Fill Effects in Excel Charts Using VBA on my web site. I have made minor modifications to one of the procedures in that tutorial to allow easy drawing and redrawing of the arrows connecting data points in two series.

To get connecting arrows, create the XY chart with two series. The arrowhead is on the end of the segment at series two, so make sure the series are plotted in the proper order. Then select the chart and run the following VBA procedure.

Note: The procedure has limited error checking. It checks that a chart has been selected, that there are two series, and that the two series have matching numbers of points. It does not check for either axis being potted in reverse order (high value to low), nor for any of the other hundred things that could go wrong.

Sub ConnectTwoXYSeries()
  Dim myCht As Chart
  Dim mySrs1 As Series
  Dim mySrs2 As Series
  Dim Npts As Integer, Ipts As Integer
  Dim myBuilder As FreeformBuilder
  Dim myShape As Shape
  Dim Ishp As Long
  Dim Xnode1 As Double, Ynode1 As Double
  Dim Xnode2 As Double, Ynode2 As Double
  Dim Xmin As Double, Xmax As Double
  Dim Ymin As Double, Ymax As Double
  Dim Xleft As Double, Ytop As Double
  Dim Xwidth As Double, Yheight As Double

  ' a chart with two series must be selected
  If ActiveChart Is Nothing Then
    GoTo ExitSub
  End If
  If ActiveChart.SeriesCollection.Count < 2 Then
    GoTo ExitSub
  End If

  Set myCht = ActiveChart
  Set mySrs1 = myCht.SeriesCollection(1)
  Set mySrs2 = myCht.SeriesCollection(2)
  Npts = mySrs1.Points.Count

  ' two series must have matching numbers of points
  If mySrs2.Points.Count <> Npts Then
    GoTo ExitSub
  End If

  ' remove any old connecting arrows
  For Ishp = myCht.Shapes.Count To 1 Step -1
    If Left(myCht.Shapes(Ishp).Name, 12) = "ArrowSegment" Then
      myCht.Shapes(Ishp).Delete
    End If
  Next

  Xleft = myCht.PlotArea.InsideLeft
  Xwidth = myCht.PlotArea.InsideWidth
  Ytop = myCht.PlotArea.InsideTop
  Yheight = myCht.PlotArea.InsideHeight
  Xmin = myCht.Axes(1).MinimumScale
  Xmax = myCht.Axes(1).MaximumScale
  Ymin = myCht.Axes(2).MinimumScale
  Ymax = myCht.Axes(2).MaximumScale

  For Ipts = 1 To Npts
    ' first point
    Xnode1 = Xleft + (mySrs1.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode1 = Ytop + (Ymax - mySrs1.Values(Ipts)) * Yheight / (Ymax - Ymin)

    ' second point
    Xnode2 = Xleft + (mySrs2.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode2 = Ytop + (Ymax - mySrs2.Values(Ipts)) * Yheight / (Ymax - Ymin)

    ' draw connecting line
    Set myShape = myCht.Shapes.AddLine(Xnode1, Ynode1, Xnode2, Ynode2)

    ' name and format shape as arrowhead
    With myShape
      .Name = "ArrowSegment" & CStr(Ipts)
      With .Line
        ' USE YOUR FAVORITE FORMATS HERE
        .ForeColor.SchemeColor = 12  ' blue
        .EndArrowheadLength = msoArrowheadLong
        .EndArrowheadWidth = msoArrowheadWidthMedium
        .EndArrowheadStyle = msoArrowheadTriangle
      End With
    End With

  Next

ExitSub:
 
End Sub