Denne vejledning viser, hvordan du arbejder med tabeller og ListObjects i VBA.
VBA -tabeller og ListObjects
Tabeller er en af Excels mest nyttige og kraftfulde funktioner. I denne vejledning vil vi gå over, hvordan man bruger VBA til at oprette en tabel, tilføje en simpel sortering til et bord, filtrere et bord og udføre andre tabelrelaterede opgaver.
Opret en tabel med VBA
ListObjects.Add -metoden kan føje en tabel til et regneark baseret på en rækkevidde i dette regneark. Vi har intervallet vist i ($ A $ 1: $ B $ 8) på et regneark kaldet Sheet1.
Følgende kode tilføjer en tabel, kaldet tabel1 til dit regneark, baseret på intervallet ($ A $ 1: $ B $ 8) ved hjælp af standardtabelformatet:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Tabel 1"Afslut Sub |
Resultatet er:
Indsætning af en kolonne i slutningen af tabellen med VBA
Du kan bruge metoden ListColumns.Add for at tilføje en kolonne til slutningen af din tabel. Vi har vores tabel kaldet tabel1 vist nedenfor.
Du kan tilføje en kolonne til din tabel ved hjælp af følgende kode, som altid tilføjer en kolonne til slutningen af tabellen:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddAfslut Sub |
Resultatet er:
Indsætte en række i bunden af tabellen med VBA
Du kan bruge metoden ListRows.Add til at tilføje en række til bunden af din tabel. Vi har vores tabel kaldet tabel1 vist nedenfor.
Følgende kode tilføjer altid en række til bunden af din tabel.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabel1"). ListRows.AddAfslut Sub |
Resultatet er:
Tilføjelse af en enkel sortering med VBA
Du kan sortere et bord med VBA. Vi har vores tabel kaldet tabel1 vist nedenfor, og vi kan bruge VBA til at sortere salgskolonnen fra laveste til højeste.
Følgende kode sorterer kolonnen Salg i stigende rækkefølge.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Område ("Tabel1 [[#overskrifter], [salg]]"). VælgActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Nøgle: = Range ("Table1 [[#All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Ark1"). ListObjects ("Tabel1"). Sorter.Header = xlJa.MatchCase = Falsk.Orientation = xlTopToBottom.SortMethod = xlPinYin.AnsøgeAfslut medAfslut Sub |
Resultatet er:
Filtrer et bord med VBA
Du kan også filtrere en Excel -tabel ved hjælp af VBA. Vi har vores bord kaldet Table1, og vi vil gerne filtrere bordet, så der kun vises salg på over 1500.
Vi kan bruge Autofilter -metoden, som har fem valgfrie parametre. Da vi gerne vil filtrere salgskolonnen, som er den anden kolonne, sætter vi feltet til 2, og vi bruger parameteren xlAnd, der bruges til datoer og tal.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Operatør: = xlAndAfslut Sub |
Resultatet er:
Ryd filteret med ShowAllData -metoden i VBA
Du kan få adgang til ShowAllData -metoden i regnearkklassen for at slette filteret. Hvis det er et tabellets filter (er), du vil rydde, skal du først vælge en celle i tabellen, som du kan gøre i VBA.
ShowAllData -metoden genererer en fejl, hvis man ikke bruger betinget logik for at kontrollere, om der er blevet anvendt et filter i regnearket. Følgende kode viser dig, hvordan du gør dette:
| 123456789 | Sub ClearingTheFilter ()Område ("Tabel1 [[#overskrifter], [Salg]]"). VælgHvis ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ThenActiveSheet.ShowAllDataAfslut HvisAfslut Sub |
Ryd alle filtre fra en Excel -tabel
Du kan få adgang til ShowAllData -metoden i ListObject -klassen uden først at skulle markere en celle i tabellen. Følgende kode viser dig, hvordan du gør dette:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataAfslut Sub |
Sletning af en række med VBA
Du kan slette en række i databordet i din tabel ved hjælp af metoden ListRows.Delete. Du skal angive hvilken række ved hjælp af rækkenummeret. Vi har følgende tabel kaldet tabel1.
Lad os sige, at du ønskede at slette den anden række i databordet i din tabel, følgende kode ville give dig mulighed for at gøre dette:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteAfslut Sub |
Resultatet er:
Sletning af en kolonne med VBA
Du kan slette en kolonne fra din tabel ved hjælp af metoden ListColumns.Delete. Vi har følgende tabel kaldet tabel 1 vist nedenfor:
For at slette den første kolonne skal du bruge følgende kode:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .SletAfslut Sub |
Resultatet er:
Konvertering af en tabel tilbage til et område i VBA
Du kan konvertere en tabel tilbage til et normalt område ved hjælp af VBA. Følgende kode viser dig, hvordan du konverterer en tabel kaldet Table1 tilbage til et område:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistAfslut Sub |
Tilføjelse af bandede kolonner og formatering til alle tabellerne i et regneark ved hjælp af VBA
Du kan få adgang til alle tabellerne i dit regneark ved hjælp af ListObjects -samlingen. I nedenstående ark har vi to tabeller, og vi vil gerne tilføje en Banded Column til begge tabeller på én gang og ændre skrifttypen for datasektionen i begge tabeller til fed ved hjælp af VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl som ListObjectDim sht Som regnearkIndstil sht = ThisWorkbook.ActiveSheetFor hver tbl I sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueNæste tblAfslut Sub |
Resultatet er:
Oprettelse af en tabel i Access i VBA ved hjælp af DoCmd.RunSQL
En af de vigtigste måder at oprette en tabel i Access i VBA på er ved at bruge DoCmd.RunSQL -metoden til at køre en handlingsforespørgsel med en SQL -sætning.
Vi har en knap på vores prøveformular, og når vi klikker på knappen, vil vi gerne oprette en tabel kaldet ProductsTable med to felter eller kolonner, den ene ville være det primære nøglefelt kaldet ProductsID og den anden ville være et felt kaldet Sales.
For at oprette denne tabel ville vi bruge følgende kode:
| 123456 | Privat under cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProduktID INTEGER PRIMÆR NØGLE, Salg Heltal);"Afslut Sub |
Resultatet er:
Filtrering af en tabel i Access ved hjælp af VBA
Du kan også filtrere en tabel i Access ved hjælp af DoCmd.ApplyFilter -metoden. Vi har vores enkle tabel vist nedenfor i Access kaldet ProductsTable.
Vi vil gerne trykke på denne knap på vores formular og derefter kun se salg, der er større end 1500.
Så vi ville bruge følgende kode til at gøre dette:
| 1234567 | Privat under cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Salg]> 1500"Afslut Sub |
Resultatet er:
