Объекты Excel
Лекция из курса «Основы офисного программирования и документы Excel»

Биллиг Владимир Арнольдович
Интернет-Университет Информационных Технологий, INTUIT.ru

Назад Оглавление

Объекты Range и Selection

Объекты Range и Selection относятся к группе схожих объектов, встречающихся в различных приложениях Office 2000. Это основные объекты, с которыми приходится работать программисту. В приложении Word есть достаточно четкая логика в том, как создаются объекты Range. Объекты верхнего уровня, например, Document, имеют метод Range, позволяющий создать новый диапазон. Объекты более низкого уровня, например Paragraph, имеют свойство Range, возвращающее диапазон, связанный с объектом. В Excel ситуация другая. Все объекты Excel имеют только свойство Range. Это свойство имеют и верхний в иерархии объект Application и Worksheet и сам объект Range, представляющий объекты нижнего уровня вплоть до ячейки. Синтаксис этого свойства следующий:

Property Range(Cell1 [,Cell2]) As Range

С объектом Selection тоже дело обстоит не так просто как в приложении Word, поскольку в Excel нет класса объектов Selection. Объект Selection возникает двояко - либо в результате работы метода Select, либо при вызове свойства Selection. Тип полученного объекта может быть различным и определяется типом выделенного объекта. Чаще всего, объект Selection принадлежит классу Range и тогда при работе с ним можно использовать все свойства и методы объектов класса Range.

Вернемся к объекту Range, создаваемому, чаще всего, при вызове свойства Range тех или иных объектов Excel. Что может быть задано в качестве параметров Cell1 и Cell2? Давайте рассмотрим этот вопрос подробнее. Прежде всего, нужно понимать, что Range уникальный объект - он может представлять как единственную ячейку таблицы, так и столбец или строку, некоторую связную и не связную прямоугольную область, а также объединение и пересечение всех подобных элементов. Это же касается и объекта Selection. Параметры Cell1 и Cell2 это не просто имена ячеек таблицы. Они имеют непростой синтаксис и в общем случае могут быть достаточно сложными выражениями, позволяющими соответственно вернуть объект Range сложной конфигурации. Если при вызове используется только один параметр, то Cell1 может быть:

• Именем ячейки, например, - "A1"
• Диапазоном ячеек, например, - "A1: B5"
• Выражением над диапазонами, содержащим операции объединения (запятая) и пересечения (пробел), например, - "A1:B5, F1: G8" или "A1:B5 A3:G8"

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

Давайте начнем с простых примеров:

Public Sub WorkWithRS()
'Работа с объектами Range и Selection
'Example 1
   Workbooks("BookOne").Activate
   Worksheets("Лист2").Activate
   Range("A3") = 5
   Range("A4") = "=A3+2"
   Range("A5:A6") = "=A3+A4"

End Sub

Обратите внимание на следующие моменты:

1. Здесь при вызове Range во всех случаях используется только один параметр - и это ячейка или диапазон, заданные в формате "А1".
2. Цепочка вызовов начинается непосредственно с Range, по умолчанию это означает, что речь идет о свойстве Range активного листа. Заметьте, что это должен быть рабочий лист, иначе возникнет ошибка. Так что для полноты картины следовало писать, например Worksheets("Лист2").Range или ActiveSheet.Range.
3. Объекту Range мы присваиваем значения и формулы. Опять - таки здесь используется концепция умолчания, в результате присваивания определяются свойства Value или Formula объекта Range. По-видимому, лучше писать подобные присваивания в таком виде:

   'Example 2
      Range("B1").Value = 7
      Range("B2").Formula = "=B1+2"
      Range("B3:B4").Formula = "=B1+B2"

4. Когда формула присваивается диапазону ячеек, то переменные в формуле носят относительные имена и изменяются при переходе к очередной ячейке диапазона, так что формула, приписанная ячейке A6, будет иметь вид: "=A4 +A5"

Следующий пример демонстрирует важную еще одну важную для понимания относительность ссылок, задаваемых параметром Cell. Этот параметр задает ссылки, относительно объекта Range, вызвавшего свойство Range. Вот пример:

'Example 3
   Dim myRange As Range
   Set myRange = Range("C1:C4")
   myRange.Range("A1") = 7
   myRange.Range("B1") = 7
   myRange.Range("A2") = "=A3+2"
   myRange.Range("A3:A5") = "=A3+A4"

Вначале создается объект myRange, заданный диапазоном "C1:C4". Вызов myRange.Range("A1") определяет объект из одной ячейки А1, где адрес вычисляется относительно объекта myRange. Такая ссылка задает ячейку С1 в абсолютных адресах. Заметьте, что можно обращаться к любым ячейкам вне зависимости от того, какую область занимает вызывающий объект myRange, задающий по существу начальную точку отсчета. В нашем примере присваивается значение не только ячейкам A1 - A4, но и ячейкам B1 и A5, не входящих в диапазон, определяемый объектом myRange. Остается еще заметить, что если в левой части операторов присваивания А1 и А2 - это ссылки относительно объекта myRange, то в формулах правой части А1 и А2 привязаны к абсолютным адресам.

Наш следующий пример демонстрирует работу с объектом Selection:

'Example 4
   Range("D1").Select
   Selection.Range("A1") = 7
   Selection.Range("A2") = "=C1+2"
   Selection.Range("A3:A4") = "=C1+C2"

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

Рассмотрим теперь вызов Range с двумя параметрами:

'Example 5
   Dim myRange1 As Range
   Set myRange1 = Range("E1", "E6")
   Debug.Print myRange1.Count
   myRange1.Range("A1") = 27
   myRange1.Range("A2") = "=D1+2"
   myRange1.Range("A3:A6") = "=D1+D2"

Здесь в роли параметров метода Range выступают ячейки, первая из них определяет левый верхний, а вторая - правый нижний элемент диапазона объекта Range. Далее с этим объектом работаем также как и в предыдущем примере. В качестве параметров Cell1 и Cell2 могут выступать не только ячейки, но и объекты Range, что и демонстрирует следующий пример. Возвращаемый объект Range в этом случае представляет объединение областей, охватывающее область первого и второго объектов. Вот пример:

'Example 6
   Dim myr1 As Range, myr2 As Range, myr3 As Range
   Set myr1 = Range("A11:C15")
   myr1 = 33
   Set myr2 = Range("A13:F14")
   myr2 = 44
   Set myr3 = Range(myr1, myr2)
   Debug.Print myr3.Cells.Count
   Debug.Print myr3.Cells(1, 1)

Прямоугольная область, полученная в результате, будет состоять из 30 элементов, и первый элемент этой области будет иметь значение 33, что и отражают результаты отладочной печати. Пример демонстрирует, как можно построить объединение диапазонов, дающее в результате новый непрерывный диапазон, включающий области обоих объектов. Покажем теперь, как построить "настоящее" объединение и пересечение диапазонов. В настоящем объединении могут быть несмежные диапазоны. Оно включает те и только те ячейки, которые принадлежат объединяемым объектам. Чтобы задать объединение или пересечение диапазонов, нужно вызвать Range с одним параметром, Этот параметр в этом случае представляет список элементов, разделенный знаком объединения -"," (запятая) или знаком пересечения - " " (пробел). Каждый элемент списка представляет диапазон, возможно, ячейку. Вот соответствующий пример

'Example 7
   Dim myRange3 As Range
   'Пример пересечения
   Set myRange3 = Range("A6:E6 E1:E6")
   ' Пересечение состоит из одного элемента E6
   Debug.Print myRange3.Count
   myRange3.Select
   'Пример объединения
   Dim myRange4 As Range
   Set myRange4 = Range("A6:E6, E1:E6")
   'Объединение содержит 11 (!) элементов
   Debug.Print myRange4.Count
   myRange4.Select

Следует обратить внимание на несколько моментов. Во-первых, выделяется хотя и связная, но не прямоугольная область - такой уголок, состоящий из строки и столбца. Во- вторых, наше объединение не совсем "настоящее". В математике общие элементы присутствуют в одном экземпляре. Здесь же объединяются списки элементов без всякого их выбрасывания, так что "угол" E6 будет присутствовать дважды. В третьих, заметьте, при построении пересечения и объединения нельзя использовать переменные - диапазоны должны быть заданы константами. Следующий пример приводит к ошибке, если, конечно, убрать знаки комментария.

'Example 8
Dim myRange5 As Range
'Set myRange5 = Range("myRange1, myRange2")

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

'Example 9
   Dim myRange5 As Range
   Set myRange5 = Range("A6:E6, E1:E6, C1:C6 B5:D5")
   Debug.Print myRange5.Count
   myRange5.Select

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

Рис. 3.20.  Экзотический объект Range

Адресация ячеек

До сих пор в наших примерах мы использовали так называемую относительную адресацию ячеек таблицы в формате А1. Имя ячейки в этом формате строится из имени столбца (их 256 - A, B,…Z, AB, …AZ,….HZ, IA, …IV) и номера строки (1..65536). Адреса ячеек в этом формате, как мы видели при рассмотрении свойства Range, являются относительными. В этом случае начало координат, задающее нумерацию строк и столбцов, связывается с объектом, вызвавшим Range. Относительные ссылки вещь весьма полезная. Но иногда наряду с относительными ссылками требуются и абсолютные ссылки. Excel достаточно гибок в этом вопросе и предоставляет возможность независимых абсолютных ссылок на строку и столбец. Признаком абсолютной ссылки является знак "$", предшествующий имени строки и (или) столбца. Так что адреса: Z10, Z$10, $Z10, $Z$10 в зависимости от контекста могут именовать одну и ту же или четыре разные ячейки. Адрес ячейки на рабочем листе является лишь частью полного адреса, который, в общем случае, включает адрес листа и адр ес книги. При задании полного адреса имя листа сопровождается знаком "!". Имя книги должно заключаться в квадратные скобки. Эта книга должна быть открыта. Вот пример адресации:

'Example 10
   Debug.Print Range("$A$3").Value
   Debug.Print Range("Лист2!$A$3").Value
   Debug.Print Range("[BookOne.xls]Лист2!$A$3").Value

В первой строке приведена абсолютная ссылка на ячейку А1 текущего рабочего листа (Лист2), во второй - ссылка на эту же ячейку, но имя включает и имя листа, в третьей - дано полное имя, включающее имя книги.

Формат R1C1

Для задания адресов ячеек помимо формата A1 можно использовать и другой, так называемый R1C1 - формат. Он называется так, поскольку адрес задается индексом строки (Row) и индексом столбца (Column). И здесь ссылки бывают абсолютными и относительными. В абсолютных ссылках указываются действительно индексы ячейки, в относительных - их смещение по отношению к активной ячейке. Смещение в относительных ссылках задается в квадратных скобках и со знаком, указывающим направление смещения по отношению к активной ячейке. Вот пример, в формулах которого использовались ссылки формата R1C1:

'Example 11
   Range("A3") = 11
   Range("A4") = "=R1C1+5"
   Range("A5:A6") = "=R[-2]+R[-1]"

Здесь следует обратить внимание на два обстоятельства:

1. При вызове Range его параметры можно задавать только в формате А1. Поэтому в левой части мы сохранили "старый способ" адресации. В формулах целесообразнее применять адресацию в формате R1C1, чтобы явно подчеркнуть относительный характер ссылок, задаваемых в формулах.
2. Если, как часто бывает, вычисления в формулах, распространяются на диапазон, связанный с одним столбцом или одной строкой, то можно задать ссылку, используя только один индекс. В данном примере в последней строке было задано смещение по строкам, поскольку столбец остается неизменным и его можно не указывать

Смещение и свойство 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

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

Рис. 3.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.

Методы объекта Range

Объект Range имеет около 80 различных методов. Есть ли пересечение с методами объекта Worksheet? Есть, но оно незначительно. Общих методов примерно 10%. К таким методам относятся методы общего назначения: Activate, Calculate, CheckSpelling, Copy, Delete, PasteSpecial, PrintOut, Select. Замечу, что объект Range имеет общие методы не только с объектом Worksheet, но и со старшим в иерархии объектом Workbook. Так описанный ранее метод Run, позволяющий запускать макросы, есть и у объекта Range. Более 20 методов общего назначения входят в следующие группы, которые я лишь назову, не приводя подробного описания:

• Clear - 7 методов этой группы проводят разнообразную чистку содержания, комментариев и прочих деталей в области объекта Range.
• Copy, Fill - 9 методов этих близких групп выполняют копирование объекта Range и заполнения некоторого диапазона результатами копирования.
• Find - 3 метода позволяют проводить разнообразный поиск в области объекта Range.
• Show - 4 метода этой группы предназначены для отображения данных на экране дисплея.
• Sort - 2 метода выполняют сортировку данных в области объекта Range.

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

• Sub AutoFill(Destination As Range, [Type As XlAutoFillType = xlFillDefault]) - метод из группы Fill позволяет заполнить диапазон, заданный параметром Destination, используя значения объекта Range и тип заполнения, определенный параметром Type. Диапазон назначения Destination должен включать в себя исходный объект Range, вызвавший метод.
• Sub AutoFormat([Format As XlRangeAutoFormat = xlRangeAutoFormatClassic1], [Number], [Font], [Alignment], [Border], [Pattern], [Width]) - метод из группы методов форматирования включает автоматическое форматирование диапазона. Тип форматирования определяет первый параметр, остальные параметры имеют булевы и позволяют включать или выключать те или иные возможности форматирования. По умолчанию они включены. Если исходный диапазон представляет одну ячейку, то форматируется весь текущий регион, содержащий эту ячейку.
• Sub DataSeries([Rowcol], [Type As XlDataSeriesType = xlDataSeriesLinear], [Date As XlDataSeriesDate = xlDay], [Step], [Stop], [Trend]) - метод из группы методов, предназначенных для работы с данными. Позволяет создать автоматически ряд данных в указанном диапазоне, удовлетворяющий определенному закону построения. Первый параметр имеет два возможных значения: xlRows и xlColumns, указывающих, как будут заполняться данные - по строкам или столбцам. Второй параметр задает тип заполнения ряда данных. Остальные параметры позволяют управлять процессом заполнения данных. Вот небольшой пример на применение этого метода:

Public Sub WorkWD()
'Работа с методом DataSeries
   Dim Sh As Worksheet
   Dim myr As Range
   Set Sh = ThisWorkbook.Worksheets(1)
   Set myr = Sh.Range("C21:C32")
   myr.Cells(1, 1) = "31-Jan-2001"
   myr.DataSeries Type:=xlChronological, Date:=xlMonth
   Set myr = Sh.Range("D21:D32")
   myr.Cells(1, 1) = 320
   myr.DataSeries Type:=xlDataSeriesLinear, Step:=320

End Sub

Взгляните на два ряда данных, полученных в результате выполнения данной процедуры:

Рис. 3.22.  Автоматическое построение рядов данных

В следующих главах этой книги, посвященных работе с документами Excel, я рассмотрю по ходу дела применение многих свойств и методов как объекта Range, так и других объектов Excel. А сейчас поставлю точку в рассмотрении объектной модели Excel.

Назад Оглавление