Skip to the content

Hoe maak ik een draaitabel in Excel met VBA

In dit blog leg ik uit hoe je een draaitabel kunt maken in Excel VBA. In het volgende blog gaan we de data ophalen uit een externe database, zoals de database van SnelStart.

VBA-editor? Hoe kom ik daar ook alweer

Mocht je nog niet eerder met VBA voor Excel hebben gewerkt of niet weten wat draaitabellen zijn dan is dit blog waarschijnlijk een stap te ver. Lees dan eerst het blog over het maken van een eigen functie in Excel.

De draaitabel en VBA

In een draaitabel (in het Engels Pivot table) presenteer je de gegevens op een dusdanige manier dat je grote hoeveelheden gegevens op een analytische manier weer kunt geven.

Denk hierbij aan een omzetverdeling per maand gegroepeerd op plaats en vervolgens op klant. Voor dit blog heb ik data gebruikt uit de voorbeeld database van SnelStart. Met deze database ga ik in het vervolg blog laten zien hoe je direct de data uit een database om kunt zetten naar een draaitabel.

SnelStart data

De data uit dit blog komt uit een query de ik heb aangemaakt in de database van SnelStart. Deze query gebruikt de tabellen relaties, verkoopfactuur en verkoopfactuurregels.

Sub SnelstartNaarDraaitabel()
    'Declareer eerst de variabelen
    'We gaan een draaitabel maken en plaatsen op het werkblad [Draaitabel_Omzet]
    'Hiervoor moeten we in VBA eerst de draaitabel declareren
    Dim Draaitabel As PivotTable
    'De variabele [Werkblad_Brondata] stelt VBA in staat het juiste werkblad met onze bron gegevens te vinden en er te lezen en te schrijven
    Dim Werkblad_Brondata As Worksheet
    'De variabele [RangeBereik] stelt VBA in staat een range te selecteren op een werkblad
    Dim RangeBereik As Range
    'In de variabele [Draaitabel_Cache] slaan we tijdelijk gegevens op die we laten nodig hebben in de draaitabel
    Dim Draaitabel_Cache As PivotCache
    'Met deze variabele geven we aan waar de draaitabel moet worden geplaatst
    Dim Werkblad_Draaitabel As Worksheet
    'Tot slot declareren we een variabele die de waarde van een draaitabel veld kan bevatten
    Dim Draaitabel_veld As PivotField
    
    'Nu gaan de we de variabelen gebruiken door ze toe te wijzen
    
    'De brondata uit Snelstart vindt je op het tabblad [Snelstart] uiteraard heb ik deze data hier neergezet en de naam van het tabblad aangepast.
    Set Werkblad_Brondata = Worksheets("Snelstart")
    'Nu vertellen we de variabele [Werkblad_Draaitabel] waar we de draaitabel willen plaatsen
    Set Werkblad_Draaitabel = Worksheets("Draaitabel_Snelstart")
    
    'Om deze functie telkens opnieuw te kunnen aanroepen moeten we de eventueel aanwezige draaitabel op het tabblad [Werkblad_Draaitabel] verwijderen
    For Each Draaitabel In Werkblad_Draaitabel.PivotTables
        Draaitabel.TableRange2.Clear
    Next Draaitabel

    'Nu vertellen we de functie dat de data op ons werkblad [Snelstart] een bereik heeft van veld A1 tot en metG32
    Set RangeBereik = Werkblad_Brondata.Range("A1:G32")
    
    'Nu wordt het even spannend want er zitten versie verschillen in de aanroep van de functie die de draaitabel prepareert in het geheugen
    'Afhankelijk van de versie die je gebruikt aan of uitzetten
    Select Case Application.Version
        Case "11.0" 'Excel 2003
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=RangeBereik, Version:=xlPivotTableVersion11).CreatePivotTable TableDestination:=Werkblad_Draaitabel.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion11
        Case "12.0" 'Excel 2007
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=RangeBereik, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=Werkblad_Draaitabel.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
        Case "14.0" 'Excel 2010
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=RangeBereik, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=Werkblad_Draaitabel.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
    End Select
    
    'Nu maken we een nieuwe draaitabel
    Set Draaitabel = Werkblad_Draaitabel.PivotTables("PivotTable1")

    'Standaard is de handmatige update property uitgezet. Aangezien we graag de data eerst moeten verwerken zet ik deze actie op waar
    Draaitabel.ManualUpdate = True

    'Voeg een rij, kollom en filter toe aan de draaitabel
    
    'We willen een filter plaatsen op het veld jaar zodat we per jaar de gegevens kunnen bekijken
    Set Draaitabel_veld = Draaitabel.PivotFields("Jaar")
    Draaitabel_veld.Orientation = xlPageField

    'Nu voegen we 2 velden toe waarop we de data willen groeperen. In het voorbeeld heb ik gekozen voor de plaats en vervolgens de klanten per plaats
    Set Draaitabel_veld = Draaitabel.PivotFields("Plaats")
    Draaitabel_veld.Orientation = xlRowField
    Draaitabel_veld.Position = 1

    Set Draaitabel_veld = Draaitabel.PivotFields("Naam")
    Draaitabel_veld.Orientation = xlRowField

    'De omzet wil ik per maand analyseren en de maand moet dus horizontaal de omzet tonen
    Set Draaitabel_veld = Draaitabel.PivotFields("Maand")
    Draaitabel_veld.Orientation = xlColumnField

    'Nu moeten we nog de omzet toevoegen aan de juiste maand en hierbij wil ik graag de som van de omzet zien
    With Draaitabel.PivotFields("Omzet")
        .Orientation = xlDataField
        .Function = xlSum
        .NumberFormat = "#,##0"
        .Position = 1
    End With
    
    'Tot slot zetten we de handmatige update weer uit en mag Excel gaan rekenen


    Draaitabel.ManualUpdate = False


End Sub

Het resultaat

Als de brondata en de tabbladnamen in de Excel file zijn aangemaakt kan je de macro met de F5 toets in de VBA-editor starten waarna je eerste draaitabel een feit is.

 

Reinder.eu | onze draaitabel in Excel

Download hier het voorbeeld

Download hier het bron bestand van het blog

Download - File size: 23KB