Deze zelfstudie laat zien hoe u met tabellen en ListObjects in VBA kunt werken.
VBA-tabellen en ListObjects
Tabellen zijn een van de handigste en krachtigste functies van Excel. In deze zelfstudie bespreken we hoe u VBA kunt gebruiken om een tabel te maken, een eenvoudige sortering aan een tabel toe te voegen, een tabel te filteren en andere tabelgerelateerde taken uit te voeren.
Maak een tabel met VBA
De ListObjects.Add-methode kan een tabel aan een werkblad toevoegen op basis van een bereik in dat werkblad. We hebben het bereik weergegeven in ($A$1:$B$8) op een werkblad met de naam Blad1.
De volgende code voegt een tabel met de naam Table1 toe aan uw werkblad, gebaseerd op het bereik ($A$1:$B$8) met behulp van de standaard tabelstijl:
| 123456 | Sub CreateTableInExcel()ActiveWorkbook.Sheets("Blad1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Naam = _"Tafel 1"Einde sub |
Het resultaat is:
Een kolom aan het einde van de tabel invoegen met VBA
U kunt de methode ListColumns.Add gebruiken om een kolom aan het einde van uw tabel toe te voegen. We hebben onze tabel genaamd Table1 hieronder weergegeven.
U kunt een kolom aan uw tabel toevoegen met behulp van de volgende code, die altijd een kolom aan het einde van de tabel zal toevoegen:
| 12345 | Sub AddColumnToTheEndOfTheTable()ActiveWorkbook.Sheets("Blad1").ListObjects("Tabel1").ListColumns.ToevoegenEinde sub |
Het resultaat is:
Een rij onderaan de tabel invoegen met VBA
U kunt de methode ListRows.Add gebruiken om een rij onder aan uw tabel toe te voegen. We hebben onze tabel genaamd Table1 hieronder weergegeven.
De volgende code voegt altijd een rij toe aan de onderkant van uw tabel.
| 12345 | Sub AddRowToTheBottomOfTheTable()ActiveSheet.ListObjects ("Tabel1").ListRows.ToevoegenEinde sub |
Het resultaat is:
Een eenvoudige sortering toevoegen met VBA
U kunt een tabel sorteren met VBA. We hebben onze tabel met de naam Tabel1 hieronder weergegeven en we kunnen VBA gebruiken om de verkoopkolom van laag naar hoog te sorteren.
De volgende code sorteert de kolom Verkoop in oplopende volgorde.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable()Bereik ("Tabel1[[#Headers],[Sales]]").SelecteerActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").Sorteren.SorterenVelden.Toevoegen _Sleutel:=Bereik("Tabel1[[#Alle],[Verkoop]]"), SortOn:=xlSortOnValues, Order:= _xlAscending, DataOption:=xlSortNormalMet ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").Sorteren.Kop = xlJa.MatchCase = False.Oriëntatie = xlTopToBottom.SortMethode = xlPinYin.Van toepassing zijnEindigt metEinde sub |
Het resultaat is:
Een tabel filteren met VBA
U kunt een Excel-tabel ook filteren met VBA. We hebben onze tabel met de naam Tabel1 en we willen de tabel filteren zodat alleen verkopen van meer dan 1500 worden weergegeven.
We kunnen de Autofilter-methode gebruiken, die vijf optionele parameters heeft. Omdat we de Sales-kolom, de tweede kolom, willen filteren, stellen we het veld in op 2 en gebruiken we de xlAnd-operatorparameter, die wordt gebruikt voor datums en getallen.
| 123456 | Sub EenvoudigFilter()ActiveWorkbook.Sheets("Blad1").ListObjects("Tabel1").Bereik.AutoFilter Veld:=2, Criteria1:= _">1500", Operator:=xlAndEinde sub |
Het resultaat is:
Wis het filter met de ShowAllData-methode in VBA
U hebt toegang tot de ShowAllData-methode van de Worksheet-klasse om het filter te wissen. Als u de filter(s) van een tabel wilt wissen, moet u eerst een cel in de tabel selecteren, wat u in VBA kunt doen.
De ShowAllData-methode zal een fout genereren als men geen voorwaardelijke logica gebruikt om te controleren of er een filter is toegepast in het werkblad. De volgende code laat zien hoe je dit doet:
| 123456789 | Sub ClearingTheFilter()Bereik ("Tabel1[[#Headers],[Sales]]").SelecteerIf ActiveWorkbook.Worksheets("Blad1").FilterMode = True DanActiveSheet.ShowAllDataStop alsEinde sub |
Wis alle filters uit een Excel-tabel
U hebt toegang tot de ShowAllData-methode van de klasse ListObject zonder dat u eerst een cel in de tabel hoeft te selecteren. De volgende code laat zien hoe je dit doet:
| 123 | Sub ClearAllTableFilters()ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").AutoFilter.ShowAllDataEinde sub |
Een rij verwijderen met VBA
U kunt een rij in de databody van uw tabel verwijderen met behulp van de ListRows.Delete-methode. U moet opgeven welke rij met behulp van het rijnummer. We hebben de volgende tabel genaamd Table1.
Stel dat u de tweede rij in de databody van uw tabel wilt verwijderen, met de volgende code kunt u dit doen:
| 12345 | Sub VerwijderArow()ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").ListRows(2).VerwijderenEinde sub |
Het resultaat is:
Een kolom verwijderen met VBA
U kunt een kolom uit uw tabel verwijderen met de methode ListColumns.Delete. We hebben de volgende tabel genaamd Tabel1 die hieronder wordt weergegeven:
Om de eerste kolom te verwijderen, gebruikt u de volgende code:
| 12345 | Sub VerwijderAcolumn()ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").ListColumns(1).VerwijderenEinde sub |
Het resultaat is:
Een tabel terug converteren naar een bereik in VBA
U kunt een tabel terug converteren naar een normaal bereik met behulp van VBA. De volgende code laat zien hoe u een tabel met de naam Table1 terug naar een bereik converteert:
| 12345 | Sub converterenATableBackToANormalRange()ActiveWorkbook.Sheets("Blad1").ListObjects("Tabel1").UnlistEinde sub |
Gestreepte kolommen en opmaak toevoegen aan alle tabellen in een werkblad met VBA
U hebt toegang tot alle tabellen in uw werkblad met behulp van de ListObjects-verzameling. In het onderstaande blad hebben we twee tabellen en we willen graag een gestreepte kolom aan beide tabellen tegelijk toevoegen en het lettertype van de gegevenssectie van beide tabellen wijzigen in vet, met behulp van VBA.
| 12345678910111213 | Sub toevoegenBandedColumns()Dim tbl As ListObjectDim sht als werkbladStel sht = ThisWorkbook.ActiveSheet inVoor elke tbl In sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = Truevolgende tblEinde sub |
Het resultaat is:
Een tabel maken in Access in VBA met DoCmd.RunSQL
Een van de belangrijkste manieren om een tabel in Access in VBA te maken, is door de DoCmd.RunSQL-methode te gebruiken om een actiequery uit te voeren met een SQL-instructie.
We hebben een knop op ons voorbeeldformulier en wanneer we op de knop klikken, willen we een tabel maken met de naam ProductsTable met twee velden of kolommen, het ene is het primaire sleutelveld met de naam ProductsID en het andere is een veld met de naam Sales.
Om deze tabel te maken zouden we de volgende code gebruiken:
| 123456 | Privé sub cmdCreateProductsTable_Click()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(Primaire sleutel ProductID INTEGER, Integer Sales);"Einde sub |
Het resultaat is:
Een tabel filteren in Access met VBA
U kunt een tabel in Access ook filteren met de methode DoCmd.ApplyFilter. We hebben onze eenvoudige tabel hieronder weergegeven in Access, genaamd ProductsTable.
We willen graag op deze knop op ons formulier drukken en dan alleen verkopen zien die groter zijn dan 1500.
We zouden dus de volgende code gebruiken om dit te doen:
| 1234567 | Privé sub cmdFilter_Click()DoCmd.OpenTable "ProductenTabel"DoCmd.ApplyFilter , "[Verkoop]>1500"Einde sub |
Het resultaat is:
