Table of Contents
Creating a pie chart in VBA is a simple process. First, create an Excel worksheet containing the data you want to use in your pie chart. Then, use the Chart Wizard to create an embedded chart with the data you selected. Finally, format the chart to customize the design and appearance of the pie chart. You can also use the Chart Properties and Chart Tools to further modify the chart. With the Chart Wizard, creating a pie chart in VBA is a straightforward process.
You can use the following basic syntax to create a pie chart in Excel by using VBA:
Sub CreatePieChart()
Dim MyChart As ChartObject
'get input range from user
Set Rng = Application.InputBox(Prompt:="Select chart input range", Type:=8)
'create pie chart
Set MyChart = Worksheets("Sheet1").ChartObjects.Add(Left:=ActiveCell.Left, _
Width:=400, Top:=ActiveCell.Top, Height:=300)
MyChart.Chart.SetSourceData Source:=Rng
MyChart.Chart.ChartType = xlPie
End Sub
This particular macro will prompt the user for an input range, then automatically generate a pie chart using the input range and insert it into the sheet called Sheet1 with the top left corner of the chart located in the currently active cell.
The following example shows how to use this macro in practice.
Example: How to Create a Pie Chart in VBA
Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:
Suppose we would like to use VBA to generate a pie chart using this dataset.
We can create the following macro to do so:
Sub CreatePieChart()
Dim MyChart As ChartObject
'get input range from user
Set Rng = Application.InputBox(Prompt:="Select chart input range", Type:=8)
'create pie chart
Set MyChart = Worksheets("Sheet1").ChartObjects.Add(Left:=ActiveCell.Left, _
Width:=400, Top:=ActiveCell.Top, Height:=300)
MyChart.Chart.SetSourceData Source:=Rng
MyChart.Chart.ChartType = xlPie
End Sub
To run this macro, we can click on the Developer tab along the top ribbon in Excel, then click Macros.
We can then click the one titled CreatePieChart and then click Run:
Once we click Run, we will be prompted for an input range for our bar chart:
We will type A1:B7, then press OK.
The following pie chart will automatically be created and displayed with the top left corner of the chart located in the currently active cell, which happens to be cell D2:
Note: You can change the values for the Width and Height arguments in the ChartObjects.Add() function to adjust the width and height of the pie chart, respectively.