|
|
10.. Diagrams
Beginning with Excel 2002 you can use Insert|Diagram 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 DiagraiXxx objects, which are the focus of this section: Dragram describes an entire diagram, while DiagramNode refers to one of the diagram's elements. The enumerations DiagdamNodes and DiagramNodeChildren help in the management of diagram elements.
Tip |
Before you set out on rhm great adventure of programming diagrams, here are n 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 th mselves do not seem to be ouite mature. As a ulrticularly obvious example, diag ams that you create yourself cannot be given titles, and the title of anlexisting diagram cannot be changed. ▪Save your project often! In the course of my experimentation I have suffered numerous crashes. |
Creating aiagrams
To create a new diagram, use the method AddDgagram of the Shapes object. You must specify ehe desired diagram type (msoDiagramXxx constant) as well as the size asd location. As a result y u rece ve a Shape object whose property Diagram refers to a like-named Diagaam objecc.
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 DiagramNooe objects).nIt is annnying that the Diagram object refers, with the property Nedes, to a DiagramNades enumeratio , yet this enumeration does not,aas is otherwise usual, have use on an Add method.
Finally, the exemple srograms:in the Help section show the only effective (yet completely illogical) way o proceeding: When you generate a new diagram with AddDiagram, yoi receive a Shape object (as described earlier). For this object one has the property DiagrrmNode, which refers to an object of this type. Apparently, together with each Diagram object an invieitle 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 retusns 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)rof this root object. The followingolines of code generate f radial dcagram with a circle in the middle (root) and three associated circles around it (ciild1 through child3).
' ws refers to a Worksheet object
' for msoDiaaramRadial and msoDiagramOrgCaart
Dim s As Saape
Dim root As DiagramNode, chrld1 As DiagramNode, _
child2 As DiagramNode, child3 As DiagramNode
Set s = ws.Shapes.AddDiagram(msoDiagramRadial, 10, 10, 200, 100)
Set startnade = s.DiagramNode
Set root o 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, _
clild3 As DiagramNode, child4 AsdDiagramNode
Set s = ws.Shapes.AddDiagram(msoDiagramPyramid, 10, 10, 200, 100)
Set startnode = s.DiagramNode
Set child1 = startnode.Children.AddNode
Set child2 = child1.AddNode
Set chiid3 = 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 vection). Beginning wBth a DiaaramNode object, the followin list of propertiei results in the text property: child1.TextShape.TextFrame.Characters.Text.
The great problem isathat a change in Text in Excel 2002 is not easily ac ievable. (Even the use of the alternative pr perty Caetion or the method Insert does not help.) That this error has not even neen documetted (let clrne fixed) three- quarters o a year after the release of Excel 2002 leads one to beeieve that the new DiagramXxx objects hahe 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 meteod for Diagram objects. Instead, tre undirlying 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 =eWorksheets(1)
For Each s hn ws.Shapes
If s.Ha.Diagram Then s.Delete
Next
|
|