Ten samouczek pokaże, jak pracować z tabelami i ListObjects w VBA.
Tabele VBA i obiekty listy
Tabele są jedną z najbardziej przydatnych i zaawansowanych funkcji programu Excel. W tym samouczku omówimy, jak używać VBA do tworzenia tabeli, dodawania prostego sortowania do tabeli, filtrowania tabeli i wykonywania innych zadań związanych z tabelą.
Utwórz tabelę za pomocą VBA
Metoda ListObjects.Add może dodać tabelę do arkusza na podstawie zakresu w tym arkuszu. Mamy zakres pokazany w ($ A 1: $ B 8 $) w arkuszu o nazwie Arkusz1.
Poniższy kod doda tabelę o nazwie Tabela1 do arkusza roboczego na podstawie zakresu ($A$1:$B$8) przy użyciu domyślnego stylu tabeli:
| 123456 | Sub CreateTableInExcel()ActiveWorkbook.Sheets("Arkusz1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _"Tabela 1"Napis końcowy |
Wynik to:
Wstawianie kolumny na końcu tabeli za pomocą VBA
Możesz użyć metody ListColumns.Add, aby dodać kolumnę na końcu tabeli. Mamy naszą tabelę o nazwie Table1 pokazaną poniżej.
Możesz dodać kolumnę do swojej tabeli, używając poniższego kodu, który zawsze doda kolumnę na końcu tabeli:
| 12345 | Sub Dodaj kolumnę do końca tabeli()ActiveWorkbook.Sheets("Arkusz1").ListObjects("Tabela1").ListColumns.AddNapis końcowy |
Wynik to:
Wstawianie wiersza na dole tabeli za pomocą VBA
Możesz użyć metody ListRows.Add, aby dodać wiersz na dole tabeli. Mamy naszą tabelę o nazwie Table1 pokazaną poniżej.
Poniższy kod zawsze doda wiersz na dole tabeli.
| 12345 | Sub AddRowToTheBottomoftheTable()ActiveSheet.ListObjects("Tabela1").ListRows.AddNapis końcowy |
Wynik to:
Dodawanie prostego sortowania za pomocą VBA
Możesz posortować tabelę za pomocą VBA. Mamy naszą tabelę o nazwie Table1 pokazaną poniżej i możemy użyć VBA do sortowania kolumny sprzedaży od najniższej do najwyższej.
Poniższy kod posortuje kolumnę Sales w kolejności rosnącej.
| 12345678910111213141516171819 | Sub proste sortowanie na tabeli()Zakres("Tabela1[[#Nagłówki],[Sprzedaż]]").WybierzActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").Sort.SortFields.Add _Klucz:=Zakres("Tabela1[[#Wszystko];[Sprzedaż]]"), SortOn:=xlSortOnValues, Order:= _xlRosnąco, DataOption:=xlSortNormalZ ActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").Sort.Nagłówek = xlTak.MatchCase = Fałsz.Orientacja = xlTopToBottom.Metoda sortowania = xlPinYin.ZastosowaćKończyć zNapis końcowy |
Wynik to:
Filtruj tabelę za pomocą VBA
Możesz także filtrować tabelę Excel za pomocą VBA. Mamy naszą tabelę o nazwie Table1 i chcielibyśmy ją przefiltrować tak, aby pokazywana była tylko sprzedaż powyżej 1500.
Możemy skorzystać z metody Autofiltr, która posiada pięć opcjonalnych parametrów. Ponieważ chcemy filtrować kolumnę Sales, która jest drugą kolumną, ustawiamy Field na 2 i używamy parametru operatora xlAnd, który jest używany do dat i liczb.
| 123456 | Sub prosty filtr()ActiveWorkbook.Sheets("Arkusz1").ListObjects("Tabela1").Range.AutoFilter Pole:=2, Kryteria1:= _">1500", Operator:=xlAndNapis końcowy |
Wynik to:
Wyczyść filtr za pomocą metody ShowAllData w VBA
Możesz uzyskać dostęp do metody ShowAllData klasy Worksheet, aby wyczyścić filtr. Jeśli chcesz wyczyścić filtry tabeli, najpierw musisz wybrać komórkę w tabeli, co możesz zrobić w VBA.
Metoda ShowAllData wygeneruje błąd, jeśli nie użyjemy logiki warunkowej w celu sprawdzenia, czy w arkuszu zastosowano filtr. Poniższy kod pokazuje, jak to zrobić:
| 123456789 | PodczyszczenieFiltra()Zakres("Tabela1[[#Nagłówki];[Sprzedaż]]").WybierzIf ActiveWorkbook.Worksheets("Arkusz1").FilterMode = True ThenActiveSheet.ShowAllDataZakończ, jeśliNapis końcowy |
Wyczyść wszystkie filtry z tabeli Excel
Dostęp do metody ShowAllData klasy ListObject można uzyskać bez konieczności wcześniejszego wybierania komórki w tabeli. Poniższy kod pokazuje, jak to zrobić:
| 123 | Sub ClearAllTableFilters()ActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").AutoFilter.ShowAllDataNapis końcowy |
Usuwanie wiersza za pomocą VBA
Możesz usunąć wiersz w treści danych tabeli przy użyciu metody ListRows.Delete. Musisz określić, który wiersz za pomocą numeru wiersza. Mamy następującą tabelę o nazwie Table1.
Załóżmy, że chcesz usunąć drugi wiersz w ciele danych swojej tabeli, poniższy kod pozwoli ci to zrobić:
| 12345 | Sub UsuńARow()ActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").ListRows(2).DeleteNapis końcowy |
Wynik to:
Usuwanie kolumny za pomocą VBA
Kolumnę z tabeli można usunąć przy użyciu metody ListColumns.Delete. Mamy poniższą tabelę o nazwie Table1 pokazaną poniżej:
Aby usunąć pierwszą kolumnę, użyjesz następującego kodu:
| 12345 | Sub Usuńkolumnę()ActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").ListColumns(1).DeleteNapis końcowy |
Wynik to:
Konwersja tabeli z powrotem na zakres w VBA
Możesz przekonwertować tabelę z powrotem do normalnego zakresu za pomocą VBA. Poniższy kod pokazuje, jak przekonwertować tabelę o nazwie Tabela1 z powrotem na zakres:
| 12345 | PodkonwertowanieATableBackToANormalRange()ActiveWorkbook.Sheets("Arkusz1").ListObjects("Tabela1").UnlistNapis końcowy |
Dodawanie kolumn naprzemiennych i formatowanie do wszystkich tabel w arkuszu za pomocą VBA
Możesz uzyskać dostęp do wszystkich tabel w arkuszu za pomocą kolekcji ListObjects. W poniższym arkuszu mamy dwie tabele i chcielibyśmy dodać kolumnę naprzemienną do obu tabel jednocześnie i zmienić czcionkę sekcji danych obu tabel na pogrubioną, używając VBA.
| 12345678910111213 | Dodawanie podrzędnych kolumn naprzemiennych()Dim tbl As ListObjectDim sht As WorksheetUstaw sht = ThisWorkbook.ActiveSheetDla każdej tabeli w sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = PrawdaNastępna tblNapis końcowy |
Wynik to:
Tworzenie tabeli w programie Access w VBA przy użyciu DoCmd.RunSQL
Jednym z głównych sposobów tworzenia tabeli w programie Access w języku VBA jest użycie metody DoCmd.RunSQL w celu uruchomienia kwerendy funkcjonalnej z instrukcją SQL.
Mamy przycisk na naszym przykładowym formularzu, a kiedy klikniemy przycisk, chcielibyśmy utworzyć tabelę o nazwie TabelaProduktów z dwoma polami lub kolumnami, jedno z nich byłoby polem klucza podstawowego o nazwie IdentyfikatorProduktu, a drugie byłoby polem o nazwie Sprzedaż.
W celu stworzenia tej tabeli użyjemy następującego kodu:
| 123456 | Prywatna sub cmdCreateProductsTable_Click()DoCmd.RunSQL "CREATE TABLE Tabela Produktów" _& "(ProductID INTEGER KLUCZ PODSTAWOWY, Sales Integer);"Napis końcowy |
Wynik to:
Filtrowanie tabeli w programie Access za pomocą VBA
Możesz również filtrować tabelę w programie Access przy użyciu metody DoCmd.ApplyFilter. Mamy naszą prostą tabelę pokazaną poniżej w programie Access o nazwie ProductsTable.
Chcielibyśmy nacisnąć ten przycisk w naszym formularzu, a następnie zobaczyć tylko sprzedaż większą niż 1500.
W tym celu użyjemy następującego kodu:
| 1234567 | Prywatna sub cmdFilter_Click()DoCmd.OpenTable "Tabela produktów"DoCmd.ApplyFilter , "[Sprzedaż]>1500"Napis końcowy |
Wynik to:
