Pages

How to Insert a Pie Chart Using VBA

In this lesson you will learn how to insert a pie chart automatically using VBA. Pie charts are very useful in data visualization. Because from a pie chart, users can get a clear picture about the data at a glance. So let's use the below sample data to see how to create a pie chart automatically using VBA. This table shows the profits of different branches of a company.

sample data sheet

Assume name of the worksheet is "Sheet1". As we have data in the range A1:B6, we can insert the pie chart automatically using VBA as follows.

Sub Create3DPieChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(-1, xl3DPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

Here is the result of the above macro.

VBA Program will insert the default pie chart

This is the syntax of the AddChart2 method.

Syntax of the AddChart2 method

Note that all the parameters are optional for the AddChart2 method. In the above VBA code we have set “-1” as the style and the “xl3DPie” as the xlChartType. Since we have used “-1” Excel will create the default style of the xl3DPie chart type.

Check this post if you want to learn more about the AddChart2 method.

AddChart2 Method

Next let’s see how we can insert a 2D Pie chart by modifying the above VBA code. To do that we need to only change the second parameter of the AddChart2 method.

Sub Create2DPieChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(-1, xlPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

If you run the above macro, a 2D Pie chart will be created like this.

2D Pie chart inserted by the VBA macro

Sometimes you may want to insert a pie chart with a different style. For an example, suppose you want to create a pie chart with a dark background. This is how you can do it in Excel 2013.

Sub Create3DPieChart_DarkBackground()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(262, xl3DPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub
Pie chart with dark background

Note that we only changed the style number to create this new chart.

However not all the chart styles are available for every Excel version. Because newer Excel versions have more features than previous versions. So how do we find the exact style number of our prefered chart? You can find that by using the Record Macro function.

Follow these simple steps to find the style number of your preferred chart.

Go to the developer tab and click on Record Macro.

Go to developer tab and click record macro

Give the macro a suitable name and click OK. Also you can decide where you want to store the VBA macro by selecting an option from the “Store macro in” dropdown.

Give the macro a name and click OK

Then select the data.

Select data

Go to the Insert tab and select the chart you want to create.

Select the chart you want to insert

Now select a chart design from the list. I will select the 3rd design from the top row for this example.

Select a chart design from the list

Next, go to the “Developer” tab again and click “Stop Recording”.

Stop recording

Click on the “Visual Basic” to view the code.

Click on the visual basic to view the VBA code

Now you can incorporate these circled values to your code to create a chart automatically with similar style.

Generated VBA code

Check this post if you want to learn more about recording a macro in Excel.

Record a Macro in Excel