✅ Таблицы и объекты VBA

Содержание

Таблицы и объекты VBA

В этом руководстве будет показано, как работать с таблицами и объектами ListObject в VBA.

Таблицы и объекты VBA

Таблицы - одна из самых полезных и мощных функций Excel. В этом руководстве мы рассмотрим, как использовать VBA для создания таблицы, добавления простой сортировки в таблицу, фильтрации таблицы и выполнения других задач, связанных с таблицами.

Создать таблицу с VBA

Метод ListObjects.Add может добавлять таблицу на лист на основе диапазона на этом листе. У нас есть диапазон, показанный в ($ A $ 1: $ B $ 8) на листе под названием Sheet1.

Следующий код добавит на ваш рабочий лист таблицу с именем Table1 в зависимости от диапазона ($ A $ 1: $ B $ 8) с использованием стиля таблицы по умолчанию:

123456

Подложка CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Таблица 1"Конец подписки

Результат:

Вставка столбца в конец таблицы с помощью VBA

Вы можете использовать метод ListColumns.Add, чтобы добавить столбец в конец вашей таблицы. У нас есть таблица под названием Table1, показанная ниже.

Вы можете добавить столбец в свою таблицу, используя следующий код, который всегда будет добавлять столбец в конец таблицы:

12345

Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddКонец подписки

Результат:

Вставка строки внизу таблицы с помощью VBA

Вы можете использовать метод ListRows.Add, чтобы добавить строку в конец таблицы. У нас есть таблица под названием Table1, показанная ниже.

Следующий код всегда будет добавлять строку в конец таблицы.

12345

Подложка AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Таблица1"). ListRows.AddКонец подписки

Результат:

Добавление простой сортировки с помощью 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, Order: = _xlAscending, DataOption: = xlSortNormalС ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.Header = xlYes.MatchCase = Ложь.Orientation = xlTopToBottom.SortMethod = xlPinYin.Подать заявлениеКонец сКонец подписки

Результат:

Фильтрация таблицы с помощью VBA

Вы также можете фильтровать таблицу Excel с помощью VBA. У нас есть таблица с именем Table1, и мы хотели бы отфильтровать ее, чтобы отображались только продажи, превышающие 1500.

Мы можем использовать метод автофильтра, который имеет пять необязательных параметров. Поскольку мы хотим отфильтровать столбец «Продажи», который является вторым столбцом, мы устанавливаем для поля значение 2 и используем параметр оператора xlAnd, который используется для дат и чисел.

123456

Подложка SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", оператор: = xlAndКонец подписки

Результат:

Очистите фильтр с помощью метода ShowAllData в VBA

Вы можете получить доступ к методу ShowAllData класса Worksheet, чтобы очистить фильтр. Если вы хотите очистить фильтр (-ы) таблицы, вам сначала нужно выбрать ячейку в таблице, что вы можете сделать в VBA.

Метод ShowAllData сгенерирует ошибку, если не использовать условную логику, чтобы проверить, был ли применен фильтр на листе. В следующем коде показано, как это сделать:

123456789

Sub ClearingTheFilter ()Диапазон ("Таблица1 [[# заголовков], [Продажи]]"). ВыберитеЕсли ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True, тоActiveSheet.ShowAllDataКонец, еслиКонец подписки

Очистить все фильтры из таблицы Excel

Вы можете получить доступ к методу ShowAllData класса ListObject, не выбирая сначала ячейку в таблице. В следующем коде показано, как это сделать:

123	Подложка ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataКонец подписки

Удаление строки с помощью VBA

Вы можете удалить строку в базе данных вашей таблицы с помощью метода ListRows.Delete. Вы должны указать, какая строка используется по номеру строки. У нас есть следующая таблица под названием Table1.

Допустим, вы хотите удалить вторую строку в базе данных вашей таблицы, следующий код позволит вам это сделать:

12345

Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteКонец подписки

Результат:

Удаление столбца с помощью VBA

Вы можете удалить столбец из таблицы с помощью метода ListColumns.Delete. У нас есть следующая таблица под названием Table1, показанная ниже:

Чтобы удалить первый столбец, вы должны использовать следующий код:

12345

Подраздел DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteКонец подписки

Результат:

Преобразование таблицы обратно в диапазон в VBA

Вы можете преобразовать таблицу обратно в нормальный диапазон с помощью VBA. В следующем коде показано, как преобразовать таблицу с именем Table1 обратно в диапазон:

12345

Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistКонец подписки

Добавление чередующихся столбцов и форматирование ко всем таблицам на листе с помощью VBA

Вы можете получить доступ ко всем таблицам на вашем листе с помощью коллекции ListObjects. На приведенном ниже листе у нас есть две таблицы, и мы хотели бы добавить столбец с чередованием к обеим таблицам сразу и изменить шрифт раздела данных обеих таблиц на полужирный, используя VBA.

12345678910111213

Sub AddingBandedColumns ()Dim tbl As ListObjectТусклый лист как рабочий листУстановить sht = ThisWorkbook.ActiveSheetДля каждой табл. В шт.ListObjectstbl.ShowTableStyleColumnStripes = Истинаtbl.DataBodyRange.Font.Bold = ИстинаСледующая таблицаКонец подписки

Результат:

Создание таблицы в Access в VBA с использованием DoCmd.RunSQL

Одним из основных способов создания таблицы в Access в VBA является использование метода DoCmd.RunSQL для выполнения запроса действия с оператором SQL.

У нас есть кнопка в нашем образце формы, и когда мы нажимаем на кнопку, мы хотим создать таблицу с именем ProductsTable с двумя полями или столбцами, одно будет полем первичного ключа с именем ProductsID, а другое будет полем с именем Sales.

Чтобы создать эту таблицу, мы будем использовать следующий код:

123456

Частная подпрограмма cmdCreateProductsTable_Click ()DoCmd.RunSQL "СОЗДАТЬ ТАБЛИЦУ ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, целое число продаж);"Конец подписки

Результат:

Фильтрация таблицы в доступе с помощью VBA

Вы также можете фильтровать таблицу в Access с помощью метода DoCmd.ApplyFilter. У нас есть наша простая таблица, показанная ниже в Access, которая называется ProductsTable.

Мы хотели бы нажать эту кнопку в нашей форме, и тогда мы увидим только продажи, превышающие 1500.