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.

Connect XY Series with Arrows

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
 
  

 

Peltier Tech Charts for Excel

Comments

  1. Matthew Pfluger says:

    Jon, great macro! Thanks.

  2. Great,
    that’s actually the same chart I had to draw yesterday, hum…, with manual edited arrows.
    Thanks Jon.

  3. This is what I was looking for. But this is limited to two series. Would it be possible to have all series linked instead of just two and to keep the arrow for each series instead of having it removed?

  4. Practical Oceanography says:

    Thank you for “Connect Two XY Series with Arrows.”
    It will allow me to create in Excel a stick plot of current direction and magnitude
    For example, “How to Read a Stick Plot”
    http://gyre.umeoce.maine.edu/gomoos/stick.html

  5. Is it possible to connect with this arrow with more than two series? For example five series? ten series?

  6. You can add as many series as you want. You need to compile as many nodes as you need, not just two per line segment as in my example.

    Alternatively, you could add a new XY series for each arrow, using the X and Y values that would have been used to computer the node coordinates for the lines.

Trackbacks

  1. […] Hello What Excel version are you using? Anyway, the page below may prove useful; let me know if you need further help. Connect Two XY Series with Arrows (VBA) | Peltier Tech Blog | Excel Charts […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0