Acest tutorial va arăta cum să lucrați cu Tables și ListObjects în VBA.
Tabelele VBA și ListObjects
Tabelele sunt una dintre cele mai utile și mai puternice caracteristici ale Excelului, în acest tutorial, vom trece în revistă modul de utilizare a VBA pentru a crea un tabel, vom adăuga o sortare simplă la un tabel, vom filtra un tabel și vom efectua alte sarcini legate de tabel.
Creați un tabel cu VBA
Metoda ListObjects.Add poate adăuga un tabel la o foaie de lucru, pe baza unui interval din foaia de lucru respectivă. Avem intervalul afișat în ($ A $ 1: $ B $ 8) pe o foaie de lucru numită Sheet1.
Următorul cod va adăuga un tabel, numit Table1 în foaia de lucru, pe baza intervalului ($ A $ 1: $ B $ 8) utilizând stilul de tabel implicit:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Tabelul 1"Sfârșitul Sub |
Rezultatul este:
Inserarea unei coloane la sfârșitul tabelului cu VBA
Puteți utiliza metoda ListColumns.Add pentru a adăuga o coloană la sfârșitul tabelului. Tabelul nostru numit Tabelul 1 este prezentat mai jos.
Puteți adăuga o coloană în tabel folosind următorul cod, care va adăuga întotdeauna o coloană la sfârșitul tabelului:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddSfârșitul Sub |
Rezultatul este:
Introducerea unui rând în partea de jos a tabelului cu VBA
Puteți utiliza metoda ListRows.Add pentru a adăuga un rând în partea de jos a tabelului. Tabelul nostru numit Tabelul 1 este prezentat mai jos.
Următorul cod va adăuga întotdeauna un rând în partea de jos a tabelului.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Table1"). ListRows.AddSfârșitul Sub |
Rezultatul este:
Adăugarea unui Sortare simplă cu VBA
Puteți sorta un tabel cu VBA. Avem tabelul numit Tabelul 1 prezentat mai jos și putem folosi VBA pentru a sorta Coloana de vânzări de la cea mai mică la cea mai mare.
Următorul cod va sorta coloana Vânzări în ordine crescătoare.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Interval („Tabel1 [[# Anteturi], [Vânzări]]”). SelectațiActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Cheie: = Interval ("Tabelul 1 [[# Toate], [Vânzări]]"), SortOn: = xlSortOnValues, Comandă: = _xlAscending, DataOption: = xlSortNormalCu ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sortează.Header = xlDa.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicaSe termina cuSfârșitul Sub |
Rezultatul este:
Filtrează un tabel cu VBA
De asemenea, puteți filtra un tabel Excel folosind VBA. Avem tabelul nostru numit Tabelul 1 și am dori să filtrăm tabelul astfel încât să fie afișate numai vânzările mai mari de 1500.
Putem folosi metoda Autofilter, care are cinci parametri opționali. Deoarece dorim să filtrăm coloana Vânzări care este a doua coloană, setăm câmpul la 2 și folosim parametrul operator xlAnd, care este utilizat pentru date și numere.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Operator: = xlAndSfârșitul Sub |
Rezultatul este:
Ștergeți filtrul cu metoda ShowAllData în VBA
Puteți accesa Metoda ShowAllData a clasei Foaie de lucru pentru a șterge filtrul. Dacă doriți să ștergeți filtrele unui tabel, trebuie mai întâi să selectați o celulă din tabel, pe care o puteți face în VBA.
Metoda ShowAllData va genera o eroare dacă nu se folosește logica condițională pentru a verifica dacă a fost aplicat un filtru în foaia de lucru. Următorul cod vă arată cum să faceți acest lucru:
| 123456789 | Sub ClearingTheFilter ()Interval ("Tabelul 1 [[# Anteturi], [Vânzări]]"). SelectațiDacă ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True AtunciActiveSheet.ShowAllDataEnd IfSfârșitul Sub |
Ștergeți toate filtrele dintr-un tabel Excel
Puteți accesa metoda ShowAllData a clasei ListObject fără a fi nevoie să selectați mai întâi o celulă din tabel. Următorul cod vă arată cum să faceți acest lucru:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataSfârșitul Sub |
Ștergerea unui rând cu VBA
Puteți șterge un rând din corpul de date al tabelului dvs. utilizând metoda ListRows.Delete. Trebuie să specificați ce rând folosind numărul rândului. Avem următorul tabel numit Table1.
Să presupunem că ați dorit să ștergeți al doilea rând din corpul de date al tabelului dvs., următorul cod vă va permite să faceți acest lucru:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .StergeSfârșitul Sub |
Rezultatul este:
Ștergerea unei coloane cu VBA
Puteți șterge o coloană din tabel utilizând metoda ListColumns.Delete. Avem următorul tabel numit Tabelul 1 prezentat mai jos:
Pentru a șterge prima coloană, veți utiliza următorul cod:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1). ȘtergețiSfârșitul Sub |
Rezultatul este:
Conversia unui tabel înapoi la un interval în VBA
Puteți converti un tabel înapoi la un interval normal folosind VBA. Următorul cod vă arată cum să convertiți un tabel numit Table1 într-un interval:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistSfârșitul Sub |
Adăugarea coloanelor în bandă și formatarea la toate tabelele dintr-o foaie de lucru folosind VBA
Puteți accesa toate tabelele din foaia de lucru folosind colecția ListObjects. În foaia de mai jos avem două tabele și am dori să adăugăm o coloană bandată la ambele tabele simultan și să schimbăm fontul secțiunii de date a ambelor tabele în bold, folosind VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl Ca ListObjectDim sht Ca foaie de lucruSetați sht = ThisWorkbook.ActiveSheetPentru fiecare tbl In sht.ListObjectstbl.ShowTableStyleColumnStripes = Adevărattbl.DataBodyRange.Font.Bold = AdevăratUrmătorul tblSfârșitul Sub |
Rezultatul este:
Crearea unui tabel în Access în VBA folosind DoCmd.RunSQL
Una dintre principalele modalități de a crea un tabel în Access în VBA este prin utilizarea metodei DoCmd.RunSQL pentru a rula o interogare de acțiune cu o instrucțiune SQL.
Avem un buton pe formularul de eșantionare și, atunci când facem clic pe buton, am dori să creăm un tabel numit ProductsTable cu două câmpuri sau coloane, unul ar fi câmpul cheie principal numit ProductsID, iar celălalt ar fi un câmp numit Sales.
Pentru a crea acest tabel, vom folosi următorul cod:
| 123456 | Private Sub cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Sfârșitul Sub |
Rezultatul este:
Filtrarea unui tabel în acces utilizând VBA
De asemenea, puteți filtra un tabel în Access utilizând metoda DoCmd.ApplyFilter. Avem tabelul nostru simplu prezentat mai jos în Access numit ProductsTable.
Am dori să apăsăm acest buton pe formularul nostru și apoi să vedem doar vânzări mai mari de 1500.
Deci, am folosi următorul cod pentru a face acest lucru:
| 1234567 | Private Sub cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, „[Vânzări]> 1500”Sfârșitul Sub |
Rezultatul este:
