En Excel-tabell — det som VBA kaller et ListObject — er et navngitt, selvutvidende område med innebygde filterpiler, stripete rader og strukturerte kolonnereferanser. Hvis dataene dine ikke allerede er en tabell, velg en hvilken som helst celle i området og trykk Ctrl+T, eller la VBA opprette en ved å bruke ListObjects.Add.

Referere til et ListObject

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

Å deklarere tbl As ListObject (i stedet for bare As Range) er det som gir tilgang til alle tabellspesifikke funksjoner som brukes videre i denne seksjonen — ListRows, ListColumns og Total Row kommer alle fra at objektet er riktig typet. Merk forskjellen mellom de to Debug.Print-linjene: tbl.Range dekker hele tabellen inkludert overskriftsraden, mens tbl.DataBodyRange kun dekker dataene under overskriften.

Nesten alt du gjør — legge til en rad, summere en kolonne, løkke gjennom poster — bør bruke DataBodyRange, nettopp fordi du ikke vil at overskriftsteksten skal bli behandlet som en datarad ved en feil.

Legge til rader

ListRows.Add legger til en ny rad rett under tabellen — og viktigst av alt, eventuelle strukturerte referanseformler i andre kolonner utvides automatisk til den nye raden, noe som er en av de største praktiske fordelene en tabell har over et vanlig område.

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 oppretter den tomme raden og returnerer den som et ListRow-objekt, og derfor skrives de neste seks linjene til newRow i stedet for tilbake til tbl. newRow.Range(1, 1) betyr "rad 1 i denne spesifikke nye raden, kolonne 1" — indekseringen starter på nytt for selve den nye raden, den teller ikke fra toppen av hele tabellen. Dette er en betydelig bedre vane enn å finne arkets siste rad med End(xlUp) og skrive én kolonne forbi manuelt: ListRows.Add havner alltid riktig innenfor tabellens grenser, slik at eventuelle Total Row, strukturerte referanseformler eller betingede formateringsregler som er brukt på tabellen automatisk utvides til å inkludere den nye raden.

Oppdatere poster

For å oppdatere en eksisterende rad, løkk gjennom DataBodyRange og match på en nøkkelkolonne — her oppdateres Target for Central-regionen i februar etter en budsjettendring:

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

Dette er det samme topp-til-bunn, stopp-ved-første-treff-mønsteret fra betinget logikk, brukt på faktiske rader i stedet for hardkodede verdier: løkken sjekker både måned og region med And, og i det øyeblikket begge matcher, oppdateres Target-kolonnen og Exit For kalles slik at den ikke fortsetter å skanne de resterende radene unødvendig. Ved å bruke tbl.DataBodyRange.Cells(r, 1) i stedet for en Cells-referanse på arknivå, holdes radnummereringen innenfor tabellens egne data — rad 1 her betyr første datarad, uavhengig av hvilken fysisk rad tabellen starter på i arket.

Referere til tabellkolonner

Strukturerte referanser — ListColumns("Name") — er mer lesbare og mer robuste enn å telle kolonner med nummer, spesielt når en tabell blir redigert og kolonner flyttes:

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

ListColumns("Profit") finner kolonnen etter overskriftsteksten i stedet for posisjonsnummer, slik at koden fortsatt fungerer selv om Profit senere flyttes fra kolonne E til kolonne F — å telle Cells(r, 5) manuelt ville i så fall stilletiende feile. Application.WorksheetFunction er broen som lar VBA bruke vanlige Excel-funksjoner som SUM og AVERAGE direkte på et Range-objekt, i stedet for at du må skrive en manuell løkke med løpende total, noe som både gir mindre kode og mindre risiko for avvik i tellingen.

Oppgave

1. Åpne Section_4_Reports.xlsx, lagre den som Section_4_Reports.xlsm, og bekreft at dataene på rapportarket er en tabell med navnet tblReports (klikk på en hvilken som helst celle i tabellen — fanen Tabellutforming skal vises).
2. Skriv en makro som legger til en rad for april for hver av de fem regionene ved å bruke ListRows.Add (totalt fem nye rader, bruk gjerne oppdiktede tall).
3. Skriv en annen makro som bruker ListColumns("Sales").DataBodyRange og WorksheetFunction.Sum for å skrive ut totalomsetningen for alle rader til Direktevinduet.