Create Sunburst Chart with VBA Excel

Step 1: Prepare the Data

Ensure your data is structured hierarchically. For a Sunburst chart, you will need columns that represent the hierarchical levels, such as:

1. Level 1 (Main Category)
2. Level 2 (Subcategory)
3. Level 3 (Sub-subcategory, etc.)
4. Values (representing the size of each segment)

Example of Data:

Step 2: Add VBA Code

Here’s the VBA code to create the Sunburst chart based on the data above.

Step 3: Explanation of the Code

1. Worksheet and Data Range:
   • The code starts by defining the worksheet (ws) where the data is located and calculating the last row with data (lastRow).
   • The dataRange is set to include the data from column A to D, from row 1 to lastRow.
2. Chart Creation:
   • A new chart object is added to the worksheet using ChartObjects.Add, and its position and size are specified with the Left, Width, Top, and Height parameters.
   • The data source for the chart is set using SetSourceData pointing to dataRange.
3. Chart Type:
   • The chart type is set to xlSunburst, which creates a Sunburst chart.
4. Optional Customizations:
   • A title is added to the chart using .HasTitle = True and setting the text for the chart title.
   • Some formatting is applied to the chart using .ApplyLayout (4), which can be adjusted according to your preferences.
   • The legend is moved to the bottom and excluded from the layout.

Step 4: Running the Code

1. Open the Excel workbook where you want the Sunburst chart.
2. Press Alt + F11 to open the VBA editor.
3. Insert a new module (Insert > Module).
4. Paste the above code into the module.
5. Close the editor and return to Excel.
6. Press Alt + F8, select CreateSunburstChart, and click « Run. »

Step 5: Customization

You can modify the code to adjust various aspects:

• Chart Size: Change the Left, Top, Width, and Height parameters when adding the chart.
• Chart Design: Customize the chart design by changing the layout, colors, and legend position