Tento tutoriál ukáže, ako pracovať s tabuľkami a ListObjects vo VBA.
Tabuľky VBA a ListObjects
Tabuľky sú jednou z najužitočnejších a najsilnejších funkcií Excelu. V tomto návode sa pozrieme na to, ako pomocou VBA vytvoriť tabuľku, pridať do tabuľky jednoduché triedenie, filtrovať ju a vykonávať ďalšie úlohy súvisiace s tabuľkou.
Vytvorte tabuľku pomocou VBA
Metóda ListObjects.Add môže pridať tabuľku do pracovného hárka na základe rozsahu v tomto pracovnom hárku. Máme rozsah uvedený v ($ A $ 1: $ B $ 8) na pracovnom hárku s názvom Sheet1.
Nasledujúci kód pridá do vášho pracovného hárka tabuľku s názvom Tabuľka1 na základe rozsahu ($ A $ 1: $ B $ 8) pomocou predvoleného štýlu tabuľky:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Stôl 1"Koniec pod |
Výsledkom je:
Vloženie stĺpca na koniec tabuľky pomocou VBA
Na pridanie stĺpca na koniec tabuľky môžete použiť metódu ListColumns.Add. Naša tabuľka s názvom Tabuľka1 je zobrazená nižšie.
Stĺpec môžete do tabuľky pridať pomocou nasledujúceho kódu, ktorý vždy pridá stĺpec na koniec tabuľky:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddKoniec pod |
Výsledkom je:
Vloženie riadka do spodnej časti tabuľky pomocou VBA
Na pridanie riadka do spodnej časti tabuľky môžete použiť metódu ListRows.Add. Naša tabuľka s názvom Tabuľka1 je zobrazená nižšie.
Nasledujúci kód vždy pridá riadok do spodnej časti tabuľky.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabuľka1"). ListRows.AddKoniec pod |
Výsledkom je:
Pridanie jednoduchého triedenia pomocou VBA
Tabuľku môžete triediť pomocou VBA. Naša tabuľka s názvom Tabuľka1 je zobrazená nižšie a pomocou VBA môžeme zoradiť stĺpec predaja od najnižšieho po najvyšší.
Nasledujúci kód zoradí stĺpce Predaj vo vzostupnom poradí.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Rozsah („Tabuľka1 [[#Hlavičky], [Predaj]]“). VyberteActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Kľúč: = Rozsah ("Tabuľka1 [[#Všetko], [Predaj]]"), SortOn: = xlSortOnValues, Poradie: = _xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Zoradiť.Header = xlÁno.MatchCase = False.Orientácia = xlTopToBottom.Trieda = xlPinYin.PoužiťUkončiť sKoniec pod |
Výsledkom je:
Filtrujte tabuľku pomocou VBA
Tabuľku programu Excel môžete filtrovať aj pomocou jazyka VBA. Máme tabuľku s názvom Tabuľka1 a chceli by sme tabuľku filtrovať tak, aby sa zobrazovali iba tržby vyššie ako 1 500.
Môžeme použiť metódu Autofilter, ktorá má päť voliteľných parametrov. Pretože by sme chceli filtrovať stĺpec Predaj, ktorý je druhým stĺpcom, nastavíme pole na 2, a používame parameter operátora xlAnd, ktorý sa používa pre dátumy a čísla.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", operátor: = xlAndKoniec pod |
Výsledkom je:
Vymažte filter metódou ShowAllData vo VBA
Ak chcete vymazať filter, môžete pristupovať k metóde ShowAllData triedy hárku. Ak chcete vymazať filtre tabuľky, musíte najskôr vybrať bunku v tabuľke, čo môžete urobiť vo VBA.
Metóda ShowAllData vygeneruje chybu, ak nepoužíva podmienenú logiku na kontrolu, či bol v pracovnom hárku použitý filter. Nasledujúci kód vám ukáže, ako to urobiť:
| 123456789 | PodúčtovanieTheFilter ()Rozsah („Tabuľka1 [[#Hlavičky], [Predaj]]“). VyberteAk ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ThenActiveSheet.ShowAllDataKoniec AkKoniec pod |
Vymažte všetky filtre z tabuľky programu Excel
K metóde ShowAllData triedy ListObject máte prístup bez toho, aby ste museli najskôr vybrať bunku v tabuľke. Nasledujúci kód vám ukáže, ako to urobiť:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataKoniec pod |
Odstránenie riadka pomocou VBA
Riadok v databáze vašej tabuľky môžete odstrániť pomocou metódy ListRows.Delete. Pomocou čísla riadka musíte určiť, ktorý riadok je. Máme nasledujúcu tabuľku s názvom Tabuľka1.
Povedzme, že ste chceli odstrániť druhý riadok v databáze vašej tabuľky, nasledujúci kód by vám to umožnil:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteKoniec pod |
Výsledkom je:
Odstránenie stĺpca pomocou VBA
Stĺpec môžete z tabuľky odstrániť pomocou metódy ListColumns.Delete. Nasledujúca tabuľka s názvom Tabuľka1 je uvedená nižšie:
Na odstránenie prvého stĺpca by ste použili nasledujúci kód:
| 12345 | Podradený stĺpec AC ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteKoniec pod |
Výsledkom je:
Konvertovanie tabuľky späť na rozsah vo VBA
Tabuľku môžete previesť späť na normálny rozsah pomocou VBA. Nasledujúci kód ukazuje, ako previesť tabuľku s názvom Table1 späť na rozsah:
| 12345 | SubkonvertovanieATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistKoniec pod |
Pridanie páskovaných stĺpcov a formátovanie do všetkých tabuliek v hárku pomocou jazyka VBA
K všetkým tabuľkám v pracovnom hárku máte prístup pomocou kolekcie ListObjects. V nižšie uvedenom hárku máme dve tabuľky a chceli by sme do oboch tabuliek naraz pridať pruhovaný stĺpček a pomocou VBA zmeniť písmo dátovej sekcie oboch tabuliek na tučné.
| 12345678910111213 | SubAddingBandedColumns ()Dim tbl ako ListObjectDim sht As ako pracovný listNastaviť sht = ThisWorkbook.ActiveSheetZa každý tbl v sht.ListObjectstbl.ShowTableStyleColumnStripes = Pravdatbl.DataBodyRange.Font.Bold = PravdaĎalej tblKoniec pod |
Výsledkom je:
Vytvorenie tabuľky v programe Access v jazyku VBA pomocou DoCmd.RunSQL
Jeden z hlavných spôsobov vytvorenia tabuľky v Accesse vo VBA je použitie metódy DoCmd.RunSQL na spustenie akčného dotazu s príkazom SQL.
V našom vzorovom formulári máme tlačidlo a keď klikneme na tlačidlo, chceli by sme vytvoriť tabuľku s názvom ProductsTable s dvoma poliami alebo stĺpcami, jedno by bolo poľom primárneho kľúča s názvom ProductsID a druhým poľom s názvom Predaj.
Na vytvorenie tejto tabuľky by sme použili nasledujúci kód:
| 123456 | Private Sub cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Koniec pod |
Výsledkom je:
Filtrovanie tabuľky v programe Access pomocou VBA
Tabuľku v Accesse môžete filtrovať aj pomocou metódy DoCmd.ApplyFilter. V Accesse máme nižšie uvedenú našu jednoduchú tabuľku s názvom ProductsTable.
Radi by sme stlačili toto tlačidlo na našom formulári a potom sa zobrazili iba predaje vyššie ako 1 500.
Na tento účel by sme použili nasledujúci kód:
| 1234567 | Súkromný podradený cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Predaj]> 1500"Koniec pod |
Výsledkom je:
