Receive new articles directly in your inbox!! Register your email address

How to Create Pivot Charts with VBA

Charts are a key tool when presenting data to your audience in order to deliver an immediate and effective message

Whether you're presenting insights to stakeholders, unraveling patterns, or simply gaining a deeper understanding of your data, the power of visualization can't be matched by any other solution

Graphs are also a key tool for personal data analysis and decision making as visualizations can be customized to the requirements of each analyst looking at the data

This post will show how to build pivot charts with VBA directly from a Pivot Table. This will enable to make complex data sets immediately accessible to diverse audiences. Once you have combined, summarized and calculated data with Pivot Tables you can then move to analyse and present it with Pivot Charts

A previous post explained how to create Pivot Tables with VBA. It is now time to explain how to build a Pivot Chart from the data.

The code will start from an existing Pivot Table which is assumed to be as in the picture and will provide the steps of the VBA code that will bring to the creation of the graph shown in the picture

This post will create a first basic chart by using the AddChart method

Using the AddChart method

A Chart object is a special type of the Shape object; it is therefore a member of the Shapes collection.

To create a new chart we use the AddChart method

The following statement creates and empty chart in the active worksheet

ActiveSheet.Shapes.AddChart

The AddChart method accepts 5 arguments all of which are optional

TYPE: The type of chart that we want to used A list of the most commonly ised chart types is reported below. If it is not specified the standard chart type will be used
LEFT: The left position of the chart in points (therefore not really user friendly) If omitted the chart is centered horizontally
TOP: The top position of the chart in points (again not really user friendly). If omitted the chart is centered vertically
WIDTH: The width of the chart in points If omitted Excel uses 354.
HEIGHT: Chart height in pints If omitted Excel uses 210

A good solution to use at best the measures reported in points is to create a graph by hand with the Macro recorder switched on in order to understand how your perfect chart translates in points

Commonly used chart types are the following

xlArea

xlLine

xlPie

xlColumnClustered

xlColumnStacked

xlColumnStacked100

xlArea

xlAreaStacked

xlBarClustered

xlBarStacked

xlBarStacked100

Creating the chart

It is useful to allocate the created chart to an object variable in order to reference it during the code execution

The following procedure creates an variable containing a chart object with the clusterd column chart type

Dim Mychart As Chart

Set Mychart = ActiveSheet.Shapes.AddChart(xlColumnClustered).Chart

Once the chart object variable has been created it needs to have data in it to make it meaningful

To accomplish this task the SetSoucd is used to add data to the newly created chart

In this case the data is taken from a Pivot Table (named PT) and using the TableRange1 method that allows to reference the data in the Pivot Table

Set Mychart = ActiveSheet.Shapes.AddChart(xlColumnClustered).Chart

Mychart.SetSourceData Source:=PT.TableRange1

The final result is the graph shown in the picture at the start of the post

Combining it with the code for creating a Pivot Table

The below code creates a Pivot Table and a Pivot Chart

In depth explanation on how to create a Pivot Table with VBA is reported HERE and HERE

The table containing the data from which the Pivot Table sis built is contained in a sheet called Data and it is organised as reported in the below picture

Sub createpivottable()

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim Rng As Range

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Sheets("Data").Select

lastRow = Sheets("Data").Cells(Sheets("Data").Rows.Count, 1).End(xlUp).Row

Set Rng = Sheets("Data").Range(Cells(1, 1), Cells(lastRow, 6))

On Error Resume Next

Sheets("Pivotsheet").Delete

On Error GoTo 0

Worksheets.Add

ActiveSheet.Name = "Pivotsheet"

Set PTCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=Rng)

Set PT = PTCache.createpivottable( _

TableDestination:=Range("a1"), _

TableName:="Food")

With PT

.PivotFields(2).Orientation = xlRowField

.PivotFields(4).Orientation = xlColumnField

.PivotFields(3).Orientation = xlDataField

.DisplayFieldCaptions = False

End With

Dim Mychart As Chart

Set Mychart = ActiveSheet.Shapes.AddChart(xlColumnClustered).Chart

Mychart.SetSourceData Source:=PT.TableRange1

Set PTCache = Nothing

Set PT = Nothing

Set Rng = Nothing

Set Mychart = Nothing

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

Page updated

Google Sites

Report abuse