✅ VBA-Tabellen und ListObjects

Inhaltsverzeichnis

VBA-Tabellen und ListObjects

In diesem Tutorial wird gezeigt, wie Sie mit Tabellen und ListObjects in VBA arbeiten.

VBA-Tabellen und ListObjects

Tabellen sind eine der nützlichsten und leistungsstärksten Funktionen von Excel. In diesem Tutorial erfahren Sie, wie Sie mit VBA eine Tabelle erstellen, einer Tabelle eine einfache Sortierung hinzufügen, eine Tabelle filtern und andere tabellenbezogene Aufgaben ausführen.

Erstellen Sie eine Tabelle mit VBA

Die ListObjects.Add-Methode kann einem Arbeitsblatt basierend auf einem Bereich in diesem Arbeitsblatt eine Tabelle hinzufügen. Wir haben den in ($A$1:$B$8) gezeigten Bereich auf einem Arbeitsblatt namens Sheet1.

Der folgende Code fügt Ihrem Arbeitsblatt eine Tabelle namens Table1 basierend auf dem Bereich ($A$1:$B$8) unter Verwendung des standardmäßigen Tabellenstils hinzu:

123456

Unter CreateTableInExcel()ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _"Tabelle 1"End Sub

Das Ergebnis ist:

Einfügen einer Spalte am Ende der Tabelle mit VBA

Sie können die ListColumns.Add-Methode verwenden, um am Ende Ihrer Tabelle eine Spalte hinzuzufügen. Wir haben unsere Tabelle namens Table1 unten gezeigt.

Sie können Ihrer Tabelle mit dem folgenden Code eine Spalte hinzufügen, wodurch immer eine Spalte am Ende der Tabelle hinzugefügt wird:

12345

Sub AddColumnToTheEndOfTheTable()ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").ListColumns.AddEnd Sub

Das Ergebnis ist:

Einfügen einer Zeile am unteren Rand der Tabelle mit VBA

Sie können die ListRows.Add-Methode verwenden, um eine Zeile am Ende der Tabelle hinzuzufügen. Wir haben unsere Tabelle namens Table1 unten gezeigt.

Der folgende Code fügt immer eine Zeile am Ende Ihrer Tabelle hinzu.

12345

Sub AddRowToTheBottomOfTheTable()ActiveSheet.ListObjects("Table1").ListRows.AddEnd Sub

Das Ergebnis ist:

Hinzufügen einer einfachen Sortierung mit VBA

Sie können eine Tabelle mit VBA sortieren. Wir haben unsere Tabelle namens Table1 unten gezeigt und wir können VBA verwenden, um die Verkaufsspalte vom niedrigsten zum höchsten zu sortieren.

Der folgende Code sortiert die Spalte Sales in aufsteigender Reihenfolge.

12345678910111213141516171819

Sub SimpleSortOnTheTable()Range("Table1[[#Headers],[Sales]]").SelectActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _Key:=Range("Table1[[#All],[Sales]]"), SortOn:=xlSortOnValues, Order:= _xlAufsteigend, DataOption:=xlSortNormalMit ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.Header = xlYes.MatchCase = Falsch.Orientierung = xlTopToBottom.SortMethod = xlPinYin.AnwendenEnde mitEnd Sub

Das Ergebnis ist:

Filtern einer Tabelle mit VBA

Sie können eine Excel-Tabelle auch mit VBA filtern. Wir haben unsere Tabelle mit dem Namen Table1 und möchten die Tabelle so filtern, dass nur Verkäufe über 1500 angezeigt werden.

Wir können die Autofilter-Methode verwenden, die fünf optionale Parameter hat. Da wir die Spalte Sales filtern möchten, die die zweite Spalte ist, setzen wir das Feld auf 2 und verwenden den Operatorparameter xlAnd, der für Datumsangaben und Zahlen verwendet wird.

123456

Unter SimpleFilter()ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _">1500", Operator:=xlAndEnd Sub

Das Ergebnis ist:

Löschen Sie den Filter mit der ShowAllData-Methode in VBA

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

Die ShowAllData-Methode generiert einen Fehler, wenn keine bedingte Logik verwendet wird, um zu überprüfen, ob im Arbeitsblatt ein Filter angewendet wurde. Der folgende Code zeigt Ihnen, wie das geht:

123456789

Unter ClearingTheFilter()Range("Table1[[#Headers],[Sales]]").SelectWenn ActiveWorkbook.Worksheets("Sheet1").FilterMode = True ThenActiveSheet.ShowAllDataEnde WennEnd Sub

Alle Filter aus einer Excel-Tabelle löschen

Sie können auf die ShowAllData-Methode der ListObject-Klasse zugreifen, ohne zuerst eine Zelle in der Tabelle auswählen zu müssen. Der folgende Code zeigt Ihnen, wie das geht:

123	Unter ClearAllTableFilters()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllDataEnd Sub

Löschen einer Zeile mit VBA

Sie können eine Zeile im Datenkörper Ihrer Tabelle mit der ListRows.Delete-Methode löschen. Welche Zeile Sie mit der Zeilennummer angeben müssen. Wir haben die folgende Tabelle namens Table1.

Angenommen, Sie möchten die zweite Zeile im Datenkörper Ihrer Tabelle löschen, der folgende Code würde Ihnen dies ermöglichen:

12345

Sub DeleteARow()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListRows(2).DeleteEnd Sub

Das Ergebnis ist:

Löschen einer Spalte mit VBA

Sie können eine Spalte mit der ListColumns.Delete-Methode aus Ihrer Tabelle löschen. Wir haben die folgende Tabelle namens Table1 unten gezeigt:

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

12345

Unter DeleteAColumn()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).DeleteEnd Sub

Das Ergebnis ist:

Konvertieren einer Tabelle in einen Bereich in VBA

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

12345

Sub ConvertingATableBackToANormalRange()ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").UnlistEnd Sub

Hinzufügen von gebänderten Spalten und Formatieren zu allen Tabellen in einem Arbeitsblatt mit VBA

Sie können mit der ListObjects-Auflistung auf alle Tabellen in Ihrem Arbeitsblatt zugreifen. Im folgenden Blatt haben wir zwei Tabellen und möchten beiden Tabellen gleichzeitig eine Bandspalte hinzufügen und die Schriftart des Datenabschnitts beider Tabellen mithilfe von VBA in Fettdruck ändern.

12345678910111213

Unter HinzufügenBandedColumns()Dim tbl As ListObjectDim sht als ArbeitsblattSet sht = ThisWorkbook.ActiveSheetFür jedes tbl in sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueNächstes tblEnd Sub

Das Ergebnis ist:

Erstellen einer Tabelle in Access in VBA mit DoCmd.RunSQL

Eine der wichtigsten Möglichkeiten zum Erstellen einer Tabelle in Access in VBA besteht darin, die DoCmd.RunSQL-Methode zum Ausführen einer Aktionsabfrage mit einer SQL-Anweisung zu verwenden.

Wir haben eine Schaltfläche in unserem Beispielformular und wenn wir auf die Schaltfläche klicken, möchten wir eine Tabelle namens ProductsTable mit zwei Feldern oder Spalten erstellen, eines wäre das Primärschlüsselfeld namens ProductsID und das andere wäre ein Feld namens Sales.

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

123456

Privates Sub cmdCreateProductsTable_Click()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"End Sub

Das Ergebnis ist:

Filtern einer Tabelle in Access mit VBA

Sie können eine Tabelle in Access auch mithilfe der DoCmd.ApplyFilter-Methode filtern. Wir haben unsere einfache Tabelle unten in Access namens ProductsTable.

Wir möchten diese Schaltfläche in unserem Formular drücken und dann nur Verkäufe sehen, die größer als 1500 sind.