Werken Met tabellen in Excel 2007 (VBA)

Dit artikel is ook op Microsoft Office Online gepubliceerd:

Working with Excel tables in Visual Basic for Applications (VBA)

Inleiding

In Werken Met tabellen in Excel 2007 is beschreven hoe u de tabel optie van Excel 2007 gebruikt. Deze pagina beschrijft hoe u met VBA werkt met tabellen.

Het is gewoon een ListObject…

Aan de VBA kant lijkt er niets nieuw aan de nieuwe tabellen. Ze worden als ListObjects aangeduid, een collectie die is geïntroduceerd met Excel 2003. Maar er zijn significante wijzigingen in dit deel van het objectmodel en daarvan zal een deel hier worden getoond.

Een bereik tot tabel maken

Het converteren van een bereik naar een tabel, begint met dezelfde code als in Excel 2003:

Sub MaakTabel()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), _
                                , xlYes).Name = "Tabel1"
    'Werkt niet in 2003
    ActiveSheet.ListObjects("Tabel1").TabelStyle = "TabelStyleLight2"
End Sub

Maar het nieuws is direct hierboven al zichtbaar: TabelStyles (tabel stijlen). Een collectie objecten welke deel uitmaken van het Workbook object. Omdat tabelstijlen dus niet onderdeel van de Excel applicatie zijn, gebeuren er dingen die u misschien niet zou verwachten. U kunt onderdelen van een tabelstijl bijvoorbeeld als volgt veranderen:

Sub ChangeTabelStyles()
    'Werkt niet in 2003
    ActiveWorkbook.TabelStyles(2).TabelStyleElements(xlWholeTabel) _
        .Borders(xlEdgeBottom).LineStyle = xlDash
End Sub

Hiermee verandert de lijnstijl van de onderkant van uw tabel in gestreept. Maar let op! Alle tabellen in alle open Excel bestanden met diezelfde tabelstijl veranderen mee. Maar zodra u het bestand opslaat (en de andere bestanden eventueel ook), Excel sluit en het bestand weer opent, dan zijn deze wijzigingen verdwenen! Dit komt omdat de code hierboven een ingebouwde tabelstijl heeft veranderd. Vreemd genoeg is dit dus wel (tijdelijk) toegestaan, ondanks dat de wijzigingen dus niet worden opgeslagen!

Wilt u volledige controle over uw tabelstijlen, dan dient u een bestaande tabelstijl te dupliceren en deze (nu "aangepaste" stijl) te modificeren. Dergelijke aangepaste stijlen maken wél deel uit van het bestand en wijzigingen eraan worden wel opgeslagen.

Alle tabellen tonen

Laten we eens beginnen met het tonen van alle tabellen die in een werkblad te vinden zijn:

Sub VindAlleTabellenOpBlad()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Tabel gevonden: " & oLo.Name & ", " _
                & oLo.Range.Address
    Next
End Sub

Dit stukje code werkt exact hetzelfde in Excel 2003, dus hier is niets nieuws onder de zon (tenminste, behalve dan dat in de gebruikersinterface die ListObjects in Excel 2007 dus "Tabellen" worden genoemd).

Delen van een tabel selecteren

Zeer waarschijnlijk wilt u via VBA wel eens met een specifiek deel van een tabel aan het werk gaan. Hieronder staat een serie voorbeelden die laten zien hoe dat werkt. Het commentaar in de code geeft aan welke delen in Excel 2003 niet werken.

Sub DeelVanTabelSelecteren()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '1: Met het listobject
    With oSh.ListObjects("Tabel1")
        'Toon de naam van uw tabel
        MsgBox .Name
        'Selecteer hele tabel
        .Range.Select
        'Select alleen gegevens van hele tabel
        '(niet de koprij)
        .DataBodyRange.Select
        'Selecteer derde kolom
        .ListColumns(3).Range.Select
        'Selecteer alleen data in eerste kolom
        'Werkt niet in 2003
        .ListColumns(1).DataBodyRange.Select
        'Selecteer rij 4(kopregel telt niet mee)
        .ListRows(4).Range.Select
    End With

    'Werkt niet in 2003
    '2: Met het range object
    'selecteer hele kolom (alleen data)
    oSh.Range("Tabel1[Column2]").Select
    'selecteer hele kolom(data plus koprij)
    oSh.Range("Tabel1[[#All],[Column1]]").Select
    'Selecteer hele tabel (alleen data)
    oSh.Range("Tabel1").Select
    'selecteer gehele tabel
    oSh.Range("Tabel1[#All]").Select
    'Selecteer één rij
    oSh.Range("A5:F5").Select
End Sub

Misschien is het u opgevallen, dat Excel 2007 tabellen ook behandelt alsof het bereiknamen betreft. Dit is precies wat er ook aan de hand is. Zodra u een bereik omzet in een tabel voegt Excel automatisch een bereiknaam toe die de hele tabel omvat. Deze bereiknamen worden door Excel wel op een bijzondere manier behandeld. Zo kunt u bereiknamen die horen bij een tabel niet verwijderen en worden ze automatisch hernoemd als u de naam van een tabel aanpast. Bij verwijderen van een tabel verdwijnt ook de bijbehorende bereiknaam.

QueryTabellen

Een andere verandering is de Querytables collectie. In oudere versies van Excel maakt deze collectie deel uit van de objecten onder het "Worksheet" object. Dit is niet langer het geval. Onderstaand code zal dan ook niet werken in Excel 2007:

Sub QueryTableDemo()
     Dim oQT As QueryTable
     For Each oQT In ActiveSheet.QueryTables
         MsgBox oQT.Connection
     Next
End Sub

De manier om dit in Excel 2007 te doen is:

Sub QueryTableDemo()
     Dim oLo As ListObject
     For Each oLo In ActiveSheet.ListObjects
         If oLo.SourceType = xlSrcQuery Then
             MsgBox oLo.QueryTable.Connection
         End If
     Next
End Sub

Rijen en kolommen invoegen

Een onderdeel waarbij het grootste deel van de functionaliteit al bestond in Excel 2003. Er is een beperkt aantal dingen nieuw, waaronder het "AlwaysInsert" argument bij de ListRows.Add methode:

Sub TabelInvoegVoorbeelden()
'Kolom invoegen op een specifieke plek
    Selection.ListObject.ListColumns.Add Position:=4
'Kolom invoegen rechts van huidige
    Selection.ListObject.ListColumns.Add
'Rij invoegen boven huidige
    Selection.ListObject.ListRows.Add (11)
'Werkt niet in 2003
'Rij invoegen onder huidige (kan alleen onderin de tabel)
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
End Sub

Als je vervolgens iets moet doen met de nieuwe rij, maak dan een object variabele die naar de nieuwe rij verwijst:

Dim oNewRow As ListRow
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)

Wil je vervolgens iets in de eerste cel van de nieuwe rij schrijven, dan gaat dat als volgt:

oNewRow .Range.Cells(1,1).Value="Waarde voor nieuwe cel"

Commentaar toevoegen aan een tabel

Dit is iets dat in Excel 2003 nog niet mogelijk was. Het is direct gekoppeld aan het gegeven dat een tabel gekoppeld is aan een bereiknaam. Het is niet direct duidelijk waar in de gebruikersinterface u dit commentaar zou moeten invoegen of wijzigen, omdat dit in de optie Namen Beheren op de tab "Formules" van het lint moet worden gedaan. Het is wel een erg handige optie, omdat dit commentaar zichtbaar wordt gemaakt op het moment dat u een formule invoert, zie figuur 17 AutoAanvullen toont commentaar
Figuur 17: commentaar wordt getoond bij formule auto-aanvullen.

In VBA is de syntax:

Sub CommentaarBijTabel()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Werkt niet in 2003
    'Voeg commentaar toe aan een tabel
    '(wordt dus gekoppeld aan de bijbehorende bereiknaam)
    oSh.ListObjects("Tabel_NorthWind").Comment = _
    "Deze tabel bevat gegevens uit de NorthWind database"
End Sub
Een tabel omzetten in een normaal bereik

Een tabel omzetten in een normaal bereik

