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:
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 SubThe 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 series—one 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 nodes—a 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:
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.