Draaitabellen en VBA

  • 26/01/2013
Voorbeeldbestanden bij dit artikel:
  • Draaitabellen en VBA

    • Inleiding

    Op deze pagina toon ik jullie 6 van mijn favoriete stukjes VBA-code wanneer ik met draaitabellen werk:

    1. draaitabellen automatisch bijwerken
    2. kolommen bij een detailview automatisch breed genoeg maken
    3. een snapshot of backup nemen van een draaitabel layout
    4. de som-weergave veranderen in aantal-weergave
    5. alle draaitabellen in een bestand met 1 commando vernieuwen
    6. switchen tussen de klassieke en de nieuwe look van een draaitabel

    Hier zie je een afbeelding van een draaitabel met daarnaast een stuk uit de (fake) brondata voor dit artikel. De draaitabel toont de gemiddelde lengte (in cm) van inwoners per staat van de Verenigde Staten. Het bestand staat tot jullie beschikking, bovenaan deze pagina. Download het gerust en volg mee in detail. De volledige brontabel bevat 500 fake records.

    Draaitabellen automatisch bijwerken

    In het bestandje staat de draaitabel op een werkblad genaamd pivot. De data staat op het eerste tabblad. M.a.w. telkens we het tabblad van de draaitabel activeren, moet de draaitabel bijgewerkt worden. En mochten er meerdere draaitabellen op dat werkblad staan, dan wensen we die allemaal bij te werken. Zoiets kan met deze korte event code of gebeurteniscode: toetreden tot het bewuste tabblad zal de draaitabel(len) bijwerken. 

    Private Sub Worksheet_Activate()
    

    '''''
' VBA-code to refresh / update pivot tables
' 13/10/12
'''''
    
    Dim pt As PivotTable
    For Each pt In Me.PivotTables
        pt.RefreshTable
    Next


    End Sub

    Je plaatst deze code in de sheet module van het bewuste blad. Gewoon switchen tussen tabbladen is reeds voldoende om automatisch een bijgewerkte draaitabel voor je te zien. Doe je dit niet, dan moet je (bvb.) via een rechtermuisklik op elk van de draaitabellen klikken, en kiezen voor Refresh (Vernieuwen). Niet alleen is dit tijdrovend, vroeger of later vergeet je dit te doen en rapporteer je aan de baas op basis van verkeerde cijfers!

    Kolombreedtes aanpassen

    De volgende handige code gaat over de detailweergave (drill) van een draaitabel. Stel dat je informatie wil over de gemiddelde lengte van 169 cm in de staat Arizona. Wie zijn de personen (uit de lijst van 500) die zorgen voor een gemiddelde van 169? Hoeveel wegen zij? Daartoe dubbelklik je op het getal 169 en toont Excel jou de gegevens van die personen in een nieuw tabblad:

    Dat is zeer gemakkelijk en handig. Wat mij stoort, is dat Excel niet de kolommen automatisch breed genoeg maakt. Kijk naar de rij van Helen Robertson-Carmichael: de kolom van de familienaam is niet breed genoeg. Dit wil ik vermijden door automatisch de kolommen passend te maken. Een tweede punt is dat ik tabbladen met de details liever als laatste tabblad(en) heb. Nu doet Excel dat niet automatisch, maar wordt het nieuwe tabblad links van het blad van de draaitabel gezet. Deze 2 verbeteringen in code gezet, geeft: 

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    

    '''''
' VBA-code to autofit pivot table detail views
' 13/10/12
'''''
    
    Sh.UsedRange.Columns.AutoFit
    Sh.Move After:=Sh.Parent.Worksheets(Sh.Parent.Worksheets.Count)


    End Sub

    Opnieuw is dit een event of gebeurtenis in VBA. Dit keer zet je de code bij ThisWorkbook. Op forums hebben nogal mensen zich afgevraagd hoe ze dit konden doen. Bvb. hier. De gedachte achter deze code is wel dat ELK nieuw tabblad in het bestand passende kolommen zal krijgen, en tevens achteraan in de rij gezet wordt. Wil je dit niet, dan moet je aanpassingen/uitbreidingen maken in de macro code.

    Lees op de volgende pagina voort over nog 3 zeer handige VBA codes voor draaitabellen.




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links