Смещение и свойство Offset

Мы только что сказали, что при создании объектов Range нельзя пользоваться смещением — доступен только формат А1. Тем не менее, можно использовать смещение, чтобы переходить от одного объекта Range к другому, например от одной ячейки к другой, отстоящей от первой на определенном расстоянии. Достигается это благодаря свойству Offset объекта Range. Это свойство, или если хотите метод, имеет два параметра: RowOffset и ColumnOffset — смещение по строкам и столбцам, и возвращает новый объект Range, отстоящий от прежнего на заданное расстояние. Вот пример создания нового объекта, смещенного относительно исходного:

'Example 12 Set myRange = Range("A1:A4") Set myRange1 = myRange.Offset(2, 3) myRange1.Select

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

'Example 13 Dim currcell As Range For Each currcell In Range("E1:E6").Cells If Application.WorksheetFunction.IsText(currcell.Value) Then currcell.Offset(0, 1).Formula = "Text" ElseIf Application.WorksheetFunction.IsNumber(currcell.Value) Then currcell.Offset(0, 1).Formula = "Number" ElseIf Application.WorksheetFunction.IsLogical(currcell.Value) Then currcell.Offset(0, 1).Formula = "Logical" ElseIf Application.WorksheetFunction.IsError(currcell.Value) Then currcell.Offset(0, 1).Formula = "Error" ElseIf currcell.Formula = "" Then currcell.Offset(0, 1).Formula = "Пусто" End If Next currcell

Взгляните, как выглядят значения, хранящиеся в ячейках, и результаты их анализа:

Рис. 21 Результаты анализа значений, хранимых в ячейках E1- E6

Свойства и методы объекта Range

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

Сравнение свойств объектов Range и Worksheet

У этих двух объектов есть целый ряд общих свойств. Вот они:

• Свойства, возвращающие объект Range: Range, Cells, Columns, Rows. Понятно, что с помощью, например, свойства Range можно выделить некоторую область не только из области заданной рабочим листом, но и из любой подобласти, определенной объектом Range. Это же относится и ко всем другим свойствам, возвращающим объект Range, напоминающий матрешку.

• Hyperlinks – возвращает коллекцию гиперссылок, принадлежащих области объекта Range.

Целый ряд свойств объекта Range возвращают единственный объект, в то время как родительский объект Worksheet возвращает всю коллекцию. Вот эти свойства:

• Name - для Range возвращается не строка, задающая имя, а объект Name.

• Comment – комментарий.

• PivotTable – сводная таблица.

• QueryTable – таблица запросов.

Обратите внимание, на объект Range, возвращающий единственный объект, накладываются определенные требования. Так для того, чтобы вернуть комментарий, необходимо, чтобы объект Range представлял единственную ячейку, содержащую комментарий. Сводная таблица должна содержать верхний левый угол объекта Range. Честно скажу, логика создателей объектной модели не очень понятна. Почему возвращается коллекция гиперссылок, но не возвращается коллекция комментариев или сводных таблиц, которых, вообще говоря, может быть несколько в области объекта Range. На такие вопросы ответов нет, нужно просто знать спецификации. Вот небольшой пример работы со свойством Comment:

'Example 13 - какой комментарий возвращается? Dim Sh As Worksheet Dim myr As Range, s As String, c As Comment Set Sh = ThisWorkbook.Worksheets(1) Set c = Sh.Comments(1) s = c.Text Debug.Print s Set myr = Sh.Range("C16") myr.Select Set c = myr.Comment s = c.Text Debug.Print s

Следующую группу составляют похожие свойства. Я отношу к ним следующие свойства:

• OutlineLevel, - свойство, определенное для объектов Range, представляющих строки или столбцы. Задает уровень структурирования для текущей строки или столбца и связано со свойством Outline объекта Worksheet.

• PageBreak – разрывы страниц, также устанавливаемые для строк и столбцов. Свойство связано со свойствами HPageBreaks и VPageBreaks.

Упомяну еще ряд свойств, так или иначе пересекающихся со свойствами родительского объекта:

• CurrentArray, CurrentRegion - Первое из этих свойств возвращает весь массив, частью которого является объект Range (ячейка). Второе — возвращает текущий регион, то есть минимальную прямоугольную область, содержащую элементы из Range и окаймленную пустыми строками и столбцами или границами таблицы. Если Range представляет связную область, то текущий регион охватывает Range, для несвязной области он выделяет лишь некоторую его часть.

• EntireColumn, EntireRow - Эти два свойства возвращают один или несколько столбцов или строк, охватывающих объект Range.

• End – используется для объекта Range, представляющего одну ячейку. В качестве результата возвращается объект Range, также представляющий одну ячейку в конце региона, содержащего вызывающий свойство объект Range. Вот как, например, можно, используя это свойство, выделить область от искомой ячейки до конца региона:

'Example 14 Set myr = Sh.Range("F12") Sh.Range(myr, myr.End(xlToRight)).Select

• Dependents, Precedents, DirectDependents, DirectPrecedents - В первых двух случаях возвращается объект Range, содержащий все ячейки, зависимые или предшествующие ячейкам исходного объекта. Результат, обычно, представляет несвязную область. В двух последних случаях возвращаются только непосредственно зависимые и непосредственно предшествующие ячейки. Возможно, стоит определить понятия зависимых и предшествующих ячеек. Если формула в ячейке Y содержит ссылку на ячейку X, то говорят, что Y непосредственно зависит от X, а X непосредственно предшествует Y. Обобщая понятие непосредственной зависимости, мы говорим, Y зависит от X, а X предшествует Y, если существует цепочка ячеек Z1, Z2, …ZK, начинающаяся с X и заканчивающаяся Y, такая, что каждые два соседние элемента цепочки связаны отношением непосредственной зависимости (непосредственного предшествования). Свойство CircularReferences объекта Worksheet, возвращающее ячейки, связанные циклической зависимостью, тоже относится к этой группе свойств. О зависимых ячейках и примерах использования этих свойств подробный разговор еще предстоит.

• PivotItem, PivotField – возвращают элементы сводной таблицы, хранящиеся в объекте Range.

Терминальные и нетерминальные свойства объекта Range

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

Таблица 9 Свойства объекта Range

Свойство	Описание
Address, AddressLocals	Возвращает строку, задающую ссылку на Range объект. Во втором случае это ссылка в языке пользователя. Эту ссылку можно выдавать в формате A1 или R1C1, как абсолютную или относительную. Вид возвращаемого значения определяют параметры этого свойства (метода).
Areas	Применимо обычно к объекту Selection и возвращает коллекцию объектов Range в случае, когда Selection (Range) задает несвязную область. Возвращается сам объект Range, если область содержит только один объект.
Borders	Возвращает коллекцию из четырех границ объекта Range. Позволяет выделить цветом и (или) толщиной линии границы объекта.
Text, Characters	Свойство Text возвращает строку текста, связанного с Range объектом (ячейкой). Имеет статус только для чтения. Если нужно изменить весь текст или его часть, то можно использовать свойство (метод) Characters, два параметра которого: Start и Length позволяют выделить требуемую подстроку текста.
Column, Row	Возвращают соответственно номер первого столбца или первой строки в области объекта Range.
Font	Возвращает объект Font, используемый при написании текста в области объекта Range.
FormatConditions	Возвращает коллекцию условных форматов, содержащую не более трех элементов – объектов класса FormatCondition. Объект Range может иметь до трех условных форматов, выбор каждого из которых зависит от выполнения условия форматирования. Условие определяется параметрами объекта FormatCondition – оператором условия и константой, которая сравнивается со значением выражения, заданного объектом Range. В простейшем случае, когда объект Range задает ячейку, то значение в ячейке сравнивается с заданной константой. Метод Add коллекции позволяет задать новое условие форматирования. Методы Modify и Delete объекта FormatCondition позволяют модифицировать или удалять существующий формат. Параметры формата задаются с использованием объектов Borders, Font и Interior, возвращаемых свойствами объекта FormatCondition.
Formula, FormulaR1C1, FormulaArray, FormulaLocal, FormulaHidden, FormulaLabel, FormulaR1C1Local	Первое из них позволяет прочесть или задать формулу в формате A1, второе - в формате R1C1, третье -формулу над массивами. Остальные также так или иначе связаны с заданием формул.
Locked	Возвращает значение True, если объект закрыт для модификаций и False, если модификация данного объекта возможно, хотя рабочий лист защищен. Возвращается Null, есди в области объекта Range существуют закрытые и открытые ячейки.
Offset	Об этом свойстве, возвращающем объект Range, у уже подробно рассказывал.
Style	Свойство имеет статус «только для чтения» - возвращает объект Style, характерный для объекта Range.
Value	Значение указанной ячейки. Если она пуста, то возвращается значение Empty, что можно проверить, вызвав функцию IsEmpty. Если объект Range содержит более одной ячейки, то возвращается массив значений, что можно проверить, вызвав функцию IsArray. Функции IsNumber, IsText позволяют определить тип значения, хранимого в ячейке.