Този урок ще покаже как се работи с таблици и ListObjects във VBA.
VBA таблици и ListObjects
Таблиците са една от най-полезните и мощни функции на Excel, в този урок ще разгледаме как да използваме VBA за създаване на таблица, добавяне на просто сортиране към таблица, филтриране на таблица и изпълнение на други задачи, свързани с таблицата.
Създайте таблица с VBA
Методът ListObjects.Add може да добави таблица към работен лист въз основа на диапазон в този работен лист. Имаме диапазона, показан в ($ A $ 1: $ B $ 8) на работен лист, наречен Sheet1.
Следният код ще добави таблица, наречена Table1 към вашия работен лист, въз основа на диапазона ($ A $ 1: $ B $ 8), използвайки стила на таблицата по подразбиране:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlДа). Име = _"Маса 1"End Sub |
Резултатът е:
Вмъкване на колона в края на таблицата с VBA
Можете да използвате метода ListColumns.Add, за да добавите колона в края на вашата таблица. Имаме нашата таблица, наречена Table1, показана по -долу.
Можете да добавите колона към таблицата си, като използвате следния код, който винаги ще добавя колона в края на таблицата:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddEnd Sub |
Резултатът е:
Вмъкване на ред в дъното на таблицата с VBA
Можете да използвате метода ListRows.Add, за да добавите ред в долната част на таблицата. Имаме нашата таблица, наречена Table1, показана по -долу.
Следният код винаги ще добавя ред в долната част на вашата таблица.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Таблица1"). ListRows.AddEnd Sub |
Резултатът е:
Добавяне на просто сортиране с VBA
Можете да сортирате таблица с VBA. Имаме нашата таблица, наречена Table1, показана по -долу и можем да използваме VBA, за да сортираме Колоната за продажби от най -ниската към най -високата.
Следният код ще сортира колоната Продажби във възходящ ред.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Диапазон ("Таблица1 [[#Заглавки], [Продажби]]"). ИзберетеActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Ключ: = Диапазон ("Таблица1 [[#Всички], [Продажби]]"), SortOn: = xlSortOnValues, Поръчка: = _xlНарастващо, DataOption: = xlSortNormalС ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Сортиране.Хедера = xlДа.MatchCase = False.Ориентация = xlTopToBottom.SortMethod = xlPinYin.ПриложиКрай сEnd Sub |
Резултатът е:
Филтрирайте таблица с VBA
Можете също така да филтрирате таблица в Excel, като използвате VBA. Имаме нашата таблица, наречена Table1 и бихме искали да филтрираме таблицата, така че да се показват само продажби над 1500.
Можем да използваме метода Autofilter, който има пет незадължителни параметъра. Тъй като бихме искали да филтрираме колоната „Продажби“, която е втората колона, задаваме полето на 2 и използваме операторския параметър xlAnd, който се използва за дати и числа.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Оператор: = xlAndEnd Sub |
Резултатът е:
Изчистете филтъра с метода ShowAllData във VBA
Можете да получите достъп до метода ShowAllData от класа на работен лист, за да изчистите филтъра. Ако това е филтър (и) на таблица, който искате да изчистите, първо трябва да изберете клетка в таблицата, което можете да направите във VBA.
Методът ShowAllData ще генерира грешка, ако човек не използва условна логика, за да провери дали е бил приложен филтър в работния лист. Следният код ви показва как да направите това:
| 123456789 | Sub ClearingTheFilter ()Диапазон ("Таблица1 [[#Заглавки], [Продажби]]"). ИзберетеАко ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = Истина тогаваActiveSheet.ShowAllDataКрай АкоEnd Sub |
Изчистете всички филтри от таблица на Excel
Можете да получите достъп до метода ShowAllData на класа ListObject, без да се налага първо да избирате клетка в таблицата. Следният код ви показва как да направите това:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataEnd Sub |
Изтриване на ред с VBA
Можете да изтриете ред в тялото на данни на вашата таблица, като използвате метода ListRows.Delete. Трябва да посочите кой ред, като използвате номера на реда. Имаме следната таблица, наречена Table1.
Да предположим, че сте искали да изтриете втория ред в тялото на данните на вашата таблица, следният код би ви позволил да направите това:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteEnd Sub |
Резултатът е:
Изтриване на колона с VBA
Можете да изтриете колона от таблицата си, като използвате метода ListColumns.Delete. Имаме следната таблица, наречена Таблица 1, показана по -долу:
За да изтриете първата колона, ще използвате следния код:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteEnd Sub |
Резултатът е:
Конвертиране на таблица обратно в диапазон във VBA
Можете да конвертирате таблица обратно в нормален диапазон с помощта на VBA. Следният код ви показва как да конвертирате таблица, наречена Table1 обратно в диапазон:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistEnd Sub |
Добавяне на лентови колони и форматиране към всички таблици в работен лист, използвайки VBA
Можете да получите достъп до всички таблици във вашия работен лист, като използвате колекцията ListObjects. В листа по -долу имаме две таблици и бихме искали да добавим лентова колона към двете таблици наведнъж и да променим шрифта на секцията с данни на двете таблици на удебелен, използвайки VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl като ListObjectDim sht като работен листЗадайте sht = ThisWorkbook.ActiveSheetЗа всеки tbl В sht.ListObjectstbl.ShowTableStyleColumnStripes = Вярноtbl.DataBodyRange.Font.Bold = ВярноСледващата tblEnd Sub |
Резултатът е:
Създаване на таблица в Access във VBA с помощта на DoCmd.RunSQL
Един от основните начини за създаване на таблица в Access във VBA е чрез използване на метода DoCmd.RunSQL за изпълнение на заявка за действие с SQL израз.
Имаме бутон на нашия примерен формуляр и когато щракнем върху бутона, бихме искали да създадем таблица, наречена ProductsTable с две полета или колони, едното би било полето с първичен ключ, наречено ProductsID, а другото би било поле, наречено Sales.
За да създадем тази таблица, бихме използвали следния код:
| 123456 | Частен под cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ИДЕНТИФИЦИРАН ИНТЕГЪР НА ПРОДУКТ, ПРОДЪЛЖИТЕЛЕН КЛЮЧ, Продажбено цяло число);"End Sub |
Резултатът е:
Филтриране на таблица в Access с помощта на VBA
Можете също така да филтрирате таблица в Access, като използвате метода DoCmd.ApplyFilter. Имаме нашата проста таблица, показана по -долу в Access, наречена ProductsTable.
Бихме искали да натиснем този бутон във формуляра си и след това да видим само продажби, които са по -големи от 1500.
Така че, ние ще използваме следния код, за да направим това:
| 1234567 | Частен под cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Продажби]> 1500"End Sub |
Резултатът е:
