Een Excel-tabel — wat VBA een ListObject noemt — is een benoemd, zichzelf uitbreidend bereik met ingebouwde filterpijlen, afwisselend gekleurde rijen en gestructureerde kolomverwijzingen. Als je gegevens nog geen tabel zijn, selecteer dan een willekeurige cel erin en druk op Ctrl+T, of laat VBA er een maken met ListObjects.Add.

Een ListObject refereren

Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = ThisWorkbook.Worksheets("Reports")
Set tbl = ws.ListObjects("tblReports")
 
Debug.Print tbl.Range.Address        ' full table including header
Debug.Print tbl.DataBodyRange.Rows.Count   ' data rows only, no header

Het declareren van tbl As ListObject (in plaats van alleen As Range) ontsluit alle tabelspecifieke functies die in de rest van deze sectie worden gebruikt — ListRows, ListColumns en de Total Row zijn allemaal beschikbaar doordat het object correct getypt is. Let op het verschil tussen de twee Debug.Print-regels: tbl.Range omvat de hele tabel inclusief de koprij, terwijl tbl.DataBodyRange alleen de gegevens eronder bevat.

Bijna alles wat je doet — een rij toevoegen, een kolom optellen, door records lopen — zou gebruik moeten maken van DataBodyRange, juist omdat je niet wilt dat de koptekst per ongeluk als gegevensrij wordt behandeld.

Rijen toevoegen

ListRows.Add voegt een nieuwe rij direct onder de tabel toe — en belangrijker nog, alle gestructureerde-verwijzingsformules in andere kolommen worden automatisch uitgebreid naar deze rij, wat een van de grootste praktische voordelen is van een tabel ten opzichte van een gewoon bereik.

Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
 
newRow.Range(1, 1).Value = "April"
newRow.Range(1, 2).Value = "North"
newRow.Range(1, 3).Value = 45200
newRow.Range(1, 4).Value = 30750
newRow.Range(1, 5).Value = 14450
newRow.Range(1, 6).Value = 41000

tbl.ListRows.Add maakt de lege rij aan en geeft deze terug als een ListRow-object, waardoor de volgende zes regels naar newRow schrijven in plaats van naar tbl. newRow.Range(1, 1) betekent "rij 1 van deze specifieke nieuwe rij, kolom 1" — de indexering begint opnieuw bij 1 voor de nieuwe rij zelf, het telt dus niet vanaf de bovenkant van de hele tabel. Dit is een duidelijk betere gewoonte dan handmatig de laatste rij van het werkblad opzoeken met End(xlUp) en er een kolom naast schrijven: ListRows.Add plaatst de rij altijd correct binnen de grenzen van de tabel, zodat elke Total Row, gestructureerde-verwijzingsformule of voorwaardelijke opmaakregel die op de tabel is toegepast automatisch wordt uitgebreid.

Records bijwerken

Om een bestaande rij bij te werken, loop je door DataBodyRange en zoek je op een sleutelkolom — hier wordt het Target-bedrag van de regio Central in februari bijgewerkt na een budgetherziening:

Dim r As Long
For r = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange.Cells(r, 1).Value = "February" And _
       tbl.DataBodyRange.Cells(r, 2).Value = "Central" Then
        tbl.DataBodyRange.Cells(r, 6).Value = 52000   ' revised Target
        Exit For
    End If
Next r

Dit is hetzelfde van boven naar beneden, stoppen bij de eerste match-patroon als bij voorwaardelijke logica, toegepast op echte rijen in plaats van hardgecodeerde waarden: de lus controleert Maand en Regio samen met And, en zodra beide overeenkomen, wordt de Target-kolom bijgewerkt en wordt Exit For aangeroepen zodat de resterende rijen niet onnodig worden doorzocht. Door tbl.DataBodyRange.Cells(r, 1) te gebruiken in plaats van een werkbladniveau Cells-verwijzing blijft de rijnummering beperkt tot de gegevens van de tabel — rij 1 betekent hier de eerste gegevensrij, ongeacht op welke fysieke werkbladrij de tabel begint.

Tabelkolommen refereren

Gestructureerde verwijzingen — ListColumns("Name") — zijn leesbaarder en robuuster dan kolommen tellen op nummer, vooral als een tabel wordt bewerkt en kolommen verschuiven:

Dim profitCol As Range
Set profitCol = tbl.ListColumns("Profit").DataBodyRange
 
Debug.Print Application.WorksheetFunction.Sum(profitCol)
Debug.Print Application.WorksheetFunction.Average(profitCol)

ListColumns("Profit") zoekt de kolom op basis van de koptekst in plaats van op positie, zodat de code blijft werken, zelfs als Profit later van kolom E naar kolom F verschuift — handmatig Cells(r, 5) tellen zou in dat geval ongemerkt fout gaan. Application.WorksheetFunction is de brug waarmee VBA gewone Excel-functies zoals SUM en AVERAGE direct op een Range-object kan toepassen, in plaats van dat je een handmatige lus met een lopend totaal moet schrijven, wat zowel minder code is als minder kans op een telfout.

Taak

1. Open Section_4_Reports.xlsx, sla deze op als Section_4_Reports.xlsm en controleer of de gegevens op het tabblad Reports een tabel zijn met de naam tblReports (klik op een willekeurige cel in de tabel — het tabblad Tabelontwerp zou moeten verschijnen).
2. Schrijf een macro die voor elk van de vijf regio's een rij voor april toevoegt met ListRows.Add (in totaal vijf nieuwe rijen, verzonnen cijfers zijn prima).
3. Schrijf een tweede macro die met ListColumns("Sales").DataBodyRange en WorksheetFunction.Sum de totale Sales over alle rijen afdrukt in het Direct Venster.