Creating Treemap Chart in Excel with VBA

A Treemap Chart is a data visualization tool that represents hierarchical data as nested rectangles. Each category is assigned a rectangle whose size is proportional to the corresponding value.

1. Understanding Treemap Charts

• Treemaps are useful for displaying proportions within a hierarchy.
• They work well with structured data such as product sales by category and sub-category.

2. Setting Up the Data

To create a Treemap Chart using VBA, your data should be structured hierarchically, like this:

3. VBA Code to Create Treemap Chart

The following VBA macro will:

1. Insert a Treemap Chart in the active worksheet.
2. Format the chart for better readability.

VBA Code

Sub CreateTreemapChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rng As Range
    Dim treemapChart As Chart
    ' Set worksheet
    Set ws = ActiveShee
    ' Define the data range (adjust as necessary)
    Set rng = ws.Range("A1:C8") ' A1:C8 contains Category, Sub-Category, and Value
    ' Insert Chart Object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=50, Height:=300)
    ' Reference the Chart inside the ChartObject
    Set treemapChart = chartObj.Chart
    ' Set chart source data
    treemapChart.SetSourceData Source:=rng
    ' Change chart type to Treemap
    treemapChart.ChartType = xlTreemap
    ' Format Chart Title
    treemapChart.HasTitle = True
    treemapChart.ChartTitle.Text = "Sales Distribution by Category"
    ' Set legend position
    treemapChart.Legend.Position = xlLegendPositionBottom
    ' Improve readability of the chart
    With treemapChart
        .ApplyLayout (1) ' Apply a default layout
        .ChartStyle = 5 ' Use a pre-defined chart style
    End With
    ' Auto-size chart for better visibility
    chartObj.Width = 500
    chartObj.Height = 350
    chartObj.Top = 20
    chartObj.Left = 50
    ' Release objects
    Set treemapChart = Nothing
    Set chartObj = Nothing
    Set ws = Nothing
    Set rng = Nothing
    MsgBox "Treemap Chart Created Successfully!", vbInformation, "Success"
End Sub

Explanation of the VBA Code

Step 1: Selecting the Worksheet

Set ws = ActiveSheet

• This ensures the macro runs on the currently active worksheet.

Step 2: Defining the Data Range

Set rng = ws.Range("A1:C8")

• This specifies the range of data for the Treemap Chart.

Step 3: Inserting a Chart Object

Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=50, Height:=300)

• Adds a new chart to the worksheet with specific dimensions.

Step 4: Setting Up the Chart

Set treemapChart = chartObj.Chart
treemapChart.SetSourceData Source:=rng
treemapChart.ChartType = xlTreemap

• Defines the data source and sets the chart type to Treemap.

Step 5: Formatting the Chart

treemapChart.HasTitle = True
treemapChart.ChartTitle.Text = "Sales Distribution by Category"

• Enables and customizes the chart title.

treemapChart.Legend.Position = xlLegendPositionBottom

• Moves the legend to the bottom for clarity.

With treemapChart
    .ApplyLayout (1) ' Apply default layout
    .ChartStyle = 5 ' Use a pre-defined chart style
End With

• Applies formatting to enhance the appearance of the chart.

Step 6: Adjusting Chart Size and Position

chartObj.Width = 500
chartObj.Height = 350
chartObj.Top = 20
chartObj.Left = 50

• Resizes and repositions the chart on the worksheet.

Step 7: Cleaning Up Objects

Set treemapChart = Nothing
Set chartObj = Nothing
Set ws = Nothing
Set rng = Nothing

• Releases object references to free up memory.

5. Running the VBA Code

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Insert a new module and paste the VBA code.
3. Select your sheet and ensure data is structured correctly.
4. Run the macro CreateTreemapChart by pressing F5.

6. Expected Output

After running the macro, a Treemap Chart will appear on the active worksheet, displaying the hierarchical sales data. The rectangles will be sized proportionally to their values, making it easy to compare categories and sub-categories.

7. Additional Customization

You can enhance the chart by:

• Changing the chart title dynamically based on a cell value:

• ChartTitle.Text = ws.Range(« E1 »).Value

• Modifying color themes:

• ChartStyle = 10

• Adding Data Labels:

• ApplyDataLabels xlDataLabelsShowValue

• Adjusting the legend format:

• Legend.Font.Size = 12

8. Conclusion

This VBA macro automates the creation of a Treemap Chart in Excel, making it easy to visualize hierarchical data. You can modify the macro further to dynamically select data or enhance the chart formatting.