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.

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.