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. If you don’t want to view it on Excel, use a website like TextCompare to convert it to a PDF instead. Here’s the Excel 2010 version of the output.
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.