Работа с ячейками и диапазонами в VBA
VBA в Excel используется, как и следует из названия, для автоматизации обработки информации в файлах программы. Но без умения взаимодействия с объектами с помощью VBA автоматическая обработка данных невозможна. Работа с объектами рабочего листа в Excel VBA заключается в обращении к таким объектам и изменении их свойств. Поэтому важное значение обретает умение правильно обращаться с помощью VBA к объектам Excel, так как вся информация находится именно в них. В противном случае все встроенные операторы и функции, все процедуры, используемые VBA, станут не более чем красивой абстракцией.
На нашем занятии мы познакомимся с основными объектами рабочего листа, используемыми в VBA. К таким объектам относятся ячейки и диапазоны ячеек.
Самым маленьким в иерархии объектов является ячейка. Работа с ячейкой начинается с обращения к ней в коде макроса. Обратиться к ячейке можно двумя способами. Если речь идет о ячейке, выбранной в данный момент, то ее указывают как ActiveCell. Активной ячейка становится после щелчка по ней мышью или перемещения, а нее курсором в программе Excel. В VBA для выбора и активации ячейки используют метод Select. Если речь идет о конкретной ячейке, то ее указывают комбинацией Cells(R,C). Здесь R и C представляют соответственно номер строки и номер столбца листа, на пересечении которых располагается указанная ячейка. Данный вариант является практически полным аналогом абсолютной адресации стиля R1C1. Чтобы получить значение ячейки, или ввести значение в ячейку используется метод value. Для примера рассмотрим следующий код
Sub Test_Cell()
Cells(2,4).Select
Cells(1,2).Value=5
ActiveCell.Value=10
End Sub
Что произойдет после запуска данного макроса? Прежде всего стоить заметить, что, так как лист не указан, все операции будут выполнены на открытом листе. Прежде всего программа выберет ячейку D2, так как колонка D является 4 по счету. Затем в ячейку B2 запишется значение 5, а в текущую ячейку D2 – значение 10. Несмотря на свою простоту, код наглядно демонстрирует, что для внесения данных в VBA выделять ячейки не обязательно, достаточно их указать в коде. Это очень важно, так как в итоге можно, находясь на одном листе книги, выбирать данные на остальных листах, не перемещаясь между ними. Такой подход позволяет сократить код программы макроса и ускорить работу. Кроме этого, можно указать ячейки по их расположению относительно указанной ячейки. Для этого используют метод Offset. Перемещение вниз по строкам и вправо по столбцам указывают положительными числами. Перемещение по строкам вверх и по столбцам влево будет отрицательным.
Пример кода
Sub Test_Cell2
Cells(4,3).Select
ActiveCell.Value=1
ActiveCEll.Offset(1,1)=2
ActiveCEll.Offset(2,2)=3
End Sub
Результатом работы макроса будет выбор ячейки С3. Значение данной ячейки станет равным единице. Затем последовательно программа переместится относительно активной ячейки на одну строку вниз и одну колонку влево, а потом на две строки вниз и две колонки влево. Фактически будут выбраны (но не активированы!) ячейки D5 и Е6, куда и запишутся соответственно значения 2 и 3.
Кроме рассмотренных выше методов, объект Cells поддерживает множество других методов и свойств. Перечислим несколько из них. Для упрощения будем считать, что все эти методы применены к активной ячейке
1. WrapText. Включает или выключает перенос текста в ячейке.
ActiveCell.WrapText=true
2. EntireRow, EntireColumn – соответственно показывают номер строки и номер колонки выбранной ячейки. Эти свойства изменить нельзя, но допустимо использовать для работы. Рассмотрим вариант, в котором необходимо скрыть ячейку. Саму ячейку отдельно скрыть нельзя, поэтому мы скроем целиком строку с выбранной ячейкой
ActiveCell.EntireRow.Hidden=true
3. AutoFit – автоматически настраивает ширину и высоту ячейки по ее содержимому.
ActiveCell.AutoFit
4. Font –указываются настройка шрифта. Задается стиль (bold, Italic, UnderLIne), размер (Size) и другие параметры. К примеру, сделаем шрифт активной ячейки полужирным и зададим размер (кегль) равным 12
ActiveCell.Font.Bold=true
ActiveCell.Font.Size=12
5. Formula – задает в ячейке формулу для вычислений. Более подробно о вычислениях в VBA вы можете узнать на наших занятиях.
a = ActiveCell.Offset(-2, 0).Value
b = ActiveCell.Offset(-1, 0).Value
ActiveCell.Formula = “=” & a & “+” & b
Макрос суммирует значение двух ячеек, находящихся непосредственно над активной. Обратите внимание, что формула записывается как текстовая строка, начинающаяся со знака «=», а части этой строки разделены знаком амперсанда «&» с обязательным пробелом как перед ним, так и после него.
6. Interior – указывает оформление. Часто применяют, чтобы указать цвет заливки. Для указания цвета используют свойство ColorIndex либо Color. В первом случае указывают номер цвета их имеющихся 56 вариантов, во втором задают цвет с помощью комбинирования соотношения красного, зеленого и синего цветов палитрой RGB. Первая цифра в скобках отвечает за уровень красного цвета, вторая соответствует уровню зеленого, третья – синему. Максимальное значение уровня цвета равно 255.
ActiveCell.Interior.ColorIndex = 6
ActiveCell.Offset(1, 0).Interior.Color = RGB(255, 255, 0)
Оба указанных выше варианта зададут желтый цвет заливки.
7. Сору – Копирует содержимое ячейки. Можно сразу указать место назначения скопированных данных. Если это не сделать, выполнится копирование в буфер обмена.
8. Cut – вырезает содержимое ячейки.
Конечно, есть и другие свойства, и методы ячеек, здесь перечислены только наиболее часто применяемые из них.
Перейдем к использованию диапазонов в VBA. Работу с ними ведут с помощью объекта Rangе, указав в скобках нужный адрес. Например, команда
Range(“A2:D6”).value=10
Запишет значение 10 в каждую ячейку диапазона A2:D6. Обратите внимание, что адрес выбранного диапазона находится в кавычках. В качестве диапазона можно указать и одну ячейку, поэтому операторы ниже равносильны и активируют одну и ту же ячейку.
Cells(1,2).Select
Range(“B1”).Select
Кроме этого, диапазон задают, указав адрес его крайних угловых ячеек по диагонали и отделив их запятой. Обычно задают крайние верхнюю левую и нижнюю правую ячейку нужного диапазона. Таким образом, диапазон “A2:D6” можно указать так:
Range(Cells(2,1),Cells(6,4))
Несмежные диапазоны указывают двумя способами. Во-первых, отделяют адреса в объекте Range запятой
Range(“A1,B10”).Value = 180
Кроме этого, для указания несмежного диапазона используют метод Union
Union(Range(“A1”), Range(“B10”)).Value = 180
Оба варианта запишут значение 180 в диапазоны A1 и B10 одновременно, пропустив все ячейки между ними. Метод Union удобнее, если нужно применить цикл. О работе с циклами мы поговорим на следующих занятиях.
Объект Range поддерживает все методы и свойства, доступные для объектов Cells. Кроме этого, у данного объекта есть и другие свойства, и методы, которые либо нельзя использовать для объектов Cells, либо такое использование является бессмысленным.
1. Row – номер строки первой ячейки выбранного диапазона.
2. Column – номер столбца первой ячейки выбранного диапазона
3. Rows.Count и Columns.Count – количество строк и столбцов соответственно указанном диапазоне.
4. Sort – сортировка диапазона
5. AutoFilter – – аналог команды Excel «Данные» → «Фильтр» → «Автофильтр». Настройка фильтрации задается указанными для данного метода параметрами.
6. Clear, ClearComments, ClearContents, ClearFormats – выполняют очистку всего диапазона, комментариев, содержимого и формата диапазона соответственно.
7. Delete – удаляет диапазон. Необходимо указать смещение ячеек после удаления диапазона с помощью свойства Shift. Смещение влево задается как Shift=XlToLeft, для смещения вверх применяют вариант Shift:=XlUp
Стоит отметить тонкость обращения к активному диапазону в среде VBA. При работе с диапазонами для указания активного диапазона команды ActiveRange не существует. Аналогом объекта ActiveCell, указывающего на активную ячейку, для диапазонов является объект Selection. Он содержит все выделенные ячейки на листе. Доступ к данному объекту и работа с ним выполняется теми же методами и свойствами, что и при работе с объектом Range. Объект Selection позволяет более гибко обработать нужный диапазон, так как отсутствует привязка к конкретному адресу. Это очень удобно, так как расположение фактических данных обычно не совпадает с адресом указанного в макросе диапазона.
Кроме объекта Selection, содержащего выделенный диапазон, существую еще два варианта для выбора диапазона на рабочем листе. Это объекты CurrentRegion и UsedRange.
Объект CurrentRegion представляет собой диапазон, содержащий активную ячейку и ограниченный пустыми строками и столбцами, любо границами листа. Строки и столбцы, ограничивающие объект CurrentRegion не должны содержать не только данных и формул, но и форматирования.
Объект UsedRange содержит диапазон, в котором происходит работа пользователя. Этот диапазон содержит все ячейки листа, в которых обрабатывалось содержимое, в том числе форматы. При удалении ячеек с данными на листе они продолжают использоваться в Used Range. Чтобы их убрать, необходимо перейти в заполненную ячейку и сохранить файл. Кроме этого можно применить такой код
With ActiveSheet.UsedRange: End With
Обратите внимание, что для использования объекта CurrentRegion надо указать ячейку, относительно которой вычисляется адрес данного объекта. При использовании UsedRange требуется указать лист, на котором идет работа. Аналогично обращению к текущей ячейке текущий лист задают как ActiveSheet. Более подробно работу с листами рассмотрим в следующих занятиях.
Текущий же наш урок подошел к концу. Желаем всем хорошего дня и удачи в работе.
