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).
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 at[email protected]. I also want to express my thanks to the readers who wrote in about this example.