Ovaj će vodič pokazati kako raditi s tablicama i ListObjektima u VBA -i.
VBA tablice i ListObjekti
Tablice su jedna od najkorisnijih i najmoćnijih značajki programa Excel, u ovom ćemo vodiču proučiti kako koristiti VBA za stvaranje tablice, dodati jednostavno sortiranje tablici, filtrirati tablicu i izvesti druge zadatke vezane uz tablicu.
Napravite tablicu s VBA
Metoda ListObjects.Add može dodati tablicu na radni list, na temelju raspona u tom radnom listu. Raspon imamo prikazan u ($ A $ 1: $ B $ 8) na radnom listu pod nazivom Sheet1.
Sljedeći će kôd dodati tablicu, nazvanu Table1, na vaš radni list, na temelju raspona ($ A $ 1: $ B $ 8) koristeći zadani stil tablice:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlDa). Ime = _"Stol 1"Kraj podm |
Rezultat je:
Umetanje stupca na kraju tablice s VBA
Možete koristiti metodu ListColumns.Add kako biste dodali stupac na kraj tablice. Imamo našu tablicu koja se zove Tablica1 prikazana dolje.
Možete dodati stupac u tablicu pomoću sljedećeg koda, koji će uvijek dodati stupac na kraj tablice:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddKraj podm |
Rezultat je:
Umetanje retka na dno tablice s VBA
Pomoću metode ListRows.Add možete dodati redak na dno tablice. Imamo našu tablicu koja se zove Tablica1 prikazana dolje.
Sljedeći kôd uvijek će dodati redak na dno tablice.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tablica1"). ListRows.AddKraj podm |
Rezultat je:
Dodavanje jednostavnog sortiranja s VBA
Možete sortirati tablicu s VBA. Imamo dolje prikazanu tablicu koja se zove Tablica1 i pomoću VBA -a možemo razvrstati stupac prodaje od najnižeg do najvećeg.
Sljedeći kôd razvrstit će stupac Prodaja uzlaznim redoslijedom.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Raspon ("Tablica1 [[#zaglavlja], [Prodaja]]"). OdaberiteActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Ključ: = Raspon ("Tablica1 [[#Sve], [Prodaja]]"), SortOn: = xlSortOnValues, Redoslijed: = _xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.Header = xlDa.MatchCase = Netačno.Orijentacija = xlTopToBottom.SortMethod = xlPinYin.Prijavi seZavrši sKraj podm |
Rezultat je:
Filtrirajte tablicu s VBA
Excel tablicu možete filtrirati i pomoću VBA. Imamo našu tablicu koja se zove Table1 i htjeli bismo filtrirati tablicu tako da se prikazuje samo prodaja veća od 1500.
Možemo koristiti metodu Autofilter koja ima pet izbornih parametara. Budući da želimo filtrirati stupac Prodaja, drugi stupac, polje smo postavili na 2 i koristimo parametar operatora xlAnd koji se koristi za datume i brojeve.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Operator: = xlAndKraj podm |
Rezultat je:
Očistite filtar pomoću metode ShowAllData u VBA
Možete pristupiti metodi ShowAllData klase radnog lista radi brisanja filtra. Ako želite izbrisati filtre tablice, najprije morate odabrati ćeliju u tablici, što možete učiniti u VBA.
Metoda ShowAllData generirat će pogrešku ako se ne koristi uvjetna logika kako bi se provjerilo je li na radnom listu primijenjen filtar. Sljedeći kôd pokazuje kako to učiniti:
| 123456789 | Sub ClearingTheFilter ()Raspon ("Tablica1 [[#zaglavlja], [Prodaja]]"). OdaberiteAko je ActiveWorkbook.Worksheets ("List1"). FilterMode = Tačno tadaActiveSheet.ShowAllDataZavrši akoKraj podm |
Brisanje svih filtara iz Excelove tablice
Možete pristupiti metodi ShowAllData klase ListObject bez da morate prvo odabrati ćeliju u tablici. Sljedeći kôd pokazuje kako to učiniti:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataKraj podm |
Brisanje retka s VBA
Možete izbrisati redak u podatkovnom tijelu svoje tablice pomoću metode ListRows.Delete. Morate navesti koji red pomoću broja retka. Imamo sljedeću tablicu koja se zove Tablica1.
Recimo da ste htjeli izbrisati drugi redak u podatkovnom tijelu svoje tablice, sljedeći kôd bi vam to omogućio:
| 12345 | PodizbrišiARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteKraj podm |
Rezultat je:
Brisanje stupca s VBA
Možete izbrisati stupac iz tablice pomoću metode ListColumns.Delete. Imamo sljedeću tablicu pod nazivom Tablica 1 prikazana u nastavku:
Da biste izbrisali prvi stupac, upotrijebili biste sljedeći kôd:
| 12345 | PodizbrišiAcolumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteKraj podm |
Rezultat je:
Pretvaranje tablice natrag u raspon u VBA
Tablicu možete pretvoriti u normalan raspon pomoću VBA. Sljedeći kôd pokazuje vam kako pretvoriti tablicu pod nazivom Table1 natrag u raspon:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistKraj podm |
Dodavanje trakastih stupaca i oblikovanje svim tablicama na radnom listu pomoću VBA
Pomoću zbirke ListObjects možete pristupiti svim tablicama na svom radnom listu. U donjem listu imamo dvije tablice i htjeli bismo dodati obrubljeni stupac u obje tablice odjednom i promijeniti font odjeljka s podacima u obje tablice u podebljano, koristeći VBA.
| 12345678910111213 | Pod AddingBandedColumns ()Dim tbl Kao ListObjectZatamni kao radni listPostavite sht = ThisWorkbook.ActiveSheetZa svaki tbl U sht.ListObjectstbl.ShowTableStyleColumnStripes = Istinatbl.DataBodyRange.Font.Bold = IstinaSljedeći tblKraj podm |
Rezultat je:
Stvaranje tablice u programu Access u VBA pomoću DoCmd.RunSQL
Jedan od glavnih načina za stvaranje tablice u Accessu u VBA -i je korištenje DoCmd.RunSQL metode za pokretanje upita radnje s SQL izrazom.
Na našem uzorku obrasca imamo gumb, a kad pritisnemo gumb, htjeli bismo stvoriti tablicu pod nazivom ProductsTable s dva polja ili stupca, jedno bi bilo polje primarnog ključa pod nazivom ProductsID, a drugo polje pod nazivom Prodaja.
Za izradu ove tablice koristili bismo sljedeći kod:
| 123456 | Privatna pod cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(PROIZVODNI INTEGER PRIMARNI KLJUČ, Prodajni cijeli broj);"Kraj podm |
Rezultat je:
Filtriranje tablice u programu Access pomoću VBA
Također možete filtrirati tablicu u programu Access pomoću metode DoCmd.ApplyFilter. Imamo jednostavnu tablicu prikazanu ispod u Accessu pod nazivom ProductsTable.
Željeli bismo pritisnuti ovaj gumb na našem obrascu i tada vidjeti samo prodaju veću od 1500.
Dakle, za to bismo upotrijebili sljedeći kod:
| 1234567 | Privatni pod cmdFilter_Click ()DoCmd.OpenTable "ProizvodiTablica"DoCmd.ApplyFilter, "[Prodaja]> 1500"Kraj podm |
Rezultat je:
