Create & Customize Excel Pivot Table Charts, using VBA
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
Refer the separate section of “Charts in Excel VBA – Add a Chart, the Chart object & the ChartObject object“ which illustrates (in detail) creating & manipulating charts with vba code.
Charts can be added in the workbook as: (i) chart sheets – this sets charts as new sheets in your workbook. to remove a chart sheet, the entire sheet is required to be deleted.; and (ii) embedded charts – this includes the chart as an object within a sheet, which could be the same worksheet as the data.
Example 1: Create & Customize a PivotChart based on an existing PivotTable report, using the Charts.Add Method.
Create a PivotChart based on an existing PivotTable report, using the Charts.Add Method. Refer Image 1 for the existing PivotTable report on which PivotChart is based. Refer Image 1a for the PivotChart created after running below code.
Sub createPivotChart1a()
Dim PvtTbl As PivotTable
Dim rngChart As Range
Dim objChart As Chart
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
‘use the Charts.Add Method to add a new chart sheet in the workbook, which is represented as a Chart object
‘adds new chart sheet before or after specified sheet:
ActiveWorkbook.Charts.Add Before:=Worksheets(“Sheet14”), Count:=1
‘Alternate 1: to add new chart sheet before the active sheet, use below code line:
‘Set objChart = Charts.Add
‘Alternate 2: to add new chart sheet after the last worksheet, use below code line:
‘ActiveWorkbook.Charts.Add After:=Worksheets(Worksheets.Count)
‘set as active worksheet:
Set objChart = ActiveSheet
‘set range for the PivotChart, based on the existing PivotTable:
Set rngChart = PvtTbl.TableRange2
‘specify the source data for the PivotChart, name of chart sheet and the chart type:
With objChart
.SetSourceData rngChart
.Name = “PivotChart1”
.ChartType = xlColumnClustered
End With
End Sub
Customize PivotChart created above as Image 1a. Refer Image 1b for the customized PivotChart after running below code.
Sub customizePivotChart1b()
‘set as active worksheet:
Sheets(“PivotChart1”).Activate
Dim objChart As Chart
Set objChart = Sheets(“PivotChart1”)
With objChart.ChartArea
‘adjust position and size (both in points) of the Chart Area:
.Width = 440
.Height = 300
.Left = 5
.Top = 0
End With
With objChart
.Legend.Position = xlLegendPositionTop
.PlotBy = xlColumns
‘plot area settings:
.PlotArea.Interior.Color = vbYellow
.PlotArea.Border.LineStyle = XlLineStyle.xlDash
‘chart title settings:
.HasTitle = True
.ChartTitle.Characters.Text = “Sales-$”
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 14
‘color the x-axis bars:
.SeriesCollection(1).Interior.Color = vbGreen
.SeriesCollection(“Sum of Budgeted Sales”).Interior.Color = vbBlue
‘show data labels for SeriesCollection(“Sum of Budgeted Sales”):
.SeriesCollection(2).HasDataLabels = True
End With
End Sub
Delete PivotChart as in Image 1b
Sub deletePivotChart1c()
Dim objChart As Chart
Set objChart = Sheets(“PivotChart1”)
objChart.Delete
End Sub
Example 2: Add embedded chart based on an Excel Database, using the ChartObjects.Add Method.
Add embedded chart based on an Excel Database, using the ChartObjects.Add Method. Refer Image 2a which shows the added embedded Chart after running the code below. Refer Image 2 for source range ie. “Sheet13”.
Sub createEmbeddedChart2a()
Dim rngChart As Range
Dim objChart As ChartObject
‘Use the ChartObjects.Add Method to add a new embedded chart, which is represented as a ChartObject object. Note that the arguments Left and Width are mandatory to specify in this method. This method allows you to set the position and size (both in points) of the chart.
Set objChart = Sheets(“Sheet4”).ChartObjects.Add(Left:=50, Top:=50, Width:=375, Height:=300)
‘set data source range for the Chart:
Set rngChart = Worksheets(“Sheet13”).Range(“B1:D6”)
With objChart.Chart
.SetSourceData Source:=rngChart
End With
End Sub
Customize PivotChart created above as Image 2a. Refer Image 2b for the customized PivotChart after running below code.
Sub customizeEmbeddedChart2b()
‘set as active worksheet:
Sheets(“Sheet4”).Activate
Dim objChart As ChartObject
Set objChart = Sheets(“Sheet4”).ChartObjects(1)
With objChart.Chart
‘set XlChartType:
‘XlChartType examples include:- xlBarClustered, xl3DBarStacked, xl3DColumn, xl3DColumnStacked, xl3DLine, xlColumnClustered, xlColumnStacked, xlLine, xlPie, …
.ChartType = xlColumnClustered
‘use the XValues Property to set x values for a chart series:
.SeriesCollection(1).XValues = Worksheets(“Sheet13”).Range(“C2:C6”)
‘legend settings:
.HasLegend = True
.Legend.Position = xlLegendPositionTop
‘name the series:
.SeriesCollection(1).Name = “Actual Sales”
‘color the x-axis bars:
.SeriesCollection(1).Interior.Color = vbRed
‘chart title settings:
.HasTitle = True
.ChartTitle.Characters.Text = “Sales-$”
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 14
‘plot area settings:
.PlotArea.Interior.Color = vbYellow
.PlotArea.Border.LineStyle = XlLineStyle.xlDash
.PlotArea.InsideHeight = 225
.PlotArea.Top = 22
‘y-axis of a chart is the value axis:
With .Axes(xlValue, xlPrimary)
.MaximumScale = 2500
.MinimumScale = 0
.MajorUnit = 500
.MinorUnit = 100
.TickLabels.NumberFormat = “#,##0”
End With
‘x-axis is where the categories appear:
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = “Car Models”
.AxisTitle.Font.Bold = True
.AxisTitle.Font.Size = 12
.Border.Color = vbGreen
End With
End With
‘adjust position and size (both in points) of the Chart Object:
With objChart
.Left = 30
.Top = 30
.Width = 320
.Height = 240
End With
‘create new series:
With objChart.Chart.SeriesCollection.NewSeries
.Name = “Budgeted Sales”
.Values = Worksheets(“Sheet13”).Range(“E2:E6”)
.XValues = Worksheets(“Sheet13”).Range(“B2:B6”)
.AxisGroup = xlPrimary
.PlotOrder = 1
End With
‘Chart Series Formula =SERIES(name,category_labels,y_values,plot_order). Select the series in the chart which will show the Series Formula in the formula bar. Name of the series is displayed on the legend; Category_labels are x-values; y_values are the plotted values; Plot_order determines the order of plotting of series and the legend names also appear in this order. It is a number starting from 1 and not exceeding the total count of series. Series plotted first in the series collection is referenced as .SeriesCollection(1). Series can be referenced by name viz. .SeriesCollection(“Sum of Sales”).
End Sub
Code to delete all chart series:
Sub deleteSeries2c_a()
‘delete all chart series
Dim srs As Series
Dim objChart As ChartObject
Set objChart = Sheets(“Sheet4”).ChartObjects(1)
For Each srs In objChart.Chart.SeriesCollection
srs.Delete
Next srs
End Sub
Code to delete all chart series:
Sub deleteSeries2c_b()
‘delete all chart series
Dim n As Integer
Dim objChart As ChartObject
Set objChart = Sheets(“Sheet4”).ChartObjects(1)
With objChart.Chart
If .SeriesCollection.Count >= 1 Then
For n = .SeriesCollection.Count To 1 Step -1
.SeriesCollection(n).Delete
Next n
End If
End With
End Sub
Code to delete Chart:
Sub deleteSeries2c_c()
‘delete Chart
Dim n As Integer
Dim objChart As ChartObject
Set objChart = Sheets(“Sheet4”).ChartObjects(1)
objChart.Delete
End Sub
Example 3: Create a Chart (add a new chart sheet) based on an Excel Database, using the Charts.Add Method.
Create a Chart (add a new chart sheet) based on an Excel Database, using the Charts.Add Method. Refer Image 3 for the PivotChart created after running below code.
Sub createPivotChart3()
Dim rngChart As Range
Dim objChart As Chart
Dim wsData As Worksheet
‘determine the worksheet which contains the source data:
Set wsData = Worksheets(“Sheet13”)
‘set data source range for the Chart:
Set rngChart = wsData.Range(“A1:D6”)
‘use the Charts.Add Method to add a new chart sheet in the workbook, which is represented as a Chart object
‘adds new chart sheet before the active sheet:
Set objChart = Charts.Add
‘Alternate 1: adds new chart sheet after the last worksheet:
‘ActiveWorkbook.Charts.Add After:=Worksheets(Worksheets.Count)
‘Alternate 2: adds new chart sheet before or after specified sheet:
‘ActiveWorkbook.Charts.Add Before:=Worksheets(“Sheet14”), Count:=1
‘Chart settings:
With objChart
‘name of the new chart sheet:
.Name = “ChartSheet1”
.SetSourceData rngChart
.ChartType = xl3DColumn
.Legend.Delete
.PlotBy = xlColumns
.HasTitle = True
.ChartTitle.Text = “Car Sales”
.ChartTitle.Left = 190
End With
‘adjust position and size (both in points) of the plot area:
With objChart.PlotArea
.Width = 380
.Height = 250
End With
‘color x-axis:
Charts(“ChartSheet1”).SeriesCollection(1).Format.
Fill.ForeColor.RGB = rgbBlue
‘move the chart worksheet (named ChartSheet1) to the end:
Charts(“ChartSheet1”).Move after:=Sheets(Sheets.Count)
End Sub
Example 4: Create an embedded PivotChart based on an existing PivotTable report, using the ChartObjects.Add Method.
Create an embedded PivotChart based on an existing PivotTable report, using the ChartObjects.Add Method. Refer Image 4 for the existing PivotTable report on which PivotChart is based. Refer Image 4a for the PivotChart created after running below code.
Sub createPivotChart4a()
Dim rngChart As Range
Dim objChart As ChartObject
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
‘Use the ChartObjects.Add Method to add an embedded Pivot Chart, which is represented as a ChartObject object. Note that the arguments Left and Width are mandatory to specify in this method. This method allows you to set the position and size (both in points) of the chart.
Set objChart = Sheets(“Sheet17”).ChartObjects.Add(Left:=10, Top:=10, Width:=300, Height:=200)
‘set data source range for the Chart:
Set rngChart = PvtTbl.TableRange2
With objChart.Chart
.SetSourceData Source:=rngChart
End With
End Sub
Customize PivotChart created above as Image 4a. Refer Image 4b for the customized PivotChart after running below code.
Sub createPivotChart4b()
Sheets(“Sheet17”).Activate
Dim objChart As ChartObject
Set objChart = Sheets(“Sheet17”).ChartObjects(1)
With objChart.Chart
.HasLegend = True
.Legend.Position = xlLegendPositionTop
‘color the x-axis bars:
.SeriesCollection(1).Interior.Color = vbRed
.SeriesCollection(“Sum of Budgeted Sales”).Interior.Color = vbBlue
‘chart title settings:
.HasTitle = True
.ChartTitle.Characters.Text = “Car Sales”
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 14
‘plot area settings:
.PlotArea.Interior.Color = vbGreen
.PlotArea.Border.LineStyle = XlLineStyle.xlDashDotDot
End With
End Sub