PowerPivot itself cannot be automated with VBA. But with a few tricks we can control how the data drawn from PowerPivot is accessed within Excel. This article assumes that you know how to import data with PowerPivot and how to integrate it into an Excel file as a pivot table. Also you should have activated the developer tools in Excel. There you find all the VBA relevant stuff like the macro recorder and the dialogue controls you want to add.
Let’s get it started with an example
We start with a PowerPivot table (named “data”) keeping the values for two variables A and B for every day in 2012 imported from a MySQL-DB in this case. So in the PowerPivot window this would look something like that:
So let’s assume some manager is asking for a dashboard displaying those two values in a chart but only one at a time and s/he is not inclined to deal with pivot table field lists but wants to switch between A and B using buttons.
(As a matter of fact this could also be accomplished by substituting columns ‘A’ and ‘B’ with ‘type’ and ‘value’. Then the every row would end up as two rows, one with ‘A’ and another with ‘B’ in column ‘type’ – while ‘value’ keeps the respective value for that day and variable (long format). Now we can apply a slicer on ‘type’. In that case we would just have to use original PowerPivot technologies. But of course this example serves mostly the purpose of demonstrating how to use VBA with PowerPivot.)
First of all we create a new PowerPivot pivot table (PowerPivot section of the ribbon) and name it “pivot_table” – this can be done by right-clicking on a cell of the pivot table and choosing the dialogue for “pivot table options”. At the top you can choose the name. We also adjust the name of the sheet where the pivot table is located to “dashboard”. We will need those names later within the VBA code.
This is how it looks like in Excel
Mind the difference between XLSX and XLSM
Before we invest more energy into this workbook we are going to save it as an XLSM file because XLSX cannot store VBA program code. The security dialog asking for whether to activate the content or not has to be dealt with only once when someone opens the file for the first time.
Trick #1: Two different field lists – the PowerPivot and the good old one
The first trick is that there are two perspectives onto the pivot table structure. One is through the usual PowerPivot field list – which opens automatically. But there is also the usual pivot table field list available. When you place the focus on a cell in the PowerPivot pivot table the ribbon offers you a new section “PivotTable tools” and in “Options” you choose “field list”. This field list looks a bit different from the PowerPivot field list and I so far only found one advantage which is that you can use the macro recorder on it. That means if you want to know how it looks like in VBA – f.x. exchanging [yw] and [w] with [y] and [m] (which changes the time unit from week to month) – you just activate the macro recorder and then you do it. Afterwards you can check out the macro you recorded. Though keep in mind that the macro code produced via this recording tool is usually only of educational importance and has to be turned into code of more sound quality.
Trick #2: Make a variable / measure persistently available in VBA
Before we can access a PowerPivot driven measure (a variable) from within the regular field list we have to drag it into the “value area” of the PowerPivot field list. Now you can see it in the regular field list. But a new pitfall is now waiting for us – Excel is going to “forget” this measure after certain actions like refreshing the data. Then all of a sudden the recorded macros / VBA scripts stop working. This issue can be bypassed as I found out by creating a new measure within the PowerPivot field list which basically just mirrors the respective value.
There you get by right-clicking on the PowerPivot table name (data) and choosing “Add new measure …”
Now we can access the values of A and B using their new internal names “measure_for_A” and “measure_for_B”. So let’s record a macro where we just drag the value “A” into the “value area” within the regular field list.
And this is how the macro is going to look like:
|
1 2 3 4 5 6 7 8 9 |
Sub Makro1() ' ' Makro1 Makro ' ' ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").CubeFields("[Measures].[measure_for_A]") End Sub |
The VBA code for the buttons
And here is the code for the variable switch:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub show(var_name) 'removes both variables so we can add the right one afterwards Sheets("dashboard").PivotTables("pivot_table") _ .CubeFields("[Measures].[measure_for_A]") _ .Orientation = xlHidden Sheets("dashboard").PivotTables("pivot_table") _ .CubeFields("[Measures].[measure_for_B]") _ .Orientation = xlHidden 'adds the variable to the pivot table values Sheets("dashboard").PivotTables("pivot_table").AddDataField _ ActiveSheet.PivotTables("pivot_table") _ .CubeFields("[Measures].[" & var_name & "]") End Sub Sub show_A() 'add measure_for_A to the pivot table Call show("measure_for_A") 'writes the "A" to A1 so we can use it to 'display the currently used variable's name Sheets("dashboard").Cells(1, 1).Value = "A" End Sub Sub show_B() 'add measure_for_A to the pivot table Call show("measure_for_B") 'writes the "B" to A1 so we can use it to 'display the currently used variable's name Sheets("dashboard").Cells(1, 1).Value = "B" End Sub |
The procedures show_A and show_B now have to be associated with their respective button. VBA code always looks ugly – but I guess even when you have no experience with it the code should be easily understandable – just look at it long enough ;)
Trick #3: Using a cell value in a text box
To show the value of a cell in a text box – let’s say cell A1 you have to follow these steps:
- add the text field/box
- place the cursor within it
- but instead of just typing in the box you click into the function input field and enter ‘=A1’. If you write this into the box itself it is interpreted as a simple text.
Applications for this
Using buttons for the purpose of controlling pivot tables and the charts resulting from it is especially useful for switching time units. A lot of dashboards I am responsible for use day based figures which can be aggregated on a daily, weekly and monthly basis. Switching between those perspectives using the field list would be way to tedious. So there are three buttons to choose the time base you want. And most managers are easily impressed with such features – “I didn’t even know you can do something like that with Excel …”.