Умная таблица excel vba

Умная таблица excel vba

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

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

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

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

В данном примере:

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:

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю).

Результат, которого хотим добиться, выглядит примерно так:

Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

Читайте также:  Почему пропадают каналы на телевизоре

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Напишем Hello World:

Sub FormatPrice()
MsgBox "Hello World!"
End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

‘ Процедура. Ничего не возвращает
‘ Перегрузка в VBA отсутствует
Sub foo(a As String , b As String )
‘ Exit Sub ‘ Это значит "выйти из процедуры"
MsgBox a + ";" + b
End Sub

‘ Функция. Вовращает Integer
Function LengthSqr(x As Integer , y As Integer ) As Integer
‘ Exit Function
LengthSqr = x * x + y * y
End Function

Sub FormatPrice()
Dim s1 As String , s2 As String
s1 = "str1"
s2 = "str2"
If s1 <> s2 Then
foo "123" , "456" ‘ Скобки при вызове процедур запрещены
End If

Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( "1.234" ) ‘ Парсинг чисел
x = x + 10
MsgBox x

On Error Resume Next ‘ Обработка ошибок — игнорировать все ошибки
x = 5 / 0
MsgBox x

On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox "OK!"
GoTo ne

ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок

‘ Циклы бывает, какие захотите
Do While True
Exit Do

Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( "abc" ), Val( "4" ))
Case 24
MsgBox "0"
Case 25
MsgBox "1"
Case 26
MsgBox "2"
End Select

‘ Двухмерный массив.
‘ Можно также менять размеры командой ReDim (Preserve) — см. google
Dim arr(1 to 10, 5 to 6) As Integer
arr(1, 6) = 8

Dim coll As New Collection
Dim coll2 As Collection
coll.Add "item" , "key"
Set coll2 = coll ‘ Все присваивания объектов должны производится командой Set
MsgBox coll2( "key" )
Set coll2 = New Collection
MsgBox coll2.Count
End Sub

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Читайте также:  Как установить кидс контроль

Sub FormatPrice()
Sheets( "result" ).Cells.Clear
Sheets( "data" ).Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets( "result" ).Activate
Dim r As Range
Set r = Range( "A1" )
r.Value = "123"
Set r = Range( "A3,A5" )
r.Font.Color = vbRed
r.Value = "456"
Set r = Range( "A6:A7" )
r.Value = "=A1+A3"

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

  1. Считали группы из очередной строки.
  2. Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
  1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.
  • После отрисовки всех необходимых заголовков делаем еще одну строку и заполняем её данными.
  • Для упрощения работы рекомендую определить следующие функции-сокращения:

    Function GetCol(Col As Integer ) As String
    GetCol = Chr(Asc( "A" ) + Col)
    End Function

    Function GetCellS(Sheet As String , Col As Integer , Row As Integer ) As Range
    Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
    End Function

    Function GetCell(Col As Integer , Row As Integer ) As Range
    Set GetCell = Range(GetCol(Col) + CStr(Row))
    End Function

    Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

    Глобальные переменные

    Option Explicit ‘ про эту строчку я уже рассказывал
    Dim CurRow As Integer
    Const GroupsCount As Integer = 2
    Const DataCount As Integer = 3

    FormatPrice

    Sub FormatPrice()
    Dim I As Integer ‘ строка в data
    CurRow = 1
    Dim Groups(1 To GroupsCount) As String
    Dim PrGroups(1 To GroupsCount) As String

    Sheets( "data" ).Activate
    I = 2
    Do While True
    If GetCell(0, I).Value = "" Then Exit Do
    ‘ .
    I = I + 1
    Loop
    End Sub

    Теперь надо заполнить массив Groups:

    На месте многоточия

    Dim I2 As Integer
    For I2 = 1 To GroupsCount
    Groups(I2) = GetCell(I2, I)
    Next I2
    ‘ .
    For I2 = 1 To GroupsCount ‘ VB не умеет копировать массивы
    PrGroups(I2) = Groups(I2)
    Next I2
    I = I + 1

    И создать заголовки:

    На месте многоточия в предыдущем куске

    For I2 = 1 To GroupsCount
    If Groups(I2) <> PrGroups(I2) Then
    Dim I3 As Integer
    For I3 = I2 To GroupsCount
    AddHeader I3, Groups(I3)
    Next I3
    Exit For
    End If
    Next I2

    Не забудем про процедуру AddHeader:

    Перед FormatPrice

    Sub AddHeader(Ty As Integer , Name As String )
    GetCellS( "result" , 1, CurRow).Value = Name
    CurRow = CurRow + 1
    End Sub

    Теперь надо перенести всякую информацию в result

    For I2 = 0 To DataCount — 1
    GetCellS( "result" , I2, CurRow).Value = GetCell(I2, I)
    Next I2

    Подогнать столбцы по ширине и выбрать лист result для показа результата

    После цикла в конце FormatPrice

    Sheets( "Result" ).Activate
    Columns.AutoFit

    Всё. Можно любоваться первой версией.

    Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

    Sub AddHeader(Ty As Integer , Name As String )
    Sheets( "result" ).Range( "A" + CStr(CurRow) + ":C" + CStr(CurRow)).Merge
    ‘ Чтобы не заводить переменную и не писать каждый раз длинный вызов
    ‘ можно воспользоваться блоком With
    With GetCellS( "result" , 0, CurRow)
    .Value = Name
    .Font.Italic = True
    .Font.Name = "Cambria"
    Select Case Ty
    Case 1 ‘ Тип
    .Font.Bold = True
    .Font.Size = 16
    Case 2 ‘ Производитель
    .Font.Size = 12
    End Select
    .HorizontalAlignment = xlCenter
    End With
    CurRow = CurRow + 1
    End Sub

    Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

    Читайте также:  Как разблокировать телефон без отпечатка пальца

    Поэтому чуть-чуть меняем код с добавлением стиля границ:

    Sub AddHeader(Ty As Integer , Name As String )
    With Sheets( "result" ).Range( "A" + CStr(CurRow) + ":C" + CStr(CurRow))
    .Merge
    .Value = Name
    .Font.Italic = True
    .Font.Name = "Cambria"
    .HorizontalAlignment = xlCenter

    Select Case Ty
    Case 1 ‘ Тип
    .Font.Bold = True
    .Font.Size = 16
    .Borders(xlTop).Weight = xlThick
    Case 2 ‘ Производитель
    .Font.Size = 12
    .Borders(xlTop).Weight = xlMedium
    End Select
    .Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
    End With
    CurRow = CurRow + 1
    End Sub

    Осталось лишь добится пропусков перед началом новой группы. Это легко:

    В начале FormatPrice

    Dim I As Integer ‘ строка в data
    CurRow = 0 ‘ чтобы не было пропуска в самом начале
    Dim Groups(1 To GroupsCount) As String

    В цикле расстановки заголовков

    If Groups(I2) <> PrGroups(I2) Then
    CurRow = CurRow + 1
    Dim I3 As Integer

    В точности то, что и хотели.

    Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки "ID, Название, Цена" в результат. Подсказка: CurRow = 0 CurRow = 1.

    Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

    Спасибо за внимание.

    Буду рад конструктивной критике в комментариях.

    UPD: Перезалил пример на Dropbox и min.us.

    UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

    Возможно ли в Excel VBA ссылаться на именованную таблицу?

    Гипотетически это может быть.

    Я видел упоминание таблиц как объекта списка, но я не уверен, что это одно и то же.

    Возможно, это поможет вам

    Преобразование диапазона в таблицу начинается с того же кода, что и в Excel 2003 (как описано в этом ответе):

    OP спросил, возможно ли ссылаться на таблицу, а не на то, как добавить таблицу. Таким образом, рабочий эквивалент

    будет это утверждение:

    или для выбора частей (например, только данные в таблице):

    Для частей вы можете протестировать наличие заголовков и итоговых строк перед их выбором.

    И серьезно, это единственный вопрос о ссылках на таблицы в VBA в SO? Таблицы в Excel имеют такой смысл, но с ними так сложно работать в VBA!

    "Правильный" способ ссылки на таблицу заключается в получении его ListObject из его листа, т.е. SheetObject.ListObjects(ListObjectName) .

    Если вы хотите ссылаться на таблицу без использования листа, вы можете использовать hack Application.Range(ListObjectName).ListObject .

    ПРИМЕЧАНИЕ.. Этот хак полагается на то, что Excel всегда создает именованный диапазон для таблицы DataBodyRange с тем же именем, что и таблица. Однако это имя диапазона может быть изменено. хотя это не то, что вы хотели бы сделать, поскольку имя будет reset, если вы измените имя таблицы! Также вы можете получить именованный диапазон без связанного объекта ListObject.

    Учитывая, что сообщение об ошибке Excel не очень полезно 1004, когда вы ошибаетесь, вы можете создать оболочку.

    Также есть хорошая информация о ListObject здесь.

    Кроме того, удобно определять переменные, относящиеся к объектам. Например,

    Вероятно, вам будет выгодно сразу.

    В дополнение к вышесказанному вы можете это сделать (где "YourListObjectName" — это имя вашей таблицы):

    Но я думаю, что работает только если вы хотите ссылаться на объект списка, который на активном листе.

    Я нашел ваш вопрос, потому что я хотел ссылаться на объект списка (таблицу) на одном листе, на который ссылается сводная таблица на другом листе. Поскольку объекты списка являются частью коллекции Worksheets, вы должны знать имя листа, на который включен объект списка, чтобы ссылаться на него. Таким образом, чтобы получить имя рабочего листа, на котором включен объект списка, я получил имя объекта списка элементов сводной таблицы (опять же, таблицу) и зациклился на листах и ​​их объектах списка, пока не нашел лист, содержащий список объект, который я искал.

    Может быть, кто-то знает более прямой путь.

    Ссылка на основную публикацию
    Тест для определения цвета волос
    Пожалуйста, не копируйте понравившиеся вам статьи незаконно. Мы предлагаем вам разместить активную ссылку на наш сайт в случае, если вы...
    Стим показывает что я не в сети
    Не редко пользователи Steam встречаются с проблемой, когда подключение к интернету есть, браузеры работают, но клиент Стим не грузит страницы...
    Стим саппорт украли аккаунт
    Если ваш аккаунт Steam украли или взломали, то до его восстановления вам необходимо выполнить действия, указанные ниже, иначе аккаунт может...
    Тест графики видеокарты 3dmark
    Наиболее известная программа тестирования производительности, ставшая де-факто стандартом и точкой отсчета в измерениях игровых возможностей видеокарт. Основную популярность программе обеспечило...
    Adblock detector