Перейти к содержимому

VBA Excel. Создание таблицы

Создание таблицы с помощью кода VBA Excel. Создание «умной» и обычной пользовательской таблицы. Указание стиля «умной» таблицы. Примеры.

  1. Работа с «умной» таблицей
  2. Создание пользовательской таблицы
  3. Примеры создания таблиц

Работа с «умной» таблицей

Создание «умной» таблицы

Создается «умная» таблица Excel с помощью следующего кода:

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:

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 присвоить пустую строку:

ActiveSheet.ListObjects("МояТаблица1").TableStyle = ""

Этому коду соответствует выбор в разделе «Конструктор» на ленте инструментов Excel самого первого значка стилей в разделе «Светлый».

Добавление строки итогов

Строка итогов «умной» таблицы добавляется следующим образом:

ActiveSheet.ListObjects("МояТаблица1").ShowTotals = True

Удаляется строка итогов «умной» таблицы так:

ActiveSheet.ListObjects("МояТаблица1").ShowTotals = False

Удаление «умной» таблицы

Удалить умную таблицу очень просто:

ActiveSheet.ListObjects("МояТаблица1").Delete

Создание пользовательской таблицы

Мне не приходилось на практике с помощью VBA Excel создавать «умные» таблицы, в отличие от пользовательских таблиц, которые использовались для улучшения восприятия различных отчетов или сгенерированных документов.

Для создания такой таблицы необходимо:

Подробнее о создании пользовательской таблицы в Примере 2.

Примеры создания таблиц

Задание для примеров

Набор данных для создания таблиц
Набор данных для примеров создания таблиц

Допустим, на лист Excel переданы данные для заполнения табличной части товарного чека со следующими условиями:

  • в табличной части 5 граф: № п/п, Наименование, Количество, Цена и Сумма;
  • сколько наименований добавил пользователь неизвестно.

Нам необходимо:

  • определить количество строк;
  • добавить строку заголовков;
  • отобразить сетку (границы ячеек);
  • добавить строку итогов.

Таблицу будем оформлять двумя способами: путем создания «умной» и пользовательской таблиц.

Пример 1 - «умная» таблица

Упаковываем набор данных из задания в «умную» таблицу:

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 - «обычная» таблица

Упаковываем набор данных из задания в пользовательскую таблицу:

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, количество строк может быть любым, в пятой графе должны быть числа.