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
Sub
The 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.