- •Объекты Excel
- •Объектная модель Excel
- •Объект Excel Application
- •Терминальные свойства
- •Методы объекта Application
- •События объекта Excel.Application
- •Создание объекта Application, реагирующего на события
- •Пример обработки события Change
- •Объект Workbook
- •Свойства-участники объекта Workbook
- •Группа свойств, возвращающих основные объекты рабочей книги. К ним я отношу следующие свойства:
- •К третьей группе относятся свойства, возвращающие объекты, специфические для рабочих книг Excel. К ним относятся:
- •Изменения в объектной модели объекта WorkSheet
- •Методы - "незнакомцы"
- •Методы – свойства
- •События объекта Worksheet
- •Объекты Range и Selection
- •Смещение и свойство Offset
- •Методы объекта Range
События объекта Worksheet
Со всеми событиями, которые может обрабатывать объект Worksheet, мы уже знакомы. Всего таких событий 8. Я напомню, что при возникновении события сообщение о нем операционная система посылает, как правило, нескольким объектам. Поэтому, когда возникает событие, связанное с рабочим листом, сообщение о нем будет послано и объектам Workbook и Application, стоящим на верхних уровнях иерархии. Все они, каждый по-своему, могут обрабатывать это событие. Подробно обо всем этом рассказано при рассмотрении событий объекта Application. Замечу еще, что объект Worksheet это последний объект в иерархии, для которого определены события, на нижних уровнях иерархии таких объектов нет.
Объекты 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
Обратите внимание на следующие моменты:
Здесь при вызове Range во всех случаях используется только один параметр — и это ячейка или диапазон, заданные в формате "А1".
Цепочка вызовов начинается непосредственно с Range, по умолчанию это означает, что речь идет о свойстве Range активного листа. Заметьте, что это должен быть рабочий лист, иначе возникнет ошибка. Так что для полноты картины следовало писать, например Worksheets("Лист2").Range или ActiveSheet.Range.
Объекту Range мы присваиваем значения и формулы. Опять - таки здесь используется концепция умолчания, в результате присваивания определяются свойства Value или Formula объекта Range. По-видимому, лучше писать подобные присваивания в таком виде:
'Example 2 Range("B1").Value = 7 Range("B2").Formula = "=B1+2" Range("B3:B4").Formula = "=B1+B2"
Когда формула присваивается диапазону ячеек, то переменные в формуле носят относительные имена и изменяются при переходе к очередной ячейке диапазона, так что формула, приписанная ячейке 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
Чтобы убедиться, что все построено правильно взгляните на картинку:
Рис. 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]"
Здесь следует обратить внимание на два обстоятельства:
При вызове Range его параметры можно задавать только в формате А1. Поэтому в левой части мы сохранили "старый способ" адресации. В формулах целесообразнее применять адресацию в формате R1C1, чтобы явно подчеркнуть относительный характер ссылок, задаваемых в формулах.
Если, как часто бывает, вычисления в формулах, распространяются на диапазон, связанный с одним столбцом или одной строкой, то можно задать ссылку, используя только один индекс. В данном примере в последней строке было задано смещение по строкам, поскольку столбец остается неизменным и его можно не указывать