I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!


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.

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) _
    ' 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 & _
        content = content &amp; _
            .cell("banner", "value").toString
        content = content &amp; _
            .cell("body", "value").toString
        With .cell("htmlname", "value")
            openNewHtml .toString, content
            If Not OpenUrl(.toString) Then
                MsgBox ("could not open " &amp; .toString &amp; " 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)
        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, _
                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
        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.