Creating A Connection Between Series

A reader recently wrote in asking me how to draw a shape between two series on a line chart. The shape should connect two consecutive points on the chart so that what you see is a trapezoidal shape between the two lines. Of course, the big thing is to gain an understand of how charts work. The discussion starts on page 314 of VBA for Dummies. After you know the basics of chart creation, you can start with a worksheet and chart that looks like this:

ChartDrawing01

These values are contrived, but the technique will work with any chart you might want to create. The trapezoid will appear between the second and third points of the two lines. Here is the code you’ll need to perform the task.

Sub ModifyChart()
    ' Obtain access to the chart.
    Dim TestChart As Chart
    Set TestChart = Sheet1.ChartObjects(1).Chart
     
    ' Obtain access to each of the series.
    Dim Series1 As Series
    Set Series1 = TestChart.SeriesCollection(1)
    Dim Series2 As Series
    Set Series2 = TestChart.SeriesCollection(2)
     
    ' Obtain the position of the second and third points
    ' for each series.
    Dim Point1 As Point
    Set Point1 = Series1.Points(2)
    Dim Point2 As Point
    Set Point2 = Series1.Points(3)
    Dim Point3 As Point
    Set Point3 = Series2.Points(2)
    Dim Point4 As Point
    Set Point4 = Series2.Points(3)
     
    ' Create a drawing object using the second and
    ' third points of each series.
    Dim MyDrawing As Shape
     
    ' Add this shape to the chart.
    Set MyDrawing = TestChart.Shapes.AddShape(msoShapeTrapezoid, 15, 20, 25, 30)
     
    ' Modify the position of the shape.
    MyDrawing.Nodes.SetPosition 1, Point1.Left, Point1.Top
    MyDrawing.Nodes.SetPosition 2, Point2.Left, Point2.Top
    MyDrawing.Nodes.SetPosition 3, Point4.Left, Point4.Top
    MyDrawing.Nodes.SetPosition 4, Point3.Left, Point3.Top
    MyDrawing.Nodes.SetPosition 5, Point1.Left, Point1.Top
     
    ' Modify the shape color.
    MyDrawing.Fill.ForeColor.RGB = RGB(0, 255, 0)
    MyDrawing.Line.ForeColor.RGB = RGB(0, 128, 0)
End Sub

The code begins by gaining access to the chart, which already appears on the worksheet. Perhaps someone drew it there and is relying on your macro to show the correlation between the two lines. TestChart provides access to the only chart on the worksheet. The Sheet1.ChartObjects index always begins at 1 and counts up.

This chart has two seriesone for each of the lines. The next step is to gain access to those series as Series1 and Series2. In short, we’re drilling down into the chart components to obtain specifics about those lines so we can draw something between them.

The chart draws the two lines according to a list of points stored in the series that are based on the individual data values you provide and the order of that point in the list. We want to use the second and third points to draw the trapezoid, so you need to access the Points property for each series and supply and index of 2 and 3. You end up with four Point variables that describe the location of the points on the line.

It’s important to create the shape from the chart. Yes, you could create it completely manually, but using the chart saves you time. So, this example creates a Shape, MyDrawing, using the TestChart.Shapes.AddShape() function. The points you supply when creating the shape are unimportant because you’ll change them later anyway.

Every closed shape consists of one more node than the drawing requires. If you’re drawing a four-sided shape, as we are here, then you need five nodes to define it. That’s what the next step does. It redefines the position of the shape based on five nodes, using the first position to define the fifth nodesa really important concept to keep in mind if you want the shape to be solid.

Finally, the example changes the inside color of the resulting shape to a bright green and the line that defines it to a darker green. You’ll see these results from the example:

ChartDrawing02

As you can see, the shape precisely follows the two lines as it should. You can perform other drawing tricks like this one using the technique I’ve provided here. Let me know if you have any questions about it at John@JohnMuellerBooks.com.

 

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.