Denne opplæringen vil demonstrere hvordan du arbeider med tabeller og ListObjects i VBA.
VBA -tabeller og ListObjects
Tabeller er en av Excel mest nyttige og kraftfulle funksjoner. I denne opplæringen vil vi gå over hvordan du bruker VBA til å lage et bord, legge til en enkel sortering i et bord, filtrere et bord og utføre andre tabellrelaterte oppgaver.
Lag et bord med VBA
ListObjects.Add -metoden kan legge til en tabell i et regneark, basert på et område i regnearket. Vi har området vist i ($ A $ 1: $ B $ 8) på et regneark kalt Sheet1.
Følgende kode vil legge til en tabell, kalt Table1 i regnearket, basert på området ($ A $ 1: $ B $ 8) ved bruk av standard tabellstil:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"), xlYes) .Name = _"Tabell 1"Slutt Sub |
Resultatet er:
Sette inn en kolonne på slutten av tabellen med VBA
Du kan bruke metoden ListColumns.Add for å legge til en kolonne på slutten av tabellen. Vi har tabellen vår kalt Table1 vist nedenfor.
Du kan legge til en kolonne i tabellen ved hjelp av følgende kode, som alltid vil legge til en kolonne i enden av tabellen:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddSlutt Sub |
Resultatet er:
Sette inn en rad nederst i tabellen med VBA
Du kan bruke ListRows.Add -metoden for å legge til en rad nederst i tabellen. Vi har tabellen vår kalt Table1 vist nedenfor.
Følgende kode vil alltid legge til en rad nederst i tabellen.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabell1"). ListRows.AddSlutt Sub |
Resultatet er:
Legge til en enkel sortering med VBA
Du kan sortere et bord med VBA. Vi har tabellen vår kalt Table1 vist nedenfor, og vi kan bruke VBA til å sortere salgskolonnen fra laveste til høyeste.
Følgende kode sorterer salgskolonnen i stigende rekkefølge.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Område ("Tabell1 [[#overskrifter], [salg]]"). VelgActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Nøkkel: = Range ("Table1 [[#All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Ark1"). ListObjects ("Tabell1"). Sorter.Header = xlJa.MatchCase = Falsk.Orientation = xlTopToBottom.SortMethod = xlPinYin.Søke omSlutt medSlutt Sub |
Resultatet er:
Filtrer et bord med VBA
Du kan også filtrere en Excel -tabell ved hjelp av VBA. Vi har bordet vårt som heter Table1, og vi vil filtrere bordet slik at bare salg på over 1500 vises.
Vi kan bruke Autofilter -metoden, som har fem valgfrie parametere. Siden vi ønsker å filtrere salgskolonnen, som er den andre kolonnen, setter vi feltet til 2, og vi bruker operatørparameteren xlAnd, som brukes for datoer og tall.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Operatør: = xlAndSlutt Sub |
Resultatet er:
Fjern filteret med ShowAllData -metoden i VBA
Du kan få tilgang til ShowAllData -metoden i regnearkklassen for å fjerne filteret. Hvis det er et tabellfilter (er) du vil slette, må du først velge en celle i tabellen, som du kan gjøre i VBA.
ShowAllData -metoden vil generere en feil hvis man ikke bruker betinget logikk for å kontrollere om det har blitt brukt et filter i regnearket. Følgende kode viser deg hvordan du gjør dette:
| 123456789 | Sub ClearingTheFilter ()Område ("Tabell1 [[#overskrifter], [salg]]"). VelgHvis ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ThenActiveSheet.ShowAllDataSlutt omSlutt Sub |
Fjern alle filtre fra en Excel -tabell
Du kan få tilgang til ShowAllData -metoden i ListObject -klassen uten å måtte velge en celle i tabellen først. Følgende kode viser deg hvordan du gjør dette:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataSlutt Sub |
Sletter en rad med VBA
Du kan slette en rad i databodyen i tabellen ved hjelp av metoden ListRows.Delete. Du må spesifisere hvilken rad som bruker radnummeret. Vi har følgende tabell kalt Table1.
La oss si at du ønsket å slette den andre raden i databodien til tabellen din, med følgende kode kan du gjøre dette:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteSlutt Sub |
Resultatet er:
Sletter en kolonne med VBA
Du kan slette en kolonne fra tabellen ved hjelp av metoden ListColumns.Delete. Vi har følgende tabell kalt Table1 vist nedenfor:
For å slette den første kolonnen bruker du følgende kode:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteSlutt Sub |
Resultatet er:
Konvertere en tabell tilbake til et område i VBA
Du kan konvertere et bord tilbake til et normalt område ved hjelp av VBA. Følgende kode viser deg hvordan du konverterer en tabell kalt Table1 tilbake til et område:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistSlutt Sub |
Legge til bandede kolonner og formatering til alle tabellene i et regneark ved hjelp av VBA
Du får tilgang til alle tabellene i regnearket ved å bruke ListObjects -samlingen. I arket nedenfor har vi to tabeller, og vi vil gjerne legge til en båndet kolonne på begge tabellene samtidig og endre skrifttypen til dataseksjonen i begge tabellene til fet skrift, ved hjelp av VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl As ListObjectDim sht Som regnearkSett sht = ThisWorkbook.ActiveSheetFor hver tbl I sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueNeste tblSlutt Sub |
Resultatet er:
Opprette en tabell i Access i VBA ved hjelp av DoCmd.RunSQL
En av hovedmåtene for å lage en tabell i Access i VBA, er å bruke DoCmd.RunSQL -metoden til å kjøre en handlingsforespørsel med en SQL -setning.
Vi har en knapp på eksempelskjemaet vårt, og når vi klikker på knappen, vil vi lage en tabell kalt ProductsTable med to felt eller kolonner, det ene vil være det primære nøkkelfeltet ProductID og det andre vil være et felt som heter Sales.
For å lage denne tabellen ville vi bruke følgende kode:
| 123456 | Privat sub cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Slutt Sub |
Resultatet er:
Filtrere en tabell i Access ved hjelp av VBA
Du kan også filtrere en tabell i Access ved hjelp av DoCmd.ApplyFilter -metoden. Vi har vår enkle tabell vist nedenfor i Access kalt ProductsTable.
Vi vil gjerne trykke på denne knappen på skjemaet vårt og deretter bare se salg som er større enn 1500.
Så vi ville bruke følgende kode for å gjøre dette:
| 1234567 | Privat sub cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Salg]> 1500"Slutt Sub |
Resultatet er:
