Werken Met tabellen in Excel (VBA)

Inleiding

In Werken Met tabellen in Excel is beschreven hoe u de tabel optie van Excel 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 is simpel:

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

De opmaak van de tabel wordt bepaald door 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()
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

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.

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
        .ListColumns(1).DataBodyRange.Select
        'Selecteer rij 4(kopregel telt niet mee)
        .ListRows(4).Range.Select
    End With

    '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 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.

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)
'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

Het is niet direct duidelijk waar in de gebruikersinterface u het commentaar van een tabel 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:

AutoAanvullen toont commentaar

In VBA is de syntax:

Sub CommentaarBijTabel()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '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:

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 tabellen 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.

Sub SorterenEnFilteren()
    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
    '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.

Formattering verwijderen van een tabel

Stel dat je net een bereik naar een tabel hebt omgezet, maar het oorspronkelijke bereik had je voorzien van allerlei opmaak zoals randen en opvulkleuren. Tabellen hebben hun eigen tabel stijlen, maar die overschrijven formattering die je zelf hebt gedaan niet. Wat je kunt doen is de Standaard stijl (Zie dit artikel) toepassen op de tabel, maar dat zorgt ervoor dat al je getalsopmaak verdwijnt. Onderstaand macrootje maakt eerst een nieuwe stijl en zet het getalsopmaak gedeelte van die stijl uit. Als die stijl op de tabel wordt toegepast behoud je de getalsopmaak.

Sub RemoveFormattingOfTable()
    Dim oStNormalNoNum As Style
    On Error Resume Next
    Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
    On Error GoTo 0
    If oStNormalNoNum Is Nothing Then
        ActiveWorkbook.Styles.Add "NormalNoNum"
        Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
        oStNormalNoNum.IncludeNumber = False
    End If
    With ActiveSheet.ListObjects(1)
        .Range.Style = "NormalNoNum"
        'Now apply tablestyle:
        .TableStyle = "TableStyleLight1"
    End With
    ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

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

Laatste 8 commentaren van in totaal 49 (Toon ze allemaal):

Commentaar van: Hoogeboom (5-12-2017 18:44:09) deeplink naar dit commentaar

Hallo Jan Karel,

Sorry, mijn fout, ik wil filteren.

Ik dacht dat er voor een tabel in Excel andere opdrachten beschikbaar zijn. Wat ik eigenlijk wil bereiken:
Ik heb een muziek cd tabel in Excel met o.a. de kolommen Artiest, cdnaam en titel, ik wil drie listboxen vullen met unieke waarden uit de genoemde kolommen en vervolgens filteren op een waarde uit een van de listboxen op een ander weekblad.

Groetjes,

Karel

Commentaar van: Jan Karel Pieterse (6-12-2017 14:14:04) deeplink naar dit commentaar

Hoi Karel,

Heb je gekeken naar Slicers?

Commentaar van: Hoogeboom (6-12-2017 16:25:07) deeplink naar dit commentaar

Ik zal er naar kijken,

Bedankt,

Karel

Commentaar van: Ad van Lieshout (24-10-2018 13:26:25) deeplink naar dit commentaar

Ik heb onderstaande VBA code gevonden op het internet en deze werkt perfect in een normale sheet van excel 2007.
Ik wil deze code ook gaan gebruiken in mijn adressenbestand in excel 2007. Ik heb het adressenbestand opgemaakt met een tabel maar nu werkt het maar gedeeltelijk. Kleine letters worden wel hoofdletters maar als ik b.v. intik b.v. 3406ad dan worden de letters ad AD alleen de spatie plaats hij niet. Moet er met tabellen iets aan de VBA code toegevoegd worden? Graag even uw advies c.q. een oplossing.

Alvast bedankt voor uw medewerking.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
'de macro start enkel voor kolom I
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
If Len(Target) = 6 Then
If Target Like "####[A-Z][A-Z]" Then _
Target = Left(Target, 4) & " " & UCase(Right(Target, 2))
End If
End Sub

Commentaar van: Jan Karel Pieterse (24-10-2018 13:45:50) deeplink naar dit commentaar

Hoi Ad,

Werkt dit beter?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    If Len(Target) = 6 Then
        If Target Like "####[a-zA-Z][a-zA-Z]" Then _
         Target = Left(Target, 4) & " " & UCase(Right(Target, 2))
    End If
End Sub

De term Option Compare Text kan je weglaten

Commentaar van: Ad van Lieshout (24-10-2018 14:02:57) deeplink naar dit commentaar

Hallo Jan Karel,

Ik heb het veranderd zoals jij hebt aangegeven incl. het weghalen van de term Option Compare Text maar er veranderd niets, kleine letters zet hij om in hoofdletters alleen zet hij de spatie niet.
Als ik buiten de tabel de Range opgeef dan doet hij het perfect, dus ik denk dat het iets te maken heeft met de opgemaakte tabel.

Maar alvast bedankt voor je snelle reactie.

Commentaar van: Jan Karel Pieterse (24-10-2018 14:22:52) deeplink naar dit commentaar

Hoi Ad,

Bij mij werkte het prima. Kan je je bestand even mailen?

Commentaar van: Gerwin (20-12-2019 15:41:00) deeplink naar dit commentaar

Formule Index gebruiken, alleen vul je ipv rij kolom in en bij kolom idem dito.

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: excelexperts.nl/forum/index.php.