Create Tree Map Chart with Excel VBA

1. steps to Create a Tree Map Chart with VBA

To create a Tree Map Chart using VBA, we will:

• Prepare sample hierarchical data in an Excel worksheet.
• Insert a Tree Map Chart.
• Format the chart for better readability.

2. VBA Code to Create a Tree Map Chart

The following VBA code:

• Inserts sample data in an Excel worksheet.
• Creates a Tree Map Chart.
• Adjusts formatting.

Sub CreateTreeMapChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim treeMapChart As Chart
    Dim dataRange As Range
    ' Step 1: Set the worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("TreeMapData")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "TreeMapData"
    End If
    On Error GoTo 0
    ' Step 2: Add Sample Data for Tree Map
    ws.Cells.Clear
    ws.Range("A1:C1").Value = Array("Category", "Subcategory", "Value"
    ws.Range("A2:C10").Value = Array( _
        Array("Fruits", "Apples", 50), _
        Array("Fruits", "Bananas", 30), _
        Array("Fruits", "Oranges", 40), _
        Array("Vegetables", "Carrots", 20), _
        Array("Vegetables", "Potatoes", 35), _
        Array("Vegetables", "Tomatoes", 25), _
        Array("Dairy", "Milk", 60), _
        Array("Dairy", "Cheese", 45), _
        Array("Dairy", "Yogurt", 30)
    ' Step 3: Define the data range
    Set dataRange = ws.Range("A1:C10")
    ' Step 4: Create the Tree Map Chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300)
    Set treeMapChart = chartObj.Chart
    treeMapChart.SetSourceData Source:=dataRange
    treeMapChart.ChartType = xlTreemap
    ' Step 5: Format the Tree Map Chart
    With treeMapChart
        .HasTitle = True
        .ChartTitle.Text = "Tree Map Chart - Sales Data"
        .ChartTitle.Font.Size = 14
        .ChartTitle.Font.Bold = True
        .Legend.Position = xlBottom
    End With
    ' Step 6: Autofit columns
    ws.Columns("A:C").AutoFit
    ' Notify user
    MsgBox "Tree Map Chart created successfully!", vbInformation, "Success"
End Sub

Explanation of the Code

Step 1: Define the Worksheet

Set ws = ThisWorkbook.Sheets("TreeMapData")
If ws Is Nothing Then
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "TreeMapData"
End If

• The code checks if a worksheet named « TreeMapData » exists.
• If not, it creates a new worksheet and assigns it this name.

Step 2: Insert Sample Data

ws.Range("A1:C1").Value = Array("Category", "Subcategory", "Value")
ws.Range("A2:C10").Value = Array( _
    Array("Fruits", "Apples", 50), _
    Array("Fruits", "Bananas", 30), _
    Array("Fruits", "Oranges", 40), _
    Array("Vegetables", "Carrots", 20), _
    Array("Vegetables", "Potatoes", 35), _
    Array("Vegetables", "Tomatoes", 25), _
    Array("Dairy", "Milk", 60), _
    Array("Dairy", "Cheese", 45), _
    Array("Dairy", "Yogurt", 30))

• The headers « Category », « Subcategory », and « Value » are set.
• Sample hierarchical data is inserted.

Step 3: Define the Data Range

Set dataRange = ws.Range("A1:C10")

• The data range for the chart is defined.

Step 4: Create and Insert the Tree Map Chart

Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300)
Set treeMapChart = chartObj.Chart
treeMapChart.SetSourceData Source:=dataRange
treeMapChart.ChartType = xlTreemap

• A chart is inserted into the worksheet.
• The data is assigned to the chart.
• The chart type is set to Tree Map (xlTreemap).

Step 5: Format the Tree Map Chart

With treeMapChart
    .HasTitle = True
    .ChartTitle.Text = "Tree Map Chart - Sales Data"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Font.Bold = True
    .Legend.Position = xlBottom
End With

• A title is added to the chart.
• The title’s font size and bold property are set.
• The legend is positioned at the bottom.

Step 6: Autofit Columns and Display Success Message

ws.Columns("A:C").AutoFit
MsgBox "Tree Map Chart created successfully!", vbInformation, "Success"

• Columns are resized for better visibility.
• A message box informs the user that the Tree Map Chart is created successfully.

4. Running the VBA Code

• Open Excel and press ALT + F11 to open the VBA Editor.
• Insert a New Module.
• Copy and paste the VBA code.
• Run CreateTreeMapChart.

5. Expected Output

• A new worksheet named TreeMapData is created.
• The sample data is added.
• A Tree Map Chart is inserted and formatted.

6. Customization Options

You can modify:

• The data to fit your needs.
• The chart title (treeMapChart.ChartTitle.Text).
• The chart size (Width, Height).
• The chart position (Left, Top).

7. Conclusion

This VBA macro automates the process of creating a Tree Map Chart in Excel, saving time and ensuring consistency.