Create a donut chart in Excel VBA

Steps to Create a Donut Chart Using VBA in Excel:

Prepare the Data in Excel: Before running the VBA code, ensure that you have data structured in a table format. For example:

• Access the VBA Editor:
  • Open your Excel workbook.
  • Press Alt + F11 to open the VBA editor.
  • Click Insert, then select Module to create a new module.
• Copy the following VBA code into the module:

Sub CreateDonutChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range   
    ' Define the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Replace "Sheet1" with your sheet name
    Set dataRange = ws.Range("A1:B5")  ' Replace "A1:B5" with the range of your data
    ' Create the donut chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225) ' Set position and size of the chart
    chartObj.Chart.SetSourceData Source:=dataRange  ' Set the data source for the chart
    ' Set the chart type to Donut
    chartObj.Chart.ChartType = xlDoughnut  ' Donut chart type is xlDoughnut
    ' Customize the chart (optional)
    With chartObj.Chart
        ' Add a chart title
        .HasTitle = True
        .ChartTitle.Text = "Category Distribution"       
        ' Change color of each segment
        .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)  ' Red
        .SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)  ' Green
        .SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)  ' Blue
        .SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)  ' Yellow       
        ' Display data labels (value and percentage)
        .ApplyDataLabels ShowValue:=True, ShowPercentage:=True      
        ' Optional: Add a legend
        .HasLegend = True
    End With
End Sub

Detailed Explanation of the Code:

• Variable Declarations:
  • ws: Represents the worksheet where the chart will be created.
  • chartObj: Represents the chart object that we will create.
  • dataRange: Represents the data range to be used for the chart.
• Defining the Worksheet and Data Range:
  • Set ws = ThisWorkbook.Sheets(« Sheet1 »): Defines the worksheet containing your data. Replace « Sheet1 » with your actual worksheet name.
  • Set dataRange = ws.Range(« A1:B5 »): Defines the range of data to be used for the chart. Change « A1:B5 » to the actual range of your data.
• Creating the Chart:
  • Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225): This creates a new chart object in the worksheet and sets its position (left, width, top, height) in pixels.
  • chartObj.Chart.SetSourceData Source:=dataRange: Sets the data source for the chart.
• Setting the Chart Type:
  • chartObj.Chart.ChartType = xlDoughnut: This changes the chart type to a donut chart (xlDoughnut).
• Customizing the Chart (Optional):
  • With chartObj.Chart: Opens a section to customize the chart.
  • .HasTitle = True: Adds a title to the chart.
  • .ChartTitle.Text = « Category Distribution »: Sets the title text of the chart.
  • .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0): Changes the color of the first segment to red (you can set other colors as well).
  • .ApplyDataLabels ShowValue:=True, ShowPercentage:=True: Displays data labels on the chart, showing both the values and percentages.
  • .HasLegend = True: Adds a legend to the chart.

Running the Code:

1. After pasting the code into the module, close the VBA editor.
2. Go back to Excel and press Alt + F8 to open the Macros window.
3. Select CreateDonutChart and click Run.

A donut chart will be created in the specified worksheet with the data you have defined.

Conclusion:

This VBA code creates a donut chart based on the defined data range and allows for various customizations, such as the chart title, segment colors, data labels, and legend. You can adjust the parameters and data range to suit your needs and adapt it for different scenarios.