VBA – Tabellen und ListObjects

Dieses Tutorial zeigt, wie man mit Tables und ListObjects in VBA arbeitet.

VBA Tabellen und ListObjects

Tabellen sind eine der nützlichsten und leistungsfähigsten Funktionen von Excel. Dieses Tutorial zeigt, wie man mit VBA eine Tabelle erstellt, eine einfache Sortierung zu einer Tabelle hinzufügt, eine Tabelle filtert und andere tabellenbezogene Aufgaben ausführt.

Erstellen einer Tabelle mit VBA

Mit der ListObjects.Add-Methode kann eine Tabelle zu einem Arbeitsblatt hinzugefügt werden, die auf einem Bereich in diesem Arbeitsblatt basiert. Wir haben den Bereich ($A$1:$B$8) auf einem Arbeitsblatt namens Tabelle1.

Der folgende Code fügt eine Tabelle mit dem Namen Tabelle1 zu Ihrem Arbeitsblatt hinzu, die auf dem Bereich ($A$1:$B$8) basiert und den Standard-Tabellenstil verwendet:

Sub TabelleInExcelErstellen()

ActiveWorkbook.Sheets("Tabelle1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _
"Tabelle1"

End Sub

Das Ergebnis ist:

Einfügen einer Spalte am Ende der Tabelle mit VBA

Sie können die Methode ListColumns.Add verwenden, um eine Spalte am Ende Ihrer Tabelle einzufügen. Unsere Tabelle heißt Tabelle1 (siehe unten).

Mit dem folgendem Code können Sie eine Spalte zu Ihrer Tabelle hinzufügen, die immer am Ende der Tabelle steht:

Sub SpalteAnsTabellenendeHinzufuegen()

ActiveWorkbook.Sheets("Tabelle1").ListObjects("Tabelle1").ListColumns.Add

End Sub

Das Ergebnis ist:

Zeile am unteren Tabellenende mit VBA einfügen

Sie können die Methode ListRows.Add verwenden, um eine Zeile am unteren Ende Ihrer Tabelle einzufügen. Unsere Tabelle heißt Tabelle1(siehe unten).

Mit dem folgenden Code wird immer eine Zeile am Ende der Tabelle hinzugefügt.

Sub ZeileZuTabellenendeHinzufuegen()

ActiveSheet.ListObjects("Tabelle1").ListRows.Add

End Sub

Das Ergebnis ist:

Einfache Sortierung mit VBA einstellen

Sie können eine Tabelle mit VBA sortieren. In der unten gezeigten Tabelle mit dem Namen Tabelle1 können wir mit VBA die Spalte „Umsatz“ vom niedrigsten zum höchsten Wert sortieren.

Der folgende Code sortiert die Spalte Umsatz in aufsteigender Reihenfolge.

Sub TabelleEinfachSortieren()

Range("Tabelle1[[#Headers],[Umsatz]]").Select
ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").Sort.SortFields.Add _
Key:=Range("Tabelle1[[#All],[Umsatz]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").Sort

.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With

End Sub

Das Ergebnis ist:

Tabelle mit VBA filtern

Sie können eine Excel-Tabelle auch mit VBA filtern. Nehmen wir unsere Tabelle mit dem Namen Tabelle1 und filtern die Tabelle, sodass nur Umsätze mit mehr als 1.500 angezeigt werden.

Wir verwenden die Methode Autofilter, die fünf optionale Parameter hat. Da wir die Spalte Umsatz in der zweiten Spalte filtern möchten, setzen wir Field auf 2, und wir verwenden für den Operator den Parameter xlAnd, der für Datumsangaben und Zahlen verwendet wird.

Sub EinfacherFilter()

ActiveWorkbook.Sheets("Tabelle1").ListObjects("Tabelle1").Range.AutoFilter Field:=2, Criteria1:= _
">1500", Operator:=xlAnd

End Sub

Das Ergebnis ist:

Filter mit der ShowAllData-Methode in VBA löschen

Sie können auf die ShowAllData-Methode der Worksheet-Klasse zugreifen, um den Filter zu löschen. Wenn Sie den Filter einer Tabelle löschen möchten, müssen Sie zunächst eine Zelle in der Tabelle auswählen, was Sie in VBA tun können.

Die ShowAllData-Methode erzeugt einen Fehler, wenn keine bedingte Logik verwendet wird, um auf eine eventuelle Filteranwendung im Arbeitsblatt zu prüfen. Der folgende Code zeigt Ihnen, wie Sie dies tun können:

Sub FilterEntfernen()

Range("Tabelle1[[#Headers],[Umsatz]]").Select
If ActiveWorkbook.Worksheets("Tabelle1").FilterMode = True Then
ActiveSheet.ShowAllData

End If

End Sub

Alle Filter aus einer Excel-Tabelle löschen

Sie können auf die Methode ShowAllData der Klasse ListObject zugreifen, ohne vorher eine Zelle in der Tabelle auszuwählen. Der folgende Code zeigt Ihnen, wie Sie dies tun können:

Sub AlleTabellenFilterLoeschen()
ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").AutoFilter.ShowAllData
End Sub

Eine Zeile mit VBA löschen

Sie können im Datenkörper Ihrer Tabelle mit der Methode ListRows.Delete eine Zeile löschen. Sie müssen die Zeile anhand der Zeilennummer angeben. Wir haben die folgende Tabelle mit dem Namen Tabelle1.

Angenommen, Sie möchten die zweite Zeile im Datenkörper Ihrer Tabelle löschen, dann können Sie dies mit dem folgenden Code tun:

Sub ZeileLoeschen()

ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").ListRows(2).Delete

End Sub

Das Ergebnis ist:

Eine Spalte mit VBA löschen

Sie können eine Spalte aus Ihrer Tabelle mit der Methode ListColumns.Delete löschen. Wir haben die folgende Tabelle mit dem Namen Tabelle1, wie unten gezeigt:

Um die erste Spalte zu löschen, würden Sie den folgenden Code verwenden:

Sub SpalteLoeschen()

ActiveWorkbook.Worksheets("Tabelle1").ListObjects("Tabelle1").ListColumns(1).Delete

End Sub

Das Ergebnis ist:

 

Tabelle zurück zu einem Bereich in VBA konvertieren

Sie können eine Tabelle mithilfe von VBA in einen normalen Bereich zurück konvertieren. Der folgende Code zeigt Ihnen, wie Sie eine Tabelle namens Tabelle1 in einen Bereich zurück konvertieren können:

Sub TabelleInBereichKonvertieren()

ActiveWorkbook.Sheets("Tabelle1").ListObjects("Tabelle1").Unlist

End Sub

Hinzufügen von gebänderten Spalten und alle Tabellen in einem Arbeitsblatt mit VBA formatieren

Sie können auf alle Tabellen in Ihrem Arbeitsblatt mit der ListObjects-Sammlung zugreifen. Das folgende Arbeitsblatt enthält zwei Tabellen. Beiden Tabellen soll eine gebänderte Spalte hinzugefügt und die Schriftart des Datenbereichs beider Tabellen mithilfe von VBA in Fett geändert werden.

Sub GebaenderteSpaltenHinzufügen()

Dim Tabelle As ListObject
Dim Blatt As Worksheet

Set Blatt = ThisWorkbook.ActiveSheet

For Each Tabelle In Blatt.ListObjects
   Tabelle.ShowTableStyleColumnStripes = True
   Tabelle.DataBodyRange.Font.Bold = True
Next Tabelle 

End Sub

Das Ergebnis ist:

Eine Tabelle in Access in VBA mit DoCmd.RunSQL erstellen

Eine der wichtigsten Wege, eine Tabelle in Access in VBA zu erstellen, ist die Verwendung der Methode DoCmd.RunSQL, um eine Aktionsabfrage mit einer SQL-Anweisung auszuführen.

Wir haben eine Schaltfläche in unserem Beispielformular, und wenn wir auf die Schaltfläche klicken, möchten wir eine Tabelle namens ProduktTabelle mit zwei Feldern oder Spalten erstellen, von denen eine das Primärschlüsselfeld namens ProduktID und die andere ein Feld namens Umsatz ist.

Um diese Tabelle zu erstellen, würden wir den folgenden Code verwenden:

Private Sub cmdProduktTabelleErstellen_Klicken()

DoCmd.RunSQL "CREATE TABLE ProduktTabelle " _
& "(ProduktID INTEGER PRIMARY KEY, Umsatz Integer);"

End Sub

Das Ergebnis ist:

Tabelle in Access mit VBA filtern

Sie können eine Tabelle in Access auch mit der Methode DoCmd.ApplyFilter filtern. Wir haben unsere unten gezeigte einfache Tabelle in Access mit dem Namen ProduktTabelle.

Wir möchten diese Schaltfläche in unserem Formular betätigen und dann nur die Umsätze anzeigen, die größer als 1.500 sind.

Dazu würden wir den folgenden Code verwenden:

Private Sub cmdFilter_Click()

DoCmd.OpenTable "ProduktTabelle"

DoCmd.ApplyFilter , "[Umsatz]>1500"

End Sub

Das Ergebnis ist: