Создание таблицы с помощью кода VBA Excel. Создание умной и обычной пользовательской таблицы. Указание стиля умной таблицы, добавление строки итогов. Примеры.
Создание и удаление умной таблицы
Создание умной таблицы
Создается умная таблица Excel с помощью следующего кода:
|
1 2 |
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$15"), , xlNo).Name _ = "МояТаблица1" |
В данном примере:
ActiveSheet — лист, на котором создается таблица, может быть любой лист рабочей книги Excel.
Range(«$A$1:$L$15») — диапазон, который преобразуется в таблицу. Можно использовать и такую форму: Range(Cells(1, 1), Cells(15, 12)), где индексы строк и столбцов можно заменить переменными.
xlNo — указывает, что первая строка выбранного диапазона не содержит заголовки столбцов (граф) будущей таблицы, и их необходимо добавить. В этом случае будет добавлена дополнительная строка с наименованиями столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д., которые в дальнейшем можно переименовать (количество строк в таблице, включая строку заголовков, получится на одну больше, чем в указанном диапазоне). Если в диапазоне уже содержатся заголовки столбцов будущей таблицы, то следует указать вместо xlNo значение xlYes. В этом случае первая строка указанного диапазона будет преобразована в строку заголовков, а если она будет не заполнена, то добавятся названия столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д. (количество строк в таблице, включая строку заголовков, будет то же, что и в указанном диапазоне).
МояТаблица1 — имя, присваиваемое создаваемой таблице. Имя должно быть без пробелов: при указании в коде VBA названия таблицы с пробелами, во время его выполнения Excel заменит пробелы знаками подчеркивания (по крайней мере, так происходит в Excel 2016).
Таблица будет создана со стилем по умолчанию (TableStyleMedium2 в Excel 2016).
Стиль умной таблицы
Присвоение стиля таблице (изменение стиля) осуществляется с помощью свойства TableStyle объекта ListObjects:
|
1 |
ActiveSheet.ListObjects("МояТаблица1").TableStyle = "TableStyleMedium15" |
Свойство TableStyle может принимать следующие значения:
- TableStyleLight (светлый) с индексом от 1 до 21 (в Excel 2016);
- TableStyleMedium (средний) с индексом от 1 до 28 (в Excel 2016);
- TableStyleDark (темный) с индексом от 1 до 11 (в Excel 2016).
Например, TableStyleLight5, TableStyleMedium24, TableStyleDark8.
Чтобы отменить стиль таблицы в коде VBA, необходимо свойству TableStyle присвоить пустую строку:
|
1 |
ActiveSheet.ListObjects("МояТаблица1").TableStyle = "" |
Этому коду соответствует выбор в разделе «Конструктор» на ленте инструментов Excel самого первого значка стилей в разделе «Светлый».
Добавление строки итогов
Строка итогов умной таблицы добавляется следующим образом:
|
1 |
ActiveSheet.ListObjects("МояТаблица1").ShowTotals = True |
Удаляется строка итогов умной таблицы так:
|
1 |
ActiveSheet.ListObjects("МояТаблица1").ShowTotals = False |
Удаление умной таблицы
Удалить умную таблицу очень просто:
|
1 |
ActiveSheet.ListObjects("МояТаблица1").Delete |
Создание пользовательской таблицы
Мне не приходилось на практике с помощью VBA Excel создавать умные таблицы, в отличие от пользовательских таблиц, которые использовались для улучшения восприятия различных отчетов или сгенерированных документов.
Для создания такой таблицы необходимо:
- определить диапазон, если он заранее не известен (иногда для этого может понадобиться определить номер последней заполненной строки);
- добавить границы ячеек;
- отформатировать строку заголовков;
- добавить строку итогов, если она необходима.
Подробнее о создании пользовательской таблицы в Примере 2.
Примеры создания таблиц
Задание для примеров
Допустим, на лист Excel переданы данные для заполнения табличной части товарного чека со следующими условиями:
- в табличной части 5 граф: № п/п, Наименование, Количество, Цена и Сумма;
- сколько наименований добавил пользователь неизвестно.
Нам необходимо:
- определить количество строк;
- добавить строку заголовков;
- отобразить сетку (границы ячеек);
- добавить строку итогов.
Таблицу будем оформлять двумя способами: путем создания умной и пользовательской таблиц.
Пример 1 — умная таблица
Упаковываем набор данных из задания в умную таблицу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub test1() 'Объявляем переменную для присвоения ей количества строк Dim a As Long 'Определяем количество строк a = Cells(1, 1).CurrentRegion.Rows.Count 'Создаем умную таблицу с добавлением строки заголовков ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1),Cells(a, 5)), , xlNo).Name _ = "ТоварныйЧек1" 'Изменяем названия граф Cells(1, 1) = "№ п/п" Cells(1, 2) = "Наименование" Cells(1, 3) = "Количество" Cells(1, 4) = "Цена" Cells(1, 5) = "Сумма" 'Добавляем строку итогов ActiveSheet.ListObjects("ТоварныйЧек1").ShowTotals = True 'Стиль оставляем по умолчанию End Sub |
Результат выполнения кода Примера 1 получится такой:
Пример 2 — «обычная» таблица
Упаковываем набор данных из задания в пользовательскую таблицу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub test2() 'Объявляем переменную для присвоения ей количества строк Dim a As Long 'Определяем количество строк a = Cells(1, 1).CurrentRegion.Rows.Count 'Добавляем строку заголовков Cells(1, 1).EntireRow.Insert 'Указываем названия граф Cells(1, 1) = "№ п/п" Cells(1, 2) = "Наименование" Cells(1, 3) = "Количество" Cells(1, 4) = "Цена" Cells(1, 5) = "Сумма" 'Добавляем сетку Range(Cells(1, 1), Cells(a + 1, 5)).Borders.LineStyle = True 'Добавляем строку итогов Cells(a + 2, 4) = "Итого:" With Cells(a + 2, 5) .FormulaR1C1 = "=SUM(R[-" & a & "]C:R[-1]C)" .Borders.LineStyle = True .Font.Bold = True End With 'Выделяем заголовки жирным шрифтом и 'применяем автоподстройку ширины столбцов With Range(Cells(1, 1), Cells(1, 5)) .Font.Bold = True .EntireColumn.AutoFit End With End Sub |
Результат выполнения кода Примера 2 получится такой:
Если решите поэкспериментировать с моим кодом, добавьте любые данные в пять колонок на активном листе Excel, количество строк может быть любым, в пятой графе должны быть числа.
О работе с умной таблицей (обращение к ячейкам, строкам и столбцам; добавление и удаление строк и столбцов) рассказано в статье VBA Excel. Работа с умной таблицей
Не совсем удобно изучать пример, когда в коде используется стиль ссылок R1C1, а таблица с A, B, C, D и т.д.
1) что значит xlSrcRange ?
2) что значит Range(Cells(1, 1),Cells(a, 5)) — почему нельзя сразу указать (1, 5)? в начале же вроде присвоили a = (1, 1), не совсем понятно зачем все это дублировать
3) как здесь идет расчет сетки Range(Cells(1, 1), Cells(a + 1, 5)) ? и здесь Cells(a + 2, 4) ?
4) что значит SUM(R[-» & a & «]C:R[-1]C)» обычно формула СУММ по иному показывает.
Расчет сетки покажите на основании картинок, чтобы понятно было со ссылками типа А1:Е5 = это заголовки например и т.д.
Привет, Рустем!
1)
xlSrcRange– это константа, которая указывает, что таблица будет создана в диапазоне ячеек, заданном значением следующего параметра (в первом примере это –Range(Cells(1, 1),Cells(a, 5))).2)
Range(Cells(1, 1),Cells(a, 5))– это прямоугольный диапазон, ограниченный верхней левой ячейкойCells(1, 1)и правой нижней –Cells(a, 5). Строкаa = Cells(1, 1).CurrentRegion.Rows.Countзаписывает в переменную a количество строк исходной таблицы без заголовков (Задание для примеров). Использование переменной a полезно, когда неизвестно количество строк в исходной таблице.3)
Range(Cells(1, 1), Cells(a + 1, 5))– это прямоугольный диапазон, включающий в себя строку заголовков.Cells(a + 2, 4)– это ячейка, в которую записано слово «Итого:». Границы добавляются к ячейкеCells(a + 2, 5).4) Строку
.FormulaR1C1 = "=SUM(R[-" & a & "]C:R[-1]C)"можно заменить строкой.Formula = "=SUM(E2:E" & a + 1 & ")".Евгений, день добрый!
А возможно сделать, чтобы макрос сам определял границы умной таблицы и не включал в неё пустые столбцы/строки?
Добрый день, Григорий!
Чтобы определить границы обычной таблицы для ее преобразования в умную, надо знать адрес хотя бы одной ячейки, входящей в обычную таблицу. Допустим, мы знаем, что в обычную таблицу входит ячейка
Cells(4, 5), тогда мы можем определить диапазон таблицы и преобразовать его в умную таблицу:Свойство Range.CurrentRegion возвращает диапазон, окруженный пустыми строками и столбцами.
Обсуждение закрыто.