10.6 Diagrams

Beginning with Excel 2002 you can use InsertDiagram to insert an organization chart or one of five additional types of diagram into an Excel worksheet (cycle diagram, radial diagram, pyramid diagram, Venn diagram, and target diagram). These diagrams appear in a basic format and can be enhanced with your own text, formatting, and additional subobjects. In working with diagrams the Organization Chart toolbars are helpful (only for organization charts ), as well as the Diagram toolbar (for the other five diagram types).

To create or edit a diagram in VBA code, you must use the new DiagramXxx objects, which are the focus of this section: Diagram describes an entire diagram, while DiagramNode refers to one of the diagram's elements. The enumerations DiagramNodes and DiagramNodeChildren help in the management of diagram elements.

Tip

Before you set out on the great adventure of programming diagrams, here are a few tips:

Macro recording does not work either in creating or in editing a diagram. This makes the creation of Diagram objects a labor- intensive process.
The Diagram objects themselves do not seem to be quite mature. As a particularly obvious example, diagrams that you create yourself cannot be given titles, and the title of an existing diagram cannot be changed.
Save your project often! In the course of my experimentation I have suffered numerous crashes.

Creating Diagrams

To create a new diagram, use the method AddDiagram of the Shapes object. You must specify the desired diagram type ( msoDiagramXxx constant) as well as the size and location. As a result you receive a Shape object whose property Diagram refers to a like-named Diagram object.

 Dim s As Shape Dim d As Diagram Dim ws As Worksheet Set ws = Worksheets(1) Set s = ws.Shapes.AddDiagram(msoDiagramRadial, 10, 10, 200, 100) Set d = s.Diagram

Inserting Diagram Elements

A new diagram, regardless of type, is originally empty. The next step consists in filling the diagram with elements (with DiagramNode objects). It is annoying that the Diagram object refers, with the property Nodes , to a DiagramNodes enumeration, yet this enumeration does not, as is otherwise usual, have use of an Add method.

Finally, the example programs in the Help section show the only effective (yet completely illogical) way of proceeding: When you generate a new diagram with AddDiagram , you receive a Shape object (as described earlier). For this object one has the property DiagramNode , which refers to an object of this type. Apparently, together with each Diagram object an invisible and in some sense virtual diagramNode object is generated that serves as the starting point for the addition of additional elements. (But note that DiagramNodes.Count returns 0.)

How one proceeds next depends on the type of diagram: In the case of organization charts a root object must be created. All further objects are added as subobjects ( Children ) of this root object. The following lines of code generate a radial diagram with a circle in the middle ( root ) and three associated circles around it ( child1 through child3 ).

 ' ws refers to a Worksheet object ' for msoDiagramRadial and msoDiagramOrgChart Dim s As Shape Dim root As DiagramNode, child1 As DiagramNode, _     child2 As DiagramNode, child3 As DiagramNode Set s = ws.Shapes.AddDiagram(msoDiagramRadial, 10, 10, 200, 100) Set startnode = s.DiagramNode Set root = startnode.Children.AddNode Set child1 = root.Children.AddNode Set child2 = root.Children.AddNode Set child3 = root.Children.AddNode

With the other diagram types, however, all diagram objects are at the same level. The next example shows how a four-part pyramid diagram is generated:

 ' msoDiagramPyramid, msoDiagramCycle, msoDiagramTarget, msoDiagramVenn Dim s As Shape Dim child1 As DiagramNode, child2 As DiagramNode, _     child3 As DiagramNode, child4 As DiagramNode Set s = ws.Shapes.AddDiagram(msoDiagramPyramid, 10, 10, 200, 100) Set startnode = s.DiagramNode Set child1 = startnode.Children.AddNode Set child2 = child1.AddNode Set child3 = child1.AddNode Set child4 = child1.AddNode

Providing Labels for Diagram Elements

The text of a diagram element is managed via a TextFrame object (see the previous section). Beginning with a DiagramNode object, the following list of properties results in the text property: child1.TextShape.TextFrame.Characters.Text .

The great problem is that a change in Text in Excel 2002 is not easily achievable. (Even the use of the alternative property Caption or the method Insert does not help.) That this error has not even been documented (let alone fixed) three- quarters of a year after the release of Excel 2002 leads one to believe that the new DiagramXxx objects have not caught the imagination of programmers.

Without the possibility of attaching labels to diagram elements, further programming makes no sense at all. One may hope that the many inconsistencies plaguing the Diagram objects will be corrected in future versions of Excel.

Deleting Diagrams

There is no Delete method for Diagram objects. Instead, the underlying Shape object must be deleted. With the property HasShape you can test whether the Shape object is being used to represent a diagram or for some other purpose. The following loop deletes the diagram in the first worksheet of a file:

 Dim s As Shape Dim ws As Worksheet Set ws = Worksheets(1) For Each s In ws.Shapes   If s.HasDiagram Then s.Delete Next