Обработка ячеек и диапазонов ячеек рабочего листа

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

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

Основной объект, который позволяет сформировать такую ссылку— это объект Range. Этот объект может представлять собой одиночную ячейку, строку или столбец, группу ячеек одного листа или даже объемный диапазон, располагающийся на нескольких листах рабочей книги. Давайте познакомимся с основными методами и свойствами, которые могут возвращать ссылку на такой объект.

Пожалуй, самый естественный способ получения ссылки на объект Range — это метод Range. Имеются два вида синтаксиса для применения этого метода. Первый использует один аргумент:

объект. Range(интервал)

где:

объект — это объект типа Worksheet или Range, к которому применяется метод Range. Этот объект может быть опущен, и тогда предполагается, что метод Range применяется к активному рабочему листу — объекту ActiveSheet;

диапазон — это ссылка на диапазон, представляющая собой текстовую строку. Эта строка может быть ссылкой на диапазон в формате A1, при этом могут использоваться операторы диапазона (символ ":"), пересечения (символ пробела " "), или объединения (символ-разделитель списка — запятая ","). Например, следующий оператор ссылается на диапазон A1:B10 активного рабочего листа и очищает его содержимое:

Range("A1:B10").ClearContents

Строка, описывающая диапазон, может содержать символы $, задающие абсолютный стиль ссылок, но эти символы игнорируются и никак не влияют на определение диапазона. Если метод применяется к объекту типа Worksheet, то получается абсолютная ссылка на указанный диапазон рабочего листа; если же метод применяется к объекту типа Range, то ссылка считается относительной. Например, если текущая активная ячейка — это ячейка B3, то оператор

Selection.Range("B1")

возвращает ячейку C3, поскольку ссылка B1 считается относительной для объекта Range (активной ячейки B3), возвращаемого свойством Selection. В то же время оператор

ActiveSheet.Range("B1")

всегда возвращает ячейку B1.

Если вы используете имена для диапазонов ячеек рабочего листа, то они также могут использоваться в качестве ссылки на диапазон, например, оператор

Range("Условия").Copy

копирует поименованный диапазон "Условия" в буфер обмена.

Другой синтаксис для метода Range использует два аргумента:

объект. Range(ячейка1, ячейка2)

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

Хотя метод Range и позволяет получить отдельную ячейку рабочего листа, однако чаще всего для этого используется другой, более удобный для этого конкретного случая метод — метод Cells. Этот метод возвращает в качестве объекта Range одиночную ячейку или семейство одиночных ячеек, а его особенное удобство заключается в том, что он использует стиль ссылок R1C1, т.е. числовые значения, определяющие номер строки и столбца, на пересечении которых находится интересующая вас ячейка, или же просто порядковый номер ячейки на рабочем листе (напомним, что в одной строке рабочего листа Excel располагается 256 ячеек). Использование числовых аргументов особенно удобно при организации циклической обработки ячеек некоторого диапазона, что позволяет использовать в качестве счетчика цикла и аргумента метода Cells одну и ту же переменную.

Итак, метод Cells имеет три вида синтаксиса:

объект. Cells(номерСтроки, номерСтолбца)

объект. Cells(номерЯчейки)

объект. Cells

Нумерация строк, столбцов и ячеек начинается с единицы, так что Cells(2,3) возвращает ячейку C2, а Cells(257) — ячейку A2. Третий вид обращения к методу Cells, без указания аргументов, возвращает семейство всех ячеек объекта.

Вот пример использования метода Cells:

For номерСтолбца = 1 To 4

Cells(1,номерСтолбца).Value = "Квартал " & номерСтолбца

Next

Этот цикл записывает в первые четыре ячейки первой строки текстовые значения "Квартал 1", "Квартал 2", "Квартал 3" и "Квартал 4".

При использовании метода Cells вы применяете абсолютные номера строк и столбцов. Однако иногда бывает нужно обратиться к ячейке, абсолютные номера строки и столбца которой не известны — нужно сделать относительную ссылку, например, обратиться к ячейке, расположенной на два столбца правее и одну строку ниже активной ячейки. Конечно, можно выяснить абсолютный адрес активной ячейки, а затем вычислить адрес интересующей вас ячейки, но для этого существует другой, более удобный способ: использовать еще один метод — Offset (смещение). Вот синтаксис, используемый для этого метода:

объект. Offset(смещениеСтроки, смещениеСтолбца)

Смещение 0 означает отсутствие смещения, т.е. определяет использование тех же самых строк или столбцов, что и у первоначального объекта. Обратите внимание на то, что смещения могут быть и отрицательными числами.

Используемый объект должен принадлежать к классу Range, т.е. может быть ячейкой или диапазоном ячеек. Если это диапазон ячеек, то и результат применения метода Offset также будет являться диапазоном ячеек такого же размера, смещенного на указанное количество строк и столбцов. Например, оператор

Range("A1:C2").Offset(1,1).ClearContents

очищает содержимое диапазона ячеек B2:D3.