It’s important to realize that VBA usually provides more than one way to accomplish any given task. Each technique offers benefits and comes with costs. In the Creating A Connection Between Series post, you saw one way to use shapes to work with a series in a chart. This post builds on the information starting on page 314 of VBA for Dummies. Interestingly enough, there is another way to obtain the same output, as shown here:
Sub ModifyChart2() ' 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 and add this shape ' to the chart. With TestChart.Shapes.BuildFreeform(msoEditingCorner, Point1.Left, Point1.Top) .AddNodes msoSegmentLine, msoEditingCorner, Point2.Left, Point2.Top .AddNodes msoSegmentLine, msoEditingCorner, Point4.Left, Point4.Top .AddNodes msoSegmentLine, msoEditingCorner, Point3.Left, Point3.Top .AddNodes msoSegmentLine, msoEditingCorner, Point1.Left, Point1.Top .ConvertToShape End With ' Obtain access to the new shape. Dim MyDrawing As Shape Set MyDrawing = TestChart.Shapes(1) ' Modify the shape color. MyDrawing.Fill.ForeColor.RGB = RGB(255, 0, 255) MyDrawing.Line.ForeColor.RGB = RGB(128, 0, 128)End SubThe disadvantages of this method are that you must spend more time creating the initial shape and the code is less readable. Using a predefined shape means that you simply move the points to meet your needs and you’re finished. However, the advantages of this method are:
- Create a shape with any number of segments
- Use curved segments instead of straight lines
- Easy control how the corners look
In short, even though this method requires a lot more effort initially, it offers significantly more flexibility in exchange for that effort.
The code begins much like the previous example—obtaining data points from two series and using those points to create a connection. The difference in this example is the call to BuildFreeform(), which allows you to create a shape of any complexity by relying on the AddNodes() method. Simply add any number of nodes to create the shape desired by tracing its outline.
The one thing that many developers forget is to include a call to ConvertToShape(). If you don’t make this call, you end up with an outline, not a shape. The inside won’t be filled in because Excel doesn’t see it as a shape—it’s simply a series of lines.
After you create the shape, you must gain access to it. Because this is the first and only shape on the chart, the index is easy—use a value of 1. If you create a number of shapes, then you must determine which index to use, which will always be the last shape entered if this is the newest shape in the list. Use the Count property to obtain this value. For this example, you’d use TestChart.Shapes.Count to obtain the value. When you run this example, you get precisely the same output as with the earlier example.
The point of this exercise is that you should always consider the possibility of an alternative solution to any given problem. Once you’ve come up with an alternative, consider the pros and cons of this new approach to determine which approach is best for your specific needs. It isn’t always easy to determine which approach will work best, so be willing to create some test cases to check the outcome of a specific approach for your requirement. Let me know if you have any questions at John@JohnMuellerBooks.com.