Arbejde med Excel-tabeller

En Excel-tabel — det som VBA kalder et ListObject — er et navngivet, selvudvidende område med indbyggede filterpile, båndede rækker og strukturerede kolonnereferencer. Hvis dine data ikke allerede er en tabel, skal du vælge en vilkårlig celle i området og trykke på Ctrl+T, eller lade VBA oprette en ved hjælp af ListObjects.Add.

Referencer 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

Deklarering af tbl As ListObject (i stedet for blot As Range) er det, der låser op for alle tabelspecifikke funktioner, der bruges i resten af dette afsnit — ListRows, ListColumns og Total Row stammer alle fra, at objektet er korrekt typet. Bemærk forskellen mellem de to Debug.Print-linjer: tbl.Range dækker hele tabellen inklusive overskriftsrækken, mens tbl.DataBodyRange kun dækker dataene nedenunder.

Næsten alt, hvad du foretager dig — tilføjelse af en række, summering af en kolonne, gennemløb af poster — bør bruge DataBodyRange, netop fordi du ikke ønsker, at overskriftsteksten ved en fejl bliver behandlet som en datarække.

Tilføjelse af rækker

ListRows.Add tilføjer en ny række direkte under tabellen — og vigtigt, alle strukturerede-referencer formler i andre kolonner udvides automatisk til den, hvilket er en af de største praktiske fordele ved en tabel frem for et almindeligt 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 opretter den tomme række og returnerer den som et ListRow-objekt, hvilket er grunden til, at de næste seks linjer skriver til newRow i stedet for tilbage til tbl. newRow.Range(1, 1) betyder "række 1 i denne specifikke nye række, kolonne 1" — indekseringen starter forfra ved 1 for selve den nye række, den tæller ikke fra toppen af hele tabellen. Dette er en væsentligt bedre vane end at finde arkets sidste række med End(xlUp) og skrive én kolonne forbi den manuelt: ListRows.Add placerer altid korrekt inden for tabelgrænsen, så enhver Total Row, struktureret-referencer formel eller betinget formatering, der er anvendt på tabellen, automatisk udvides til at inkludere den.

Opdatering af poster

For at opdatere en eksisterende række, gennemløb DataBodyRange og match på en nøglekolonne — her opdateres Central-regionens Target for februar efter en budgetrevision:

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 top-til-bund, stop-ved-første-match mønster fra betinget logik, anvendt på rigtige rækker i stedet for hardkodede værdier: løkken tjekker Måned og Region sammen med And, og i det øjeblik begge matcher, opdateres Target-kolonnen og Exit For kaldes, så den ikke fortsætter med at gennemgå de resterende rækker unødigt. Ved at bruge tbl.DataBodyRange.Cells(r, 1) i stedet for et regnearksniveau Cells-reference holdes rækkenummereringen inden for tabellens egne data — række 1 her betyder den første datarække, uanset hvilken fysisk regnearksrække tabellen starter på.

Referencer til tabelkolonner

Strukturerede referencer — ListColumns("Name") — er mere læsbare og mere robuste end at tælle kolonner efter nummer, især når en tabel bliver redigeret 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") finder kolonnen ud fra dens overskriftstekst i stedet for at tælle position, så koden fortsætter med at virke, selvom Profit senere flyttes fra kolonne E til kolonne F — at tælle Cells(r, 5) manuelt ville i det tilfælde bryde koden uden varsel. Application.WorksheetFunction er broen, der lader VBA kalde almindelige Excel-funktioner som SUM og AVERAGE direkte på et Range-objekt, i stedet for at du skriver en manuel løkke med en løbende sum, hvilket både er mindre kode og mindre tilbøjeligt til at indeholde en off-by-one-fejl.

Opgave

1. Åbn Section_4_Reports.xlsx, gem den som Section_4_Reports.xlsm, og bekræft at dataene på fanen Reports er en tabel med navnet tblReports (klik på en vilkårlig celle i tabellen — fanen Tabeldesign bør vises).
2. Skriv et makro, der tilføjer en april-række for hver af de fem regioner ved hjælp af ListRows.Add (i alt fem nye rækker, opfundne tal er fine).
3. Skriv et andet makro, der bruger ListColumns("Sales").DataBodyRange og WorksheetFunction.Sum til at udskrive det samlede salg for alle rækker til Direkte vindue.