De code hiervoor is erg eenvoudig en kent dezelfde syntax als in Excel 2003:

Sub VerwijderTabel()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Maak tabel gewoon bereik
    oSh.ListObjects("Tabel1").Unlist
End Sub

Speciale functies: Sorteren en filteren

Met Excel 2007 krijgen we een hele nieuwe set filter- en sorteer mogelijkheden. Hieronder wordt daarvan slechts een tipje van de sluier opgelicht. Wilt u meer VBA code zien, dan kunt u het beste de macro recorder inschakelen en uw acties uitvoeren. Mogelijkheden omvatten onder andere: het Sorteren op kleur (handig als u bepaalde cellen van uw tabel even een kleurtje heeft gegeven om later iets mee te doen), filteren op de kleur van de tekst etcetera. De onderstaande code werkt uiteraard alleen in Excel 2007. De lijst optie in Excel 2003 kent slechts de standaard autofilter en sorteer mogelijkheden zoals die in essentie al in Excel 5 aanwezig waren.

Sub SorterenEnFilteren()
'Werkt niet in 2003
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Tabel1")
        'Vorige sorteeropties verwijderen
        .Sort.SortFields.Clear
        'Sortering op celkleur instellen
        .Sort.SortFields.Add( _
                Range("Tabel1[[#All],[Column2]]"), _
                xlSortOnCellColor, xlAscending, , _
                xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
        With .Sort
            'Andere sorteeropties instellen
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            'Sorteren
            .Apply
        End With
    End With
    'Alleen de oude autofilter dingen werken in Excel 2003
    'Filteren op de kleur van de tekst
    ActiveSheet.ListObjects("Tabel1").Range.AutoFilter Field:=2, _
        Criteria1:=RGB(156, 0, 6), Operator:=xlFilterFontColor
End Sub

De formattering van een cel in de tabel opvragen

Je vraagt je wellicht af waarom deze paragraaf hier staat, je kan toch gewoon cell.Interior.ThemeColor gebruiken om de ThemeColor van een cel in de tabel te bekijken? Nee, helaas niet. Omdat de formattering van een cel in een tabel volledig bepaald wordt door de tabel stijl en de plaats in de tabel moet je deze beide gegevens opvragen en combineren om het juiste formatteringsgedeeldte uit de tabel stijl te halen:

Bepaal waar in de tabel de cel zich bevindt (op eerste rij, in eerste kolom, op totaal rij,...)
Bepaal de tabel instellingen: Staat rij strepen aan, is de eerste rij een koprij, ...
Gebaseerd op deze informatie kan het juiste TableStyleElement worden opgevraagd uit de tabel stijl.

De functie hieronder geeft een TableStyleElement terug dat hoort bij een cel oCell in een tabel oLo:

Function GetStyleElementFromTableCell(oCell As Range, oLo As ListObject) As TableStyleElement
'-------------------------------------------------------------------------
' Procedure : GetStyleElementFromTableCell
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 2-6-2009
' Purpose   : Function to return the proper style element from a cell inside a table
'-------------------------------------------------------------------------
    Dim lRow As Long
    Dim lCol As Long
    'Determine on what row we are inside the table
    lRow = oCell.Row - oLo.DataBodyRange.Cells(1, 1).Row
    lCol = oCell.Column - oLo.DataBodyRange.Cells(1, 1).Column

    With oLo
        If lRow < 0 And .ShowHeaders Then
            'on first row and has header
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlHeaderRow)
        ElseIf .ShowTableStyleFirstColumn And lCol = 0 Then
            'On first column and has first column style
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlFirstColumn)
        ElseIf .ShowTableStyleLastColumn And lCol = oLo.Range.Columns.Count - 1 Then
            'On last column and has last col style
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlLastColumn)
        ElseIf lRow = .DataBodyRange.Rows.Count And .ShowTotals Then
            'On last row and has total row
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlTotalRow)
        Else
            If .ShowTableStyleColumnStripes And Not .ShowTableStyleRowStripes Then
                'in table, has column stripes
                If lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            ElseIf .ShowTableStyleRowStripes And Not .ShowTableStyleColumnStripes Then
                'in table, has column stripes
                If lRow Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            ElseIf .ShowTableStyleColumnStripes And .ShowTableStyleRowStripes Then
                If lRow Mod 2 = 0 And lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                ElseIf lRow Mod 2 <> 0 And lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
                ElseIf lRow Mod 2 = 0 And lCol Mod 2 <> 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            End If
        End If
    End With

End Function

Je kan deze functie als volgt gebruiken:

Sub test()
    Dim oLo As ListObject
    Dim oTSt As TableStyleElement
    Set oLo = ActiveSheet.ListObjects(1)
    Set oTSt = GetStyleElementFromTableCell(ActiveCell, oLo)
    With ActiveCell.Offset(, 8)
        .Interior.ThemeColor = oTSt.Interior.ThemeColor
        .Interior.TintAndShade = oTSt.Interior.TintAndShade
    End With
End Sub

Merk op, dat de functie geen rekening houdt met het gegeven dat je bij een tabel in kan stellen dat er meer dan 1 rij en/of kolom dezelfde formattering moet krijgen, dus bijvoorbeeld dat telkens 2 rijen donker zijn en dan weer twee rijen licht.

Conclusie

Natuurlijk is er meer te weten over tabellen en lijsten als het gaat om het objectmodel in VBA. Een hele goede manier om hierover meer te weten te komen is door macro’s op te nemen tijdens het doen van allerlei aanpassingen aan uw tabel.

Vragen, suggesties en opmerkingen

Al het commentaar over deze pagina:

Comment by: Marc Bittremieux (9/5/2009 1:05:46 AM)

Ik zoek een mogelijkheid in excel (2007) om rijen en kolommen te transponeren. Maar niet op de klassieke wijze.
Graag wil ik een oorsprongtabel transponeren, met behoud van de oorsprongtabel, waarbij latere wijzigingen in de oorsprongtabel ook automatisch in de getransponeerde tabel wijzigen.
De cellen in de getransponeerde tabel moeten dus geen 'waarden' zijn, maar verwijzingen naar de oorsprongtabel.

Wie heeft hiervoor een vlotte werkwijze?

Marc

Comment by: Jan Karel Pieterse (9/7/2009 8:53:41 AM)

Hallo Marc,

Je zou met behulp van geneste functies kunnen werken, waarbij je de functie VERSCHUIVING combineert met de functie VERGELIJKEN. De vergelijken functie gebruik je dan om de juiste rij en kolom op te zoeken in je brongegevens.

Maar ook een draaitabel zou eventueel geschikt kunnen zijn, dit hangt af van de indeling van je gegevens.

Comment by: Wim Lucassen (6/22/2010 8:07:04 AM)

Is het mogelijk om in een tabel de totaalrij te tonen en er daarna berekeningen aan toe te voegen, maar niet aan al de cellen. Bijvoorbeeld vanaf kolom "K" t/m kolom "AA" terwijl de tabel begint in de kolom "A".
Alvast hartelijk dank
Groet Wim Lucassen

Comment by: Jan Karel Pieterse (6/24/2010 2:30:08 AM)

Hallo Wim,

Ik denk dat je dan de tabel moet uitbreiden met die kolommen, de totaalrij moet namelijk per definitie deel uitmaken van de tabel.

Comment by: Piet Meulendijks (7/1/2010 3:38:48 AM)

dag heer Pieterse,

ik heb een regel code die 2007 wel werkt en in 2003 niet:

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

hebt u een suggestie om dit in 2003 te laten werken (EN in 2007)

bij voorbaat dank,
Piet Meulendijks, Son

Comment by: Jan Karel Pieterse (7/1/2010 4:31:31 AM)

Hallo Piet,

Deze optie is nieuw in 2007 en kan dus in 2003 niet worden gebruikt. Bovendien kent 2007 meer sorteervelden dan 2003. OM een sorteer functie in een macro te krijgen die zowel in 2003 als in 2007 werkt, kan je het beste de functie opnemen met een macro in 2003 en dan die code aanpassen.

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: www.eileenslounge.com.