Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Probeer onze RefTreeAnalyser
de beste Excel formule auditing tool.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Excel Tabellen (VBA)
This page in English

Werken Met tabellen in Excel 2013, 2010 en 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 2013, 2010 en 2007 is beschreven hoe u de tabel optie van deze Excel versies 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 2013, 2010 en 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 2013, 2010 en 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, 2010 of 2013:

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-2013 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 17AutoAanvullen 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 2013, 2010 en 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 en hoger. 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:

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 37 (Toon ze allemaal):

 


Comment by: Jan Karel Pieterse (3/31/2015 3:44:38 PM)

Hallo Martin,

Ik zou via VBA de betreffende kolom NA de refresh hernoemen. Met een formule gaat niet omdat tabelkopregels geen formule mogen bevatten.

 


Comment by: T.H. de Swart (5/30/2015 9:05:21 PM)

Ik wil middels een BOX een cel in een ander bestand selecteren en kopiëren naar het bestand waarin ik deze cel informatie zal gebruiken.
Hoe moet dit in VBA code?


gr. Teus de Swart

 


Comment by: Jan Karel Pieterse (6/1/2015 8:43:08 AM)

Hallo Teus,

Ik begrijp niet helemaal wat je bedoelt, maar kijk eens naar Application.FileDialog in VBA help.

 


Comment by: Ad (1/19/2016 1:53:25 PM)

Hoe kan ik een aantal kolommen selecteren op basis van de koptekst in deze kolommen, de kolom koppen bevatten allemaal een nummer.

Vervolgens wil ik de geselecteerde kolommen kopieeren naar een nieuw werkblad.

Ik krijg diversen excel CSV bestanden met iedere keer andere kolom benamingen en in andere volgorde het enige wat overeenkomt is dat bijvoorbeeld kolom "54" dezelfde soort informatie bevat.

 


Comment by: Jan Karel Pieterse (1/19/2016 2:52:55 PM)

Hoi Ad,

Kan je een voorbeeldregel uit je tekstbestand plaatsen? Liefst twee verschillende?

 


Comment by: Martin Wolf (4/27/2016 11:18:05 AM)

Ik zoek een VBA oplossing voor het volgende:
Op Blad1 heb ik een tabel1 staan en op Blad2 Tabel2. Nu wil ik een bepaalde regel in Tabel1 selecteren en onderaan toevoegen aan Tabel2. De Regel moet daarna ook verwijderd worden uit Tabel1.
Is daar een oplossing voor?

 


Comment by: Jan Karel Pieterse (4/29/2016 11:55:04 AM)

Hallo Martin,

Dat gaat als volgt:

    With Worksheets("Blad2").ListObjects("Tabel2").ListRows.Add
        .Range.Value = Intersect(Selection.EntireRow, Worksheets("Blad1").ListObjects("Tabel1").DataBodyRange).Value
    End With
    Intersect(Selection.EntireRow, Worksheets("Blad1").ListObjects("Tabel1").DataBodyRange).Delete

 


Comment by: Martin (4/29/2016 8:33:58 PM)

Dank Jan Karel,
Het werkt perfect!

 


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.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].