Цей підручник покаже, як працювати з таблицями та ListObjects у VBA.
Таблиці VBA та ListObjects
Таблиці-одна з найкорисніших і найпотужніших функцій Excel. У цьому підручнику ми розглянемо, як використовувати VBA для створення таблиці, додати до таблиці просте сортування, фільтрувати таблицю та виконувати інші завдання, пов’язані з таблицею.
Створіть таблицю з VBA
Метод ListObjects.Add може додавати таблицю до аркуша на основі діапазону в цьому аркуші. У нас є діапазон, показаний у ($ A $ 1: $ B $ 8) на робочому аркуші під назвою Sheet1.
Наступний код додасть до вашого аркуша таблицю під назвою Таблиця1 на основі діапазону ($ 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, щоб додати стовпець до кінця таблиці. У нас є наша таблиця під назвою Таблиця1, показана нижче.
Ви можете додати стовпець до таблиці за допомогою такого коду, який завжди додаватиме стовпець до кінця таблиці:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddEnd Sub |
Результат такий:
Вставка рядка внизу таблиці за допомогою VBA
Ви можете використовувати метод ListRows.Add, щоб додати рядок внизу таблиці. У нас є наша таблиця під назвою Таблиця1, показана нижче.
Наступний код завжди додаватиме рядок внизу таблиці.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Таблиця1"). ListRows.AddEnd Sub |
Результат такий:
Додавання простого сортування за допомогою VBA
Ви можете сортувати таблицю за допомогою VBA. У нас є наша таблиця під назвою Таблиця 1, показана нижче, і ми можемо використовувати 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 = Неправда.Орієнтація = xlTopToBottom.SortMethod = xlPinYin.ЗастосуватиЗакінчити зEnd Sub |
Результат такий:
Відфільтруйте таблицю з VBA
Ви також можете фільтрувати таблицю Excel за допомогою VBA. У нас є таблиця під назвою Таблиця 1, і ми хотіли б відфільтрувати таблицю так, щоб відображалися лише продажі більше 1500.
Ми можемо використовувати метод автофільтрації, який має п’ять додаткових параметрів. Оскільки ми хотіли б відфільтрувати стовпець Продажі, який є другим стовпцем, ми встановили для поля значення 2 і використовуємо параметр оператора xlAnd, який використовується для дат і чисел.
| 123456 | Додатковий простий фільтр ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Оператор: = xlAndEnd Sub |
Результат такий:
Очистіть фільтр за допомогою методу ShowAllData у VBA
Ви можете отримати доступ до методу ShowAllData класу Worksheet, щоб очистити фільтр. Якщо ви хочете очистити фільтр (и) таблиці, спершу вам потрібно вибрати клітинку в таблиці, що можна зробити у VBA.
Метод ShowAllData генерує помилку, якщо не використовувати умовну логіку, щоб перевірити, чи на робочому аркуші застосовано фільтр. Наступний код показує, як це зробити:
| 123456789 | Sub ClearingTheFilter ()Діапазон ("Таблиця1 [[#Заголовки], [Продажі]]"). ВиберітьЯкщо ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ТодіActiveSheet.ShowAllDataЗакінчити ЯкщоEnd Sub |
Очистити всі фільтри з таблиці Excel
Ви можете отримати доступ до методу ShowAllData класу ListObject без необхідності спочатку вибрати клітинку в таблиці. Наступний код показує, як це зробити:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataEnd Sub |
Видалення рядка за допомогою VBA
Ви можете видалити рядок у базі даних своєї таблиці за допомогою методу ListRows.Delete. Ви повинні вказати, який рядок, використовуючи номер рядка. У нас є наступна таблиця під назвою Таблиця1.
Скажімо, ви хотіли видалити другий рядок у тілі даних вашої таблиці, наступний код дозволить вам це зробити:
| 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 = ІстинаДалі таблEnd Sub |
Результат такий:
Створення таблиці в Access у VBA за допомогою DoCmd.RunSQL
Одним з основних способів створення таблиці в Access у VBA є використання методу DoCmd.RunSQL для виконання запиту дії з оператором SQL.
У нашій зразковій формі є кнопка, і коли ми натискаємо кнопку, ми хочемо створити таблицю з назвою ProductsTable з двома полями або стовпцями, одне з них буде полем первинного ключа під назвою ProductsID, а інше - полем під назвою Продажі.
Для створення цієї таблиці ми б використали такий код:
| 123456 | Приватний саб cmdCreateProductsTable_Click ()DoCmd.RunSQL "СТВОРИТИ ТАБЛИЦУ ProductsTable" _"End Sub |
Результат такий:
Фільтрація таблиці в Access за допомогою VBA
Ви також можете відфільтрувати таблицю в Access за допомогою методу DoCmd.ApplyFilter. У нас є наша проста таблиця, показана нижче в Access під назвою ProductsTable.
Ми хотіли б натиснути цю кнопку у нашій формі, а потім побачити лише продажі, які перевищують 1500.
Отже, для цього ми використали б такий код:
| 1234567 | Приватний саб cmdFilter_Click ()DoCmd.OpenTable "ПродуктиТаблиця"DoCmd.ApplyFilter, "[Продажі]> 1500"End Sub |
Результат такий:
