Denna handledning visar hur du arbetar med tabeller och ListObjects i VBA.
VBA -tabeller och ListObjects
Tabeller är en av Excels mest användbara och kraftfulla funktioner. I den här självstudien kommer vi att gå igenom hur du använder VBA för att skapa ett bord, lägga till en enkel sortering i ett bord, filtrera ett bord och utföra andra tabellrelaterade uppgifter.
Skapa ett bord med VBA
ListObjects.Add -metoden kan lägga till en tabell i ett kalkylblad, baserat på ett intervall i det kalkylbladet. Vi har intervallet som visas i ($ A $ 1: $ B $ 8) på ett kalkylblad som kallas Sheet1.
Följande kod kommer att lägga till en tabell, kallad Table1 i ditt kalkylblad, baserat på intervallet ($ A $ 1: $ B $ 8) med standard tabellstil:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Bord 1"Avsluta Sub |
Resultatet är:
Infoga en kolumn i slutet av tabellen med VBA
Du kan använda metoden ListColumns.Add för att lägga till en kolumn i slutet av din tabell. Vi har vårt bord som heter Table1 som visas nedan.
Du kan lägga till en kolumn i tabellen med följande kod, som alltid lägger till en kolumn i slutet av tabellen:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddAvsluta Sub |
Resultatet är:
Infoga en rad längst ner i tabellen med VBA
Du kan använda metoden ListRows.Add för att lägga till en rad längst ner i tabellen. Vi har vårt bord som heter Table1 som visas nedan.
Följande kod kommer alltid att lägga till en rad längst ner i tabellen.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabell1"). ListRows.AddAvsluta Sub |
Resultatet är:
Lägga till en enkel sortering med VBA
Du kan sortera ett bord med VBA. Vi har vår tabell som heter Table1 som visas nedan och vi kan använda VBA för att sortera försäljningskolumnen från lägsta till högsta.
Följande kod sorterar försäljningskolumnen i stigande ordning.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Område ("Tabell1 [[#rubriker], [försäljning]]"). VäljActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Nyckel: = Range ("Table1 [[#All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sortera.Header = xlJa.MatchCase = Falskt.Orientation = xlTopToBottom.SortMethod = xlPinYin.TillämpaSluta medAvsluta Sub |
Resultatet är:
Filtrera ett bord med VBA
Du kan också filtrera en Excel -tabell med VBA. Vi har vårt bord som heter Table1 och vi vill filtrera bordet så att endast försäljning över 1500 visas.
Vi kan använda Autofilter -metoden, som har fem valfria parametrar. Eftersom vi vill filtrera försäljningskolumnen, som är den andra kolumnen, ställer vi in fältet till 2, och vi använder operatörsparametern xlAnd, som används för datum och nummer.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Operatör: = xlAndAvsluta Sub |
Resultatet är:
Rensa filtret med ShowAllData -metoden i VBA
Du kan komma åt metoden ShowAllData i klassen kalkylblad för att rensa filtret. Om det är ett tabellfilter som du vill rensa måste du först välja en cell i tabellen, som du kan göra i VBA.
ShowAllData -metoden genererar ett fel om man inte använder villkorlig logik för att kontrollera om det har använts ett filter i kalkylbladet. Följande kod visar hur du gör detta:
| 123456789 | Sub ClearingTheFilter ()Område ("Tabell1 [[#rubriker], [försäljning]]"). VäljOm ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ThenActiveSheet.ShowAllDataAvsluta omAvsluta Sub |
Rensa alla filter från en Excel -tabell
Du kan komma åt ShowAllData -metoden i ListObject -klassen utan att behöva välja en cell i tabellen först. Följande kod visar hur du gör detta:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataAvsluta Sub |
Radera en rad med VBA
Du kan radera en rad i databodkroppen i din tabell med metoden ListRows.Delete. Du måste ange vilken rad med radnumret. Vi har följande tabell som heter Table1.
Låt oss säga att du ville ta bort den andra raden i databodens tabell, följande kod gör att du kan göra detta:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteAvsluta Sub |
Resultatet är:
Radera en kolumn med VBA
Du kan ta bort en kolumn från tabellen med metoden ListColumns.Delete. Vi har följande tabell som heter Table1 som visas nedan:
För att ta bort den första kolumnen använder du följande kod:
| 12345 | Sub RaderaAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteAvsluta Sub |
Resultatet är:
Konvertera ett bord tillbaka till ett intervall i VBA
Du kan konvertera ett bord till ett normalt intervall med VBA. Följande kod visar hur du konverterar en tabell som heter Table1 tillbaka till ett intervall:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistAvsluta Sub |
Lägga till bandade kolumner och formatering till alla tabellerna i ett kalkylblad med VBA
Du kan komma åt alla tabeller i ditt kalkylblad med ListObjects -samlingen. I arket nedan har vi två tabeller och vi skulle vilja lägga till en bandad kolumn till båda tabellerna samtidigt och ändra teckensnittet för datasektionen i båda tabellerna till fetstil, med VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl Som ListObjectDim sht Som arbetsbladStäll in sht = ThisWorkbook.ActiveSheetFör varje tbl I sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueNästa tblAvsluta Sub |
Resultatet är:
Skapa en tabell i åtkomst i VBA med DoCmd.RunSQL
Ett av de viktigaste sätten att skapa en tabell i Access i VBA är att använda DoCmd.RunSQL -metoden för att köra en åtgärdsfråga med en SQL -sats.
Vi har en knapp på vårt provformulär och när vi klickar på knappen vill vi skapa en tabell som heter ProductsTable med två fält eller kolumner, en skulle vara det primära nyckelfältet som heter ProductsID och det andra skulle vara ett fält som heter Sales.
För att skapa denna tabell skulle vi använda följande kod:
| 123456 | Privat under cmdCreateProductsTable_Click ()DoCmd.RunSQL "SKAPA TABELLProdukttabell" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Avsluta Sub |
Resultatet är:
Filtrering av en tabell i Access med VBA
Du kan också filtrera en tabell i Access med DoCmd.ApplyFilter -metoden. Vi har vår enkla tabell som visas nedan i Access som kallas ProductsTable.
Vi skulle vilja trycka på den här knappen på vårt formulär och sedan bara se försäljning som är större än 1500.
Så vi skulle använda följande kod för att göra detta:
| 1234567 | Privat under cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Försäljning]> 1500"Avsluta Sub |
Resultatet är:
