Creating Freeform Shapes for Excel 2003

I had previously written a post for my VBA for Dummies readers entitled, “Creating Freeform Shapes“. Unfortunately, it appears that the example doesn’t work for Excel 2003 users. Microsoft sometimes changes things in VBA without telling anyone. I’m not sure whether that’s the case here or not, but none of my graphics examples would work with that older copy of Excel, which left me scratching my head for a while.

After a lot of research, trial and error, and input from faithful readers, I determined that the only solution is to use the BuildFreeform() function with a lot of complex equations. My code still didn’t work after quite a few trials and it should have. That’s when experimentation kicked in. When creating a freeform shape, you must define the initial shape, and then add nodes to it to add additional lines and curves. The VBA documentation stated that I could use the AddNodes() function with either msoEditingCorner or msoEditingAuto. It turns out that the documentation is in error—only msoEditingAuto works in Excel 2003 (this problem has been fixed in newer versions of Excel, so the documentation is now correct). So, without further ado, here is an example of creating a freeform shape for Excel 2003 readers (it also works in newer versions of Excel).

Sub ModifyChart4()
    ' Obtain access to the chart.
    Dim TestChart As Chart
    Set TestChart = Sheet1.ChartObjects(1).Chart
 
    ' Obtain the dimensions of the charting area.
    Dim XLeft As Double
    XLeft = TestChart.PlotArea.InsideLeft
    Dim XWidth As Double
    XWidth = TestChart.PlotArea.InsideWidth
    Dim YTop As Double
    YTop = TestChart.PlotArea.InsideTop
    Dim YHeight As Double
    YHeight = TestChart.PlotArea.InsideHeight
     
    ' 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)
     
    ' Determine the number of entries for each series.
    Dim S1Count As Long
    S1Count = Series1.Points.Count
    Dim S2Count As Long
    S2Count = Series2.Points.Count
     
    ' Obtain the series entry X-axis spacing in pixels.
    Dim S1XSpacing As Double
    S1XSpacing = (XWidth - XLeft) / S1Count
    Dim S2XSpacing As Double
    S2XSpacing = (XWidth - XLeft) / S2Count
     
    ' This offset isn't calculated right now, but it needs to
    ' be obtained from some source on the chart.
    XOffset = 14
     
    ' Define the X position of the second and third points for
    ' the first and second series in pixels.
    Dim S1X2 As Double
    S1X2 = S1XSpacing * 2 + XOffset
    Dim S1X3 As Double
    S1X3 = S1XSpacing * 3 + XOffset
    Dim S2X2 As Double
    S2X2 = S2XSpacing * 2 + XOffset
    Dim S2X3 As Double
    S2X3 = S2XSpacing * 3 + XOffset
     
    ' Determine the minimum and maximum values for each series and
    ' then create a range value.
    Dim YMin As Double
    YMin = TestChart.Axes(2).MinimumScale
    Dim YMax As Double
    YMax = TestChart.Axes(2).MaximumScale
    Dim YRange As Double
    YRange = YMax - YMin + 1
     
    ' Define the Y axis pixel length.
    Dim YPixels As Double
    YPixels = YHeight + YTop
     
    ' Define the position of the second and third nodes for each
    ' series within the Y axis.
    Dim S1Y2 As Double
    S1Y2 = YPixels - (YPixels * (Series1.Values(2) / YRange))
    Dim S1Y3 As Double
    S1Y3 = YPixels - (YPixels * (Series1.Values(3) / YRange))
    Dim S2Y2 As Double
    S2Y2 = YPixels - (YPixels * (Series2.Values(2) / YRange))
    Dim S2Y3 As Double
    S2Y3 = YPixels - (YPixels * (Series2.Values(3) / YRange))
     
    ' 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, S1X2, S1Y2)
        .AddNodes msoSegmentLine, msoEditingAuto, S1X3, S1Y3
        .AddNodes msoSegmentLine, msoEditingAuto, S2X3, S2Y3
        .AddNodes msoSegmentLine, msoEditingAuto, S2X2, S2Y2
        .AddNodes msoSegmentLine, msoEditingAuto, S1X2, S1Y2
        .ConvertToShape
    End With
     
    ' Obtain access to the new shape.
    Dim MyDrawing As Shape
    Set MyDrawing = TestChart.Shapes(TestChart.Shapes.Count)
     
    ' Modify the shape color.
    MyDrawing.Fill.ForeColor.RGB = RGB(128, 128, 255)
    MyDrawing.Line.ForeColor.RGB = RGB(64, 64, 128)
End Sub

This version doesn’t use any of the newer techniques for gaining access to the chart or creating the shape. It begins by figuring out the dimensions of the plotting area. Everything is based on knowing the location of the plotting area within the chart object.

The problem is figuring out where each of the nodes on that plot are. You don’t have access to them. So, you’re faced with a situation where you have to calculate the position of the individual nodes, which can be quite an undertaking. Computing the X-axis comes first. The nodes are evenly spaced across the X-axis, so you divide the number nodes by the plotting area length in pixels. That gives you the spacing between nodes. All you do to find the X location of a particular node is multiply that node’s number by the spacing. When I tried this is in the real world though, I found that there is an offset for the Y-axis legend, but that there wasn’t any way to obtain this value. Consequently, you see my trial and error value of 14 as the offset (if someone knows how to obtain this value, please let me know).

The Y-axis values are a little harder to find out. First, you need to obtain the full range of the Y-axis in pixels. Then, you need to work from the top down. The numbers you get for values on the chart are from the bottom up, though, so you need to convert between the two orientations. In addition, the node value doesn’t equate directly to a pixel count, so you must create a ratio between the largest value that the Y-axis supports and the value of the current node, and then multiply by the full pixel range to obtain the position of the current node in the Y-axis.

At this point, you know the X and Y values for each of the four nodes. You use the BuildFreeform() function call to create a FreeFormBuilder object. This object contains only the first node. To add more nodes, you rely on the AddNodes() function. As with any shape, you must close the shape before you call ConvertToShape() to convert it into a closed shape. Otherwise, you simply have a curved line.

The remainder of this example works much like the previous examples in this series. The code gains access to the new shape, colors the interior, and changes the line color. What you’ll see as output varies with the version of Excel you’re using. Here’s the Excel 2010 version of the output.

ChartDrawing0201

Please let me know if there are any additional problems with this example atJohn@JohnMuellerBooks.com. I also want to express my thanks to the readers who wrote in about this example.

Creating Freeform Shapes

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 exampleobtaining 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 shapeit’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 easyuse 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.

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.