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 SubThis 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.