Programming Excel with VBA and .NET

Program Diagrams

Excel diagrams might seem very useful from a programming perspective; however, they have a serious limitation: you can’t get or set the text of diagram nodes from code. See http://support.microsoft.com/default.aspx?scid=kb;en-us;317293 for complete details.

This appears to be a bug in the 2002 and 2003 versions of Excel, so you can assume it will continue into the future. Interestingly, you can use macro recording to record your actions building a diagram in the Excel user interface; however, if you run the macro, you will see the error in Figure 18-11.

Figure 18-11. Programming diagrams is not well supported in Excel

Warning

Many of the code samples for the Diagram and DiagramNode objects in Help fail if you run them in Excel.

Microsoft suggests using the Diagram object in the Word or PowerPoint application as a workaround to this problem, and those applications do seem to work. However, I think it’s more reliable to use Excel’s autoshapes and connectors if you want to diagram data from a worksheet.

The following code draws a hierarchical diagram from items on the active worksheet. Items in the first column are top-level parents and items in subsequent columns are all related as shown in Figure 18-12:

' Module-level variable used to set Top property ' of subsequent shapes. Dim m_lastShape As Shape   Sub DrawDiagram( ) Dim ws1 As Worksheet, ws2 As Worksheet, cel ...