Arbeiten mit Excel-Tabellen

Eine Excel-Tabelle — von VBA als ListObject bezeichnet — ist ein benannter, sich selbst erweiternder Bereich mit integrierten Filterpfeilen, abwechselnd formatierten Zeilen und strukturierten Spaltenverweisen. Falls Ihre Daten noch keine Tabelle sind, wählen Sie eine beliebige Zelle darin aus und drücken Sie Ctrl+T, oder lassen Sie VBA eine Tabelle mit ListObjects.Add erstellen.

Verweis auf ein 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

Die Deklaration von tbl As ListObject (statt nur As Range) ermöglicht den Zugriff auf alle tabellenspezifischen Funktionen, die im weiteren Verlauf dieses Abschnitts verwendet werden — ListRows, ListColumns und die Total Row sind nur verfügbar, wenn das Objekt korrekt typisiert ist. Beachten Sie den Unterschied zwischen den beiden Debug.Print-Zeilen: tbl.Range umfasst die gesamte Tabelle inklusive Kopfzeile, während sich tbl.DataBodyRange nur auf die darunterliegenden Daten bezieht.

Nahezu alle Aktionen — Zeile hinzufügen, Spalte summieren, Datensätze durchlaufen — sollten mit DataBodyRange erfolgen, damit die Kopfzeile nicht versehentlich als Datenzeile behandelt wird.

Zeilen hinzufügen

ListRows.Add fügt eine neue Zeile direkt unterhalb der Tabelle hinzu — und das Entscheidende: Alle strukturierten Verweisformeln in anderen Spalten werden automatisch auf die neue Zeile erweitert, was einen der größten praktischen Vorteile einer Tabelle gegenüber einem normalen Bereich darstellt.

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 erstellt die leere Zeile und gibt sie als ListRow-Objekt zurück, weshalb die nächsten sechs Zeilen auf newRow und nicht auf tbl schreiben. newRow.Range(1, 1) bedeutet "Zeile 1 dieser spezifischen neuen Zeile, Spalte 1" — die Indizierung beginnt für die neue Zeile selbst wieder bei 1 und zählt nicht vom Anfang der gesamten Tabelle. Dies ist eine deutlich bessere Vorgehensweise, als die letzte Zeile des Arbeitsblatts mit End(xlUp) zu suchen und manuell eine Spalte weiter zu schreiben: ListRows.Add fügt die Zeile immer korrekt innerhalb der Tabellenbegrenzung ein, sodass jede Gesamtergebniszeile, strukturierte Verweisformel oder bedingte Formatierungsregel, die auf die Tabelle angewendet wurde, automatisch auf die neue Zeile erweitert wird.

Datensätze aktualisieren

Um eine bestehende Zeile zu aktualisieren, durchlaufen Sie DataBodyRange und vergleichen Sie einen Schlüsselwert — hier wird das Ziel für die Region Central im Februar nach einer Budgetanpassung aktualisiert:

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

Dies ist das gleiche Muster von oben nach unten mit Abbruch beim ersten Treffer, wie es bei bedingter Logik verwendet wird, hier jedoch auf reale Zeilen angewendet statt auf fest codierte Werte: Die Schleife prüft Monat und Region gemeinsam mit And, und sobald beide übereinstimmen, wird die Zielspalte aktualisiert und mit Exit For die Schleife beendet, damit nicht unnötig weitergesucht wird. Die Verwendung von tbl.DataBodyRange.Cells(r, 1) statt eines arbeitsblattweiten Cells-Verweises hält die Zeilennummerierung auf die Tabellendaten beschränkt — Zeile 1 bedeutet hier die erste Datenzeile, unabhängig davon, in welcher physischen Arbeitsblattzeile die Tabelle beginnt.

Verweis auf Tabellenspalten

Strukturierte Verweise — ListColumns("Name") — sind lesbarer und robuster als das Zählen von Spaltennummern, insbesondere wenn eine Tabelle bearbeitet und Spalten verschoben werden:

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

ListColumns("Profit") findet die Spalte anhand ihres Kopfzeilentextes statt über die Positionsnummer, sodass der Code weiterhin funktioniert, selbst wenn Profit später von Spalte E nach Spalte F verschoben wird — das manuelle Zählen mit Cells(r, 5) würde in diesem Fall unbemerkt fehlschlagen. Application.WorksheetFunction ist die Schnittstelle, mit der VBA gewöhnliche Excel-Funktionen wie SUMME und MITTELWERT direkt auf ein Range-Objekt anwenden kann, anstatt eine manuelle Schleife mit Zwischensumme zu schreiben — das ist weniger Code und reduziert die Wahrscheinlichkeit von Fehlern beim Zählen.

Aufgabe

1. Öffnen Sie Section_4_Reports.xlsx, speichern Sie die Datei als Section_4_Reports.xlsm und bestätigen Sie, dass die Daten im Arbeitsblatt Reports eine Tabelle mit dem Namen tblReports sind (klicken Sie auf eine beliebige Zelle darin — die Registerkarte Tabellendesign sollte erscheinen).
2. Schreiben Sie ein Makro, das für jede der fünf Regionen eine April-Zeile mit ListRows.Add hinzufügt (insgesamt fünf neue Zeilen, erfundene Werte sind ausreichend).
3. Schreiben Sie ein zweites Makro, das mit ListColumns("Sales").DataBodyRange und WorksheetFunction.Sum die gesamten Umsätze aller Zeilen im Direktfenster ausgibt.