See õpetus näitab, kuidas VBA tabelite ja ListObjectidega töötada.
VBA tabelid ja loendiobjektid
Tabelid on üks Exceli kõige kasulikumaid ja võimsamaid funktsioone. Selles õpetuses käsitleme, kuidas kasutada VBA-d tabeli loomiseks, tabelisse lihtsa sortimise lisamiseks, tabeli filtreerimiseks ja muude tabeliga seotud ülesannete täitmiseks.
Looge tabel VBA abil
Meetodiga ListObjects.Add saab töölehele lisada tabeli, mis põhineb selle töölehe vahemikul. Vahemik on näidatud ($ A $ 1: $ B $ 8) töölehel nimega Sheet1.
Järgmine kood lisab teie töölehele tabeli nimega Table1, mis põhineb vahemikul ($ A $ 1: $ B $ 8), kasutades vaikimisi tabeli stiili:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Tabel 1"End Sub |
Tulemuseks on:
Veeru sisestamine tabeli lõppu koos VBA -ga
Tabeli lõppu veeru lisamiseks saate kasutada meetodit ListColumns.Add. Meie tabel nimega Tabel 1 on näidatud allpool.
Saate oma tabelisse veeru lisada järgmise koodi abil, mis lisab tabeli lõppu alati veeru:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddEnd Sub |
Tulemuseks on:
Rida sisestamine tabeli allosas VBA abil
Tabeli lõppu rea lisamiseks saate kasutada meetodit ListRows.Add. Meie tabel nimega Tabel 1 on näidatud allpool.
Järgmine kood lisab tabeli lõppu alati rea.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabel1"). ListRows.AddEnd Sub |
Tulemuseks on:
Lihtsa sortimise lisamine VBA -ga
Tabeli saate sortida VBA abil. Meie tabel Tabel 1 on näidatud allpool ja saame kasutada VBA -d müügiveeru sortimiseks madalaimast kõrgeimani.
Järgmine kood sorteerib veeru Müük kasvavas järjekorras.
| 12345678910111213141516171819 | Alam SimpleSortOnTheTable ()Vahemik ("Tabel1 [[#Päised], [Müük]]"). ValigeActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Võti: = Vahemik ("Tabel1 [[#Kõik], [Müük]]"), SortOn: = xlSortOnValues, Tellimus: = _xlTõusev, DataOption: = xlSortNormalRakendusega ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sorteeri.Peal = xlJah.MatchCase = Vale.Orientation = xlTopToBottom.SortMethod = xlPinYin.KandidaLõpetaEnd Sub |
Tulemuseks on:
Tabeli filtreerimine VBA abil
VBA abil saate filtreerida ka Exceli tabeli. Meil on oma tabel nimega Table1 ja me soovime tabeli filtreerida nii, et kuvatakse ainult üle 1500 müügi.
Saame kasutada automaatse filtri meetodit, millel on viis valikulist parameetrit. Kuna tahame filtreerida veeru Müük, mis on teine veerg, määrame väljaks 2 ja kasutame kuupäeva ja numbrite jaoks kasutatavat parameetrit xlAnd.
| 123456 | Alamlihtne filter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Vahemik.AutoFilter Field: = 2, Criteria1: = _"> 1500", operaator: = xlJaEnd Sub |
Tulemuseks on:
Tühjendage filter VBA ShowAllData meetodiga
Filtri tühjendamiseks pääsete juurde töölehe klassi ShowAllData meetodile. Kui see on tabeli filter (filtrid), mida soovite kustutada, peate esmalt valima tabelis lahtri, mida saate teha VBA -s.
ShowAllData meetod tekitab tõrke, kui ei kasutata tingimusloogikat, et kontrollida, kas töölehel on kasutatud filtrit. Järgmine kood näitab teile, kuidas seda teha:
| 123456789 | AlampuhastusTheFilter ()Vahemik ("Tabel1 [[#Päised], [Müük]]"). ValigeKui ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True thenActiveSheet.ShowAllDataLõpp KuiEnd Sub |
Tühjendage Exceli tabelist kõik filtrid
Saate juurdepääsu klassi ListObject meetodile ShowAllData, ilma et peaksite eelnevalt tabelis lahtrit valima. Järgmine kood näitab teile, kuidas seda teha:
| 123 | Alam ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataEnd Sub |
Rea kustutamine VBA abil
Saate kustutada rea oma tabeli andmebaasis, kasutades meetodit ListRows.Delete. Reanumbri abil peate määrama, milline rida. Meil on järgmine tabel nimega Tabel 1.
Oletame, et soovite kustutada oma tabeli andmebaasi teise rea, järgmine kood võimaldab teil seda teha:
| 12345 | Alam kustutamineARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteEnd Sub |
Tulemuseks on:
Veeru kustutamine VBA abil
Saate veeru tabelist kustutada, kasutades meetodit ListColumns.Delete. Meil on järgmine tabel nimega Tabel 1, mis on näidatud allpool:
Esimese veeru kustutamiseks kasutage järgmist koodi:
| 12345 | Alam KustutaVeerg ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteEnd Sub |
Tulemuseks on:
Tabeli teisendamine vahemikku tagasi VBA -s
Tabeli saate VBA abil normaalsesse vahemikku teisendada. Järgmine kood näitab, kuidas muuta tabel nimega Tabel tagasi vahemikku:
| 12345 | AlamkonverteerimineATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistEnd Sub |
Ribastatud veergude lisamine ja vormindamine töölehe kõikidele tabelitele VBA abil
Kõigile töölehe tabelitele pääsete juurde, kasutades ListObjects kogumit. Alloleval lehel on meil kaks tabelit ja soovime lisada mõlemale tabelile korraga riba veeru ja muuta VBA abil mõlema tabeli andmesektsiooni fondi paksuks.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl ListObjectinaDim sht kui töölehtMäära sht = ThisWorkbook.ActiveSheetIga tbl kohta sht.ListObjectstbl.ShowTableStyleColumnStripes = Tõsitbl.DataBodyRange.Font.Bold = TõsiJärgmine tblEnd Sub |
Tulemuseks on:
Tabeli loomine Accessis VBA -s, kasutades DoCmd.RunSQL -i
Üks peamisi viise Accessi tabeli loomiseks VBA -s on DoCmd.RunSQL meetodi kasutamine toimingupäringu käivitamiseks SQL -lausega.
Meie näidisvormil on nupp ja nupule klõpsates tahame luua tabeli nimega ProductsTable kahe välja või veeruga, üks oleks primaarvõtme väli nimega ProductsID ja teine väli nimega Müük.
Selle tabeli loomiseks kasutaksime järgmist koodi:
| 123456 | Privaatne alam cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" tabel _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"End Sub |
Tulemuseks on:
Tabeli filtreerimine Accessis VBA abil
Saate tabeli Accessis filtreerida ka DoCmd.ApplyFilter meetodi abil. Meil on Accessis allpool näidatud meie lihtne tabel nimega ProductsTable.
Soovime oma vormil seda nuppu vajutada ja näha ainult müüki, mis on suurem kui 1500.
Niisiis, me kasutaksime selleks järgmist koodi:
| 1234567 | Privaatne alam cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Müük]> 1500"End Sub |
Tulemuseks on:
