I was setting up some trackers at work the other day using some OLAP cubes in Excel across a number of different variables (about 20) to track monthly sales which I could refresh each month. Once I’d set the sales tracker up I realised that I wanted to look at average price across the same variables so I made a copy of the spreadsheet and went through each of the pivot tables changing them to track average price. When I then wanted to look at sales mix (%sales that month) I thought there must be a better way so decided to write some VBA to do all of this for me.
Since I wasn’t sure how long this would take I did this at home. I can’t (for obvious reasons) take work data home so I downloaded the UK Land Registry data on average home prices over the last few years, put it into an Access Database and connected a spreadsheet to this. I might have an actual play with this at some point in the future.
What I wanted to achieve:
- Be able to produce a set of pivot tables and charts for a given set of variables at a click of a button. Tables can be 1-way or 2-way.
- The list is pre-defined in a control tab by the user
- The code can change between absolute values, averages and % row values
- To change between weekly and monthly tables
- To include slicers so that I can filter between different factors
- The charts should all be pivot charts so that when the data updates the charts all update
- The field buttons should not be on the charts (I don’t like field buttons)
- The chart titles should all reflect what is shown in the chart, e.g. Average house price by Region
- The data should not be cached in the excel spreadsheet (I don’t want a 1GB+ spreadsheet)
The attached spreadsheet does just this. I apologise to anyone who is better at writing VBA for the crude methods used. I’m self-taught and the code works, is fully commented and all of the variables used are explicitly defined. Doing things in the most efficient method can come later.
Key bits of VBA
- Clearing the existing data and charts
I decided that it was easier to just delete any pivot tables and charts that exist and add new ones. This way if the list of factors changes the code won’t crash.
'Clear the existing tables Application.DisplayAlerts = False For Each ws In Worksheets If ws.Name <> "Front Sheet" And ws.Name <> "Control" Then ws.Delete Next 'Delete the existing charts If ActiveWorkbook.Charts.Count > 0 Then ActiveWorkbook.Charts.Delete Application.DisplayAlerts = True
When you delete sheets Excel displays an alert. We don’t want this to happen at this stage so we need to turn alerts off. The code then deletes all sheets apart from “Front Sheet” and “Control” which contain our key data. “ActiveWorkbook.Charts.Delete” causes an error if there aren’t any charts in your spreadsheet so this is only done if there are charts in the spreadsheet.
- Setting the chart title
Having a chart title set as equal to a cell in the spreadsheet doesn’t work when “Option Explicit” is set. I don’t know why so if someone knows please tell me. So I need to change the title in a different way.
.ChartTitle.Characters.Text = MetricName & " by " & ColName
This sets the chart title using variables already defined in the code.
The .csv is a sample of the data used which you can put into a database yourself and test the spreadsheet out. It only contains 1000 rows, the charts in the spreadsheet use this data so don’t come to any conclusions from it, there is far more noise than signal. If I get time I’ll post some findings from the more complete dataset.
Download Spredsheet: LandRegistryPivots
Download CSV: Sample