Arbeta med Excel-tabeller

En Excel-tabell — som VBA kallar ett ListObject — är ett namngivet, självexpanderande område med inbyggda filterpilar, bandade rader och strukturerade kolumnreferenser. Om dina data inte redan är en tabell, markera en cell i området och tryck på Ctrl+T, eller låt VBA skapa en med ListObjects.Add.

Referera till ett 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

Att deklarera tbl As ListObject (istället för bara As Range) är det som låser upp alla tabellspecifika funktioner som används i resten av detta avsnitt — ListRows, ListColumns och Total Row kommer alla från att objektet är korrekt typat. Notera skillnaden mellan de två Debug.Print-raderna: tbl.Range omfattar hela tabellen inklusive rubrikraden, medan tbl.DataBodyRange endast omfattar data under rubriken.

Nästan allt du gör — lägga till en rad, summera en kolumn, loopa genom poster — bör använda DataBodyRange, just för att du inte vill att rubriktexten av misstag behandlas som en datarad.

Lägga till rader

ListRows.Add lägger till en ny rad direkt under tabellen — och viktigt, alla strukturerade referensformler i andra kolumner utökas automatiskt till den, vilket är en av de största praktiska fördelarna en tabell har jämfört med ett vanligt 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 skapar den tomma raden och returnerar den som ett ListRow-objekt, vilket är anledningen till att de sex följande raderna skriver till newRow istället för tillbaka till tbl. newRow.Range(1, 1) betyder "rad 1 i just denna nya rad, kolumn 1" — indexeringen börjar om på 1 för den nya raden, den räknar inte från toppen av hela tabellen. Detta är en betydligt bättre vana än att hitta bladets sista rad med End(xlUp) och skriva en kolumn förbi den manuellt: ListRows.Add hamnar alltid korrekt inom tabellens gräns, så alla totalrader, strukturerade referensformler eller villkorsstyrda formateringsregler som tillämpas på tabellen utökas automatiskt för att inkludera den.

Uppdatera poster

För att uppdatera en befintlig rad, loopa genom DataBodyRange och matcha på en nyckelkolumn — här uppdateras Central-regionens Target för februari efter en budgetändring:

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

Detta är samma uppifrån-och-ner, stoppa-vid-första-träff-mönster som används vid villkorslogik, tillämpat på riktiga rader istället för hårdkodade värden: loopen kontrollerar Månad och Region tillsammans med And, och så fort båda matchar uppdateras Target-kolumnen och Exit For anropas så att den inte fortsätter att söka igenom resterande rader i onödan. Genom att använda tbl.DataBodyRange.Cells(r, 1) istället för ett arbetsbladsnivå Cells-referens hålls radnumreringen inom tabellens egna data — rad 1 här betyder första dataraden, oavsett vilken fysisk rad tabellen råkar börja på i arbetsbladet.

Referera till tabellkolumner

Strukturerade referenser — ListColumns("Name") — är mer läsbara och mer robusta än att räkna kolumner med nummer, särskilt när en tabell redigeras och kolumner flyttas:

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

ListColumns("Profit") hittar kolumnen via dess rubriktext istället för genom att räkna position, så koden fortsätter fungera även om Profit senare flyttas från kolumn E till kolumn F — att manuellt räkna Cells(r, 5) skulle tyst sluta fungera i det scenariot. Application.WorksheetFunction är bryggan som låter VBA anropa vanliga Excel-funktioner som SUM och AVERAGE direkt mot ett Range-objekt, istället för att du skriver en manuell loop med en löpande summa, vilket både är mindre kod och mindre risk för räknefel.

Uppgift

1. Öppna Section_4_Reports.xlsx, spara den som Section_4_Reports.xlsm och bekräfta att data på bladet Reports är en tabell med namnet tblReports (klicka i valfri cell i tabellen — fliken Tabellverktyg ska visas).
2. Skriv en makro som lägger till en rad för april för var och en av de fem regionerna med hjälp av ListRows.Add (totalt fem nya rader, påhittade siffror går bra).
3. Skriv en andra makro som använder ListColumns("Sales").DataBodyRange och WorksheetFunction.Sum för att skriva ut den totala försäljningen för alla rader till Direktfönstret.