Acknowledgement
d3.js is from Mike Bostok’s site. If you use any of this material, please be sure to maintain the acknowledgement for his work. I also found out how to create tree diagrams on Pavan Podila’s blog.
What are tree diagrams
These are useful for showing hierarchical relationships. Here’s an example and belowAnd for those with capability challenged browsers (IE mainly), here’s a screenshot. 
Data Structure
It’s rather straightforward to set up the data structure for this chart in excel. Here is some sample data – you’ll find this in the d3Tree tab of cDataSet.xlsm , downloadable from Project Downloads. All you need is
- a Label – this will be shown on the chart
- a Key – this is a unique key that identifies each row.
- a Parent Key – this refers to the unique key that is the parent of this row.
The depth and complexity of the tree is up to you – as long as you define the keys uniquely. 
To create a d3 Tree diagram, you simply need to construct a table as above. If your want to automate creating data in this format, take a look at an easy way to transform it in this post.
Tweaking the format
As in other projects on this site, the javaScript, css and html that does all this is stored as a series of parameter values in a parameter sheet- in this case the d3TreeParameters sheet. This looks like this 
Most of this you wont need to touch, but you can tweak the style (these are css style definitions) and header parameters to affect the layout and content of the final web page. The options tweak the tree layout, and I will add some more at some point. It is also possible to change the operation of the chart if you are comfortable with d3.js – you can find this in the code parameter. The generated filename is set with the htmlName parameter.
Warning
Some directories on your machine may be readonly to you – for example if you have downloaded this and run it from the download directory on windows 8 you may not be able to create a file in that directory because of local permission settings. You should set this to a directory you have permission to create files in.
This is a single file with no dependencies (except to the d3.js library) and you can put it on a web site or mail it to someone as you wish. Note that IE8 and below may not work with d3.js. Chrome is best and Firefox works too.
The VBA Code
All code is implemented in the cDataSet.xlsm download. Here is the calling procedure to generate and execute the chart code. The cJobject class has been extended to be able to transform the hierarchical data with parent/child keys into jSon format suitable for consumption by d3.js
Public Sub testD3Tree()
makeD3Tree "d3treeparameters", "d3tree"
End Sub
Private Sub makeD3Tree(params As String, data As String)
Dim ds As New cDataSet, cj As New cJobject, dsOptions As New cDataSet
Dim cs As New cSankey, dsParam As New cDataSet, _
js As String, content As String
' generate a d3.js Tree chart from excel data
' get parameters
dsParam.populateData wholeSheet(params), , , True, "item"
dsOptions.populateData wholeSheet(params), , , True, "options"
' get data values as json
js = cj _
.init(Nothing) _
.makeD3Tree(ds.populateData(wholeSheet(data), , , , , , True), _
dsOptions) _
.serialize
' construct the executable file and call the browser
content = ""
With dsParam
content = content & _
.cell("titles", "value").toString
content = content & _
.cell("styles", "value").toString
content = content & _
.cell("code", "value").toString
content = content & _
"</pre>
"
content = content & _
.cell("banner", "value").toString
content = content & _
.cell("body", "value").toString
With .cell("htmlname", "value")
openNewHtml .toString, content
If Not OpenUrl(.toString) Then
MsgBox ("could not open " & .toString & " using default browser")
End If
End With
End With
End Sub
Here are the additional cJobject methods
Public Function addD3TreeItem(ds As cDataSet, label As String, key As String, parentkey As String) As cJobject
Dim cj As cJobject, dr As cDataRow
' does parent key exist?
Set cj = Find(parentkey)
If (cj Is Nothing) Then
Set dr = findD3Parent(ds, parentkey)
If Not dr Is Nothing Then
Set cj = addD3TreeItem(ds, label, parentkey, cleanDot(dr.cell("Parent key").toString))
End If
End If
If cj Is Nothing Then
MsgBox ("could not find " & key & " " & parentkey)
Else
With cj.add(key)
.add "label", label
End With
End If
Set addD3TreeItem = cj
End Function
Private Function findD3Parent(ds As cDataSet, parentkey) As cDataRow
Dim dr As cDataRow
For Each dr In ds.rows
If cleanDot(dr.cell("key").toString) = parentkey Then
Set findD3Parent = dr
Exit Function
End If
Next dr
End Function
Private Function cleanDot(s As String) As String
'. has special meaning for cJobject so if present in key, then remove
cleanDot = makekey(Replace(s, ".", "_ _"))
End Function
Public Function makeD3Tree(ds As cDataSet, dsOptions As cDataSet) As cJobject
' this one will take a list of Name/Parents and make a structured cJobject out of it
Dim dr As cDataRow, cj As cJobject, parent As String, name As String, c3 As cJobject
Const container = "contents"
If Not ds.headingRow.validate(True, "Label", "Parent Key", "Key") Then Exit Function
Set cj = add("D3Root")
For Each dr In ds.rows
cj.addD3TreeItem ds, _
cleanDot(dr.cell("label").toString), _
cleanDot(dr.cell("key").toString), _
cleanDot(dr.cell("Parent key").toString)
Next dr
' now lets tweak that to a d3 format
Set c3 = New cJobject
With c3.init(Nothing)
' add an options branch
With .add("options")
For Each dr In dsOptions.rows
If dr.cell("value").toString <> vbNullString Then
.add dr.cell("options").toString, _
dr.cell("value").toString
End If
Next dr
End With
' add a branch for data
With .add("data")
.add "label", dsOptions.cell("root", "value").toString
.makeD3 cj.Children(1)
End With
End With
Set makeD3Tree = c3
End Function
Public Function makeD3(cj As cJobject) As cJobject
Dim cjc As cJobject
If cj.hasChildren Then
With add("children").addArray.add
For Each cjc In cj.Children
.makeD3 cjc
Next cjc
End With
Else
add cj.key, cj.Value
End If
Set makeD3 = Me
End Function
Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. You can find out more about other d3.js visualizations from Excel here.