Основы офисного программирования и документы Excel

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

До сих пор в наших примерах мы использовали так называемую относительную адресацию ячеек таблицы в формате А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), во второй - ссылка на эту же ячейку, но имя включает и имя листа, в третьей - дано полное имя, включающее имя книги.

Для задания адресов ячеек помимо

Для задания адресов ячеек помимо формата A1 можно использовать и другой, так называемый R1C1 - формат. Он называется так, поскольку адрес задается индексом строки (Row) и индексом столбца (Column). И здесь ссылки бывают абсолютными и относительными. В абсолютных ссылках указываются действительно индексы ячейки, в относительных - их смещение по отношению к активной ячейке. Смещение в относительных ссылках задается в квадратных скобках и со знаком, указывающим направление смещения по отношению к активной ячейке. Вот пример, в формулах которого использовались ссылки формата R1C1: 'Example 11 Range("A3") = 11 Range("A4") = "=R1C1+5" Range("A5:A6") = "=R[-2]+R[-1]"

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

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

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

Источники данных и структура объекта Chart

Диаграмма Excel, предоставляя пользователям широкий спектр возможностей по отображению данных, не может быть просто устроена, - она имеет достаточно сложную внутреннюю структуру. Соответственно, такую же сложную структуру имеет и объект Chart. Прежде чем переходить к деталям, давайте рассмотрим общую картину. В диаграмме можно выделить:

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

Область данных, в которой и строится собственно диаграмма.

Источник данных и данные этого источника. Эти объекты играют центральную роль, и о них я расскажу подробнее.

Заголовок диаграммы и другие подписи.

Оси.

Легенду.

Всем этим понятиям соответствуют целый ряд объектов, их свойств и методов. Рассмотрим более подробно источники данных и организацию данных при построении диаграммы. Одним из самых простых и наиболее употребительных источников данных является таблица Excel. Вот как выглядит таблица, используемая для построения диаграмм, показанных на рис. 3.11-3.14:

Рис. 3.15.  Таблица, являющаяся источником данных для построения диаграмм
Взгляните еще на одну таблицу Excel и диаграмму, для которой данная таблица выступает источником данных:

Рис. 3.16.  Диаграмма с источником данных
Для того чтобы можно было построить диаграмму, необходимо иметь, по крайней мере, один ряд данных. Напомню специальные термины, применяемые при построении диаграмм. Ось X называются осью категорий и значения, откладываемые на этой оси, называются категориями. Значения отображаемых в диаграмме функций и гистограмм составляют ряд данных. Ряд данных, представляющий последовательность числовых значений, является одним из центральных понятий в построении диаграмм. Этому понятию соответствует объект Series. Поскольку при построении диаграммы, как правило, используется несколько рядов данных, то важную роль играет понятие совокупности рядов данных, которой с объектной точки зрения соответствует коллекция - объект SeriesCollection. Замечу сразу, что все ряды в одной коллекции должны иметь одну и туже размерность. Excel позволяет строить смешанные диаграммы, где на одной диаграмме одновременно строятся несколько различного типа диаграмм, например, графики и гистограммы. В та кой смешанной диаграмме каждому типу соответствует своя группа данных. Группы одной диаграммы составляют коллекцию ChartGroups, элементы которой принадлежат классу ChartGroup. Итак, каждое значение, отображаемое на диаграмме, является членом некоторого ряда данных, ряды объединятся в коллекции. Коллекция рядов связывается с группой, каждая из которых позволяет отобразить диаграмму определенного вида. Группы объединяются в коллекцию групп. В общем случае, каждый ряд данных может иметь свой источник - свое положение, например диапазон ячеек некоторой рабочей книги Excel.
Чаще всего, хотя и не всегда, стараются делать так, чтобы у диаграммы был один источник данных, в роли которого выступает таблица Excel. Каждая из таблиц, показанных на двух предыдущих рисунках, служит единственным источником данных для соответствующих диаграмм. Поскольку построенные диаграммы не являются смешанными, то все данные таблицы составляют одну группу - одну совокупность рядов данных. Заметьте, удобно, как это сделано и в наших примерах, чтобы ряды в таблице были именованными. Если нужно задать значения категорий, то они должны составлять первый ряд таблицы, который должен быть неименованным, по крайней мере, в тех случаях, когда он состоит из числовых значений. Это позволяет отличить ряд категорий от обычных рядов данных. Значения категорий можно не задавать, тогда по умолчанию в этой роли выступает начальный отрезок натурального ряда - 1, 2, 3 и так далее. Заметьте также, в качестве рядов данных можно рассматривать как строки, так и столбцы таблицы. Можно построить диаграмму в предположении, что строки таблицы являются рядами данных, а можно полагать, что таковую роль играют столбцы. В наших примерах в первой таблице, отражающей динамику продаж, в качестве рядов использовались именованные столбцы таблицы; фамилии дилеров, осуществляющих продажу, выступают в качестве имен столбцов источника данных. Во второй таблице именованные строки задают ряды данных. Обе таблицы в качестве первого ряда задают значения, располагаемые на оси категорий.
Тип диаграммы влияет на ее структуру и предъявляет определенные требования к рядам данных. Так, для построения круговой диаграммы всегда используется только один ряд данных, для трехмерных диаграмм необходимо не менее трех рядов с учетом значений оси категорий. При построении двумерной диаграммы, отображающей графики, показанные на , используются три ряда данных - один из них задает значения координат на оси X, два других - соответствующий значения двух функций.
Хотя детальное обсуждение объектной модели Chart еще предстоит, хочу, предваряя это обсуждение привести пример программного построения диаграммы, где в качестве нескольких источников данных будут использоваться различные диапазоны ячеек рабочего листа Excel, а не единая таблица. По ходу дела в этом примере встретятся многие объекты, упоминавшиеся в этом обзоре, - Series, SeriesCollection и другие. Public Sub CreatingChart() 'Эта процедура строит диаграмму 'Добавить новую диаграмму на лист Dim MySh As Worksheet, MyChO As ChartObject, MyCh As Chart Dim Gr As ChartGroup, SC As SeriesCollection, Sr As Series Dim CG As ChartGroups Set MySh = ThisWorkbook.Worksheets(4) Set MyChO = MySh.ChartObjects.Add(50, 520, 320, 150) Set MyCh = MyChO.Chart

' Добавить ряды данных Set SC = MyCh.SeriesCollection SC.Add Source:=MySh.Range("B26:F27"), RowCol:=xlRows SC.Add Source:=MySh.Range("B29:F29"), RowCol:=xlRows 'Установить тип диаграммы для рядов данных Set Sr = MyCh.SeriesCollection(1) Sr.ChartType = xlColumnClustered Set Sr = MyCh.SeriesCollection(2) Sr.ChartType = xlColumnClustered Set Sr = MyCh.SeriesCollection(3) Sr.ChartType = xlLineMarkers

'Добавить свою ось для графика Sr.AxisGroup = xlSecondary

'Изменить ось категорий MyCh.Axes(xlCategory).CategoryNames = _ Range("C31:F31") 'Array("янв.", "февр.", "март", "апр.")

'Работа с группами Set CG = MyCh.ChartGroups Set Gr = CG(2) Debug.Print CG.Count Debug.Print ; Gr.SeriesCollection(1).Name

'Изменить внешний вид - цвет графика With Sr.Border .ColorIndex = 30 .Weight = xlThin .LineStyle = xlContinuous End With Sr.MarkerBackgroundColorIndex = xlColorIndexNone Sr.MarkerForegroundColorIndex = 30 End Sub

Прокомментируем работу этой программы:

На первом шаге ее работы создается сам объект Chart, представляющий диаграмму, встроенную в рабочий лист. Как обычно, для создания этого объекта вначале создается контейнер.

Теперь задаются ряды данных, и формируется коллекция SeriesCollection. Ряды создаются методом Add этой коллекции. Обратите внимание, для создания трех рядов данных используются два различных диапазона ячеек рабочей книги.

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

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

Следующий шаг демонстрирует работу с группами. Поскольку создана смешанная диаграмма с двумя разными типами, то и групп будет две. Отладочная печать показывает, что групп действительно две, и ряд, связанный со второй группой имеет имя "Цена".

На заключительном шаге работы изменяется цвет графика и маркеров, отмечающих точки на графике.

В заключение осталось взглянуть, как выглядит построенная диаграмма.

увеличить изображение: ,
Рис. 3.17.  Программно построенная диаграмма с несколькими источниками данных

В заключение этого параграфа следует сказать, что сегодня Excel интенсивно используется для проведения анализа данных, хранящихся в различных базах и хранилищах данных, в том числе для анализа OLAP - кубов, представляющих многомерные источники данных. Как правило, в этом случае для анализа таких данных в Excel строится сводная таблица. Источником данных для нее являются внешние источники - хранилища данных, а она сама является источником данных для построения сводной диаграммы, интерактивно изменяющейся в процессе работы со сводной таблицей. О сводных таблицах и сводных диаграммах - этом мощном инструменте анализа данных более подробный разговор пойдет в других главах этой книги.

После этих общего обзора перейдем к более детальному рассмотрению диаграмм с объектной точки зрения.

Изменения в объектной модели объекта WorkSheet

Изменения объектной модели не обошли стороной и рассматриваемый нами объект WorkSheet. Многие объекты, встроенные в объект WorkSheet, как, например, уже упоминавшийся объект QueryTable приобрели новые свойства и методы. Два новых свойства появились и у самого объекта Worksheet. Если новое терминальное свойство DisplayRightToLeft вряд ли представляет интерес для российских программистов, поскольку связано с правосторонними языками, то свойство-участник Scripts, возвращающее коллекцию объектов класса Script, представляет несомненный интерес. Каждый элемент этой коллекции задает блок script-кода, используемого в возможных сценариях при публикации рабочей книги в интернет.

Как получить объект Chart

Объект Chart задает диаграмму, расположенную на листе рабочей книги или на отдельном листе диаграммы. В зависимости от типа листа получение этого объекта ведется по-разному. Я уже об этом говорил, но хочу теперь систематизировать приводимые ранее сведения. Рассмотрим все возможные случаи:

Диаграмма встроена в рабочий лист. На рабочем листе может находиться несколько встроенных диаграмм. Коллекция ChartObjects задает совокупность объектов - контейнеров, содержащих эти диаграммы. Зная индекс или имя диаграммы, можно получить доступ к нужному элементу этой коллекции, а свойство Chart позволяет получить объект, задающий диаграмму. Так что вызов ThisWorkbook.Worksheet(3).ChartObjects(1).Chart

вернет объект Chart, задающий первую диаграмму, расположенную на третьем рабочем листе текущей рабочей книги. Я хотел выделить этот объект, вызвав его метод Select, но напрямую метод не вызывается, так что пришлось применить обходной маневр, активизировав вначале контейнер, а затем выделив область диаграммы:ThisWorkbook.Worksheets(3).ChartObjects(1).Activate ActiveChart.ChartArea.Select Так что заметьте, в этом случае приходится часто использовать оба объекта - ChartObjects и Chart для решения возникающих задач.

Диаграмма расположена на отдельном листе диаграммы. Специальные листы диаграмм рабочей книги составляют, как уже говорилось, коллекцию Charts. Хочу обратить внимание на два момента. Хотя коллекция представляет собой совокупность специальных листов рабочей книги, элементы этой коллекции являются не столько листами, сколько объектами Chart, задающими диаграммы. Эта ситуация не является типичной для коллекций. Связано это с тем, что на таком листе располагается только один объект Chart. Именно поэтому лист отождествляется с диаграммой. Вызов, который я сделал в отладочном окне Immediate: ThisWorkbook.Charts(1).Select ?ActiveChart.ChartTitle.Text

выделил в текущей рабочей книге первый лист с диаграммой, а, следовательно, сделал активной и саму диаграмму. После чего стало возможным получить заголовок диаграммы. Заметьте, в данной ситуации метод Select работает без проблем. Для получения диаграммы я использовал коллекцию Charts, но заметьте, эта коллекция является частью коллекции Sheets, поэтому тот же результат можно получить, используя эту коллекцию:ThisWorkbook.Sheets(2).Select ?ActiveChart.ChartTitle.Text

Активная диаграмма. Добраться до объекта Chart, задающего активную диаграмму, можно с использованием свойства ActiveChart, как показано в только что приведенных примерах. Еще раз хочу обратить внимание на то, что активизация листа диаграммы приводит и к активизации самой диаграммы. При работе с такими диаграммами можно использовать с тем же успехом и свойство ActiveSheet. Что же касается встроенных диаграмм, то там необходимо предварительно активизировать соответствующий контейнер.

Несколько диаграмм на одном листе диаграммы. Excel позволяет указать один и тот же лист диаграммы, как место расположения нескольких диаграмм. В этом случае новая диаграмма накладывается на область, отведенную первой диаграмме. Руками можно работать с несколькими диаграммами на таком листе, например, изменяя их параметры. Но программная работа возможна только с одним объектом, определяющим первую диаграмму. Более того, если, используя MacroRecorder, записать макрос, повторяющий работу руками с несколькими диаграммами одного листа, то этот макрос не будет корректно выполняться при его запуске. Суммируя это, рекомендую в своей работе придерживаться стратегии - одна диаграмма на листе диаграмм. Это обеспечит корректную программную работу с такими листами.

Коллекция Charts и объект Chart

Напомню, совокупность всех листов рабочей книги задается объектом Sheets. Этот объект, задающий коллекцию, представляет все листы рабочей книги независимо от их типа. Основных типов листов в рабочей книге два - WorkSheet и Chart, соответственно имеются и две коллекции для этих типов листов. Коллекцию WorkSheets мы уже рассмотрели, перейдем теперь к рассмотрению коллекции Charts.

Коллекция Charts

Эта коллекция является частью коллекции Sheets, - ее элементами являются объекты класса Chart, представляющие специальный тип листов рабочей книги - листы, содержащие диаграммы. Всякий раз, когда руками или программно создается диаграмма, ее можно встроить либо в рабочий лист, либо расположить на отдельном листе. Если предпочтение отдается второму варианту, то в коллекции Charts появляется новый элемент. Обратите внимание, все элементы этой коллекции являются объектами Chart, но не всякий объект Chart принадлежит этой коллекции.
Коллекция Charts содержит тот же набор свойств и методов, что и коллекция WorkSheets. Она содержит типичный набор свойств: Application, Count, Creator, Parent, Item. Кроме этих свойств имеется менее типичное свойство для коллекций свойство Visible и два свойства VpageBreaks и HpageBreaks, возвращающие одноименные коллекции, элементы которых задают вертикальное и горизонтальное деление листа на страницы, которые могут быть распечатаны. Обо всех этих свойствах я подробно рассказывал при описании коллекции WorkSheets.
Все методы, которые есть у коллекции WorkSheets, есть и у коллекции Charts. Вот эти методы: Add, Copy, Delete, Move, PrintOut, PrintPreview, Select. Я их уже описывал, поэтому повторяться не буду. Заметьте, отсутствует только один метод FillAcrossSheets, копирующий диапазон ячеек рабочего листа. Понятно, что для листов диаграмм этот метод не применим.

Коллекция Workbooks и объект Workbook

Документы, с которыми пользователь работает в Excel, называются рабочими книгами. Каждая рабочая книга представляется объектом WorkBook, а их коллекция - Workbooks.

Коллекция Workbooks

Коллекция Workbooks, содержащая все открытые рабочие книги, имеет обычные для коллекций свойства Application, Count, Creator, Parent и Item. У нее всего 4 метода:

Add([Template]) As Workbook - добавляет новую книгу в коллекцию. Новая книга создается на основе шаблона, заданного параметром Template. Если он опущен, то используется шаблон по умолчанию. Метод возвращает в качестве результата созданную рабочую книгу.

Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru]) As Workbook - открывает существующую книгу. При открытии можно задавать параметры, управляющие свойствами открываемого документа. Обязательным параметром является только первый, задающий имя файла, содержащего открываемую книгу.

Close -закрывает все книги коллекции. При закрытии той или иной книги может появиться диалоговое окно с предложением сохранить сделанные изменения.

OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier As XlTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator]) - открывает текстовый файл, содержащий таблицу, и создает рабочую книгу Excel, преобразуя текстовую таблицу в таблицу Excel. Это бывает важно, если таблицу, подготовленную в одном из текстовых редакторов, нужно импортировать в формат Excel. Обязательным параметром является имя файла, содержащего таблицу, подготовленную в текстовом редакторе. Остальные параметры задают характеристики таблицы. Конечно, для копирования таблицы, подготовленной в редакторе Word, проще использовать буфер и стандартную технику "Копировать" - "Вставить".

Коллекция WorkSheets и объект WorkSheet

Каждая рабочая книга состоит из листов. Совокупность всех листов рабочей книги задается объектом Sheets. Этот объект, задающий коллекцию, представляет все листы рабочей книги независимо от их типа. При программной работе часто полезно иметь дело с подколлекциями коллекции Sheets, содержащими листы только одного определенного типа. Основных типов листов в рабочей книге два - WorkSheet и Chart, соответственно имеются и две коллекции для этих типов листов. К рассмотрению коллекции WorkSheets мы сейчас и переходим.

Коллекция WorkSheets

Эта коллекция является частью коллекции Sheets, - ее элементами являются объекты класса WorkSheet, представляющие рабочие листы - листы электронных таблиц. По умолчанию при создании каждой новой рабочей книги в ее состав включаются три таких листа. С объектной точки зрения это означает, что при создании новой книги автоматически создается коллекция WorkSheets, содержащая три элемента. Как и всякая коллекция в Excel и Office 2000 данная коллекция содержит типичный набор свойств: Application, Count, Creator, Parent, Item. Кроме этих свойств имеется менее типичное свойство для коллекций свойство Visible, которое позволяет сделать видимыми или невидимыми рабочие листы книги. У коллекции WorkSheets есть еще два свойства VpageBreaks и HpageBreaks, возвращающие одноименные коллекции, элементы которых задают вертикальное и горизонтальное деление рабочего листа на страницы, которые могут быть распечатаны. Дело в том, что рабочий лист Excel имеет большие размеры по ширине и длине, так что его полностью нельзя увидеть ни на экране дисплея, ни при выводе на печать. Поэтому при печати часто приходится делить рабочий лист, вставляя разрывы по горизонтали и вертикали. Коллекции VpageBreaks и HpageBreaks содержат объекты, задающие эти разрывы.
Методов у коллекции WorkSheets немного, и практически все они типичны для коллекций:

Function Add([Before], [After], [Count], [Type]) As Object - позволяет добавить новый рабочий лист в книгу, возвращая соответствующий объект в качестве результата. Добавленный лист становится активным. Параметры Before и After позволяют указать, куда поместить добавленный лист, - перед или после листа, который до выполнения операции был активным. Параметр Count позволяет одновременно добавить несколько листов, задавая число этих листов. Параметр Type обычно не указывается, ранее он позволял добавлять листы макросов в версии Excel4.

Sub Copy([Before], [After]) - метод Copy вызывается объектом WorkSheets, чаще всего, для создания копии рабочей книги. В этом случае параметры метода не задаются. При копировании отдельной страницы параметры указывают, куда поместить ее копию. Понятно, что только один из этих параметров может быть указан в момент вызова метода.

Sub Delete() - удаляет коллекцию рабочих листов.

Sub FillAcrossSheets(Range As Range, [Type As XlFillWith = xlFillWithAll]) - область, заданная параметром Range, копируется в соответствующее место всех рабочих листов. Тип копирования задается вторым параметром, можно, например, копировать формулы, по умолчанию копируется все содержимое области, заданной параметром Range. Копируемый объект, естественно, должен быть частью одного из рабочих листов коллекции. Вот простой пример, демонстрирующий применение этого метода: Public Sub CopyRange() 'Копирование объекта Range первого листа 'на все листы рабочей книги. With ThisWorkbook .Worksheets.FillAcrossSheets (.Worksheets(1).Range("A7:C11")) End With End Sub

Sub Move([Before], [After]) - используется для перемещения листов. К коллекции его лучше не применять.

Sub PrintPreview([EnableChanges]), Sub PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName]) - используются для предварительного просмотра коллекции рабочих листов перед ее печатью и для печати коллекции.

Sub Select([Replace]) - используется для выделения листов коллекции.

Вот еще один небольшой пример на применение методов: Public Sub MoveAndOthers() 'Перемещение листов и другие операции. With ThisWorkbook.Worksheets .Select .PrintPreview (True) 'Метод Move к коллекции лучше не применять! '.Move End With End Sub
Как Вы понимаете, большинство методов - Copy, Move, Select и другие - коллекция WorkSheets "унаследовала" от своих потомков. Чаще всего эти методы применяются к отдельным листам, а не ко всей коллекции в целом. Нам придется еще с ними столкнуться, при рассмотрении методов объекта WorkSheet. Прежде, чем перейти к изучению этого объекта, скажу только, что коллекция WorkSheets, также как и все другие коллекции, событий не имеет.

Методы - "незнакомцы"

Рассмотрим теперь методы, которые нам ранее не встречались. В большинстве случаев эти методы отражают специфику Excel. У рабочего листа их не так и много. Вот эти методы:

Calculate - проводит вычисления формул рабочего листа. Обычно свойство EnableCalculation включено и вычисления идут автоматически. Но при выключенном свойстве необходимо применять этот метод для инициирования вычислений.

ClearArrows - удаляет стрелки трассировки. Эти стрелки можно установить для просмотра зависимостей при вычислениях. Стрелки соединяют ячейки А и В, если формула, записанная в В ссылается на А. Для программного задания трассировки используются методы ShowDependents и ShowPrecedents, но, заметьте, это методы объекта Range, а не рабочего листа. Вот как выглядит трассировка зависимостей, связывающих ячейки при вычислении ряда, задающего вычисление функции ex.

увеличить изображение: ,
Рис. 3.7.  Отображение зависимостей ячеек при вычислениях в Excel

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

Методов у объекта Excel.Application меньше, чем свойств, но и их около полусотни. Дадим краткий обзор, опять-таки, объединяя их по возможности в группы:

Метод ActivateMicrosoftApp(Index As xlMSApplication) позволяет активировать приложение Microsoft, заданное соответствующей константой в аргументе метода. Если приложение уже выполняется, то активируется текущий вариант. В противном случае открывается экземпляр приложения, и затем приложение активируется. Константы позволяют задать все основные приложения Office 2000, а также FoxPro, Project и некоторые другие приложения Microsoft.

Группа методов - DeleteCustomList, DeleteChartAutoFormat, AddCustomList, AddChartAutoFormat - позволяет удалять и добавлять пользовательские списки и пользовательские форматы к тем спискам и форматам, которые используются в самом приложении Excel.

Группа из пяти DDE-методов позволяет обеспечить динамический обмен данными между приложениями в соответствии со стандартом DDE. Сохранена для поддержки совместимости с предыдущими версиями Excel.

Методы, запускающие вычисления - Calculate, CalculateFull, приводят к перевычислению рабочих страниц всех рабочих книг. Метод CheckSpelling запускает проверку орфографии во всех рабочих книгах. Метод Evaluate(Name) преобразует имя объекта в сам объект. Эти методы объединяет то, что все они, по существу, являются методами объектов более низкого уровня иерархии - объектов Workbook, WorkSheet, Chat. Объект Application "наследует" эти методы у своих потомков, что позволяет распространять действие метода на все открытые рабочие книги. Следует понимать, что пользоваться вызовом этих методов объектом Application стоит в очень редких случаях. Опять-таки, можно говорить о некоторой излишней перегрузке объекта Application.

Группа Get-методов - GetCustomListContents, GetCustomListNum, позволяет вернуть содержимое пользовательского списка, получить его номер. Методы GetOpenFileName, GetSaveAsFileName позволяют получить имя файла, выбранное пользователем, открывая по ходу дела соответствующее диалоговое окно.

Группа On-методов, позволяющих запустить на выполнение некоторый макрос. Метод OnKey(Key, Procedure) позволяет запустить макрос, заданный вторым параметром метода, при нажатии пользователем комбинации клавиш, заданной первым параметром метода. Метод OnTime(EarliestTime, Procedure As String, [LatestTime], [Schedule]) позволяет запустить макрос, заданный вторым параметром метода, в указанное время. О схожем методе OnTime рассказывалось при описании методов объекта Word.Application. Методы OnRepeat(Text As String, Procedure As String) и OnUndo(Text As String, Procedure As String) позволяют указать макросы и текст, который будет появляться в пунктах "Повторить Выполнение" и "Отменить Выполнение" из меню Правка. Когда пользователь выберет соответствующий пункт меню, то запускается макрос, указанный втор ым параметром метода. Вот простой пример на применение этих методов:

Public Sub RepeatAndUndo() 'Создание пунктов Повторить и Отменить в меню Правка Call Application.OnRepeat("Hello", "Test") Call Application.OnUndo("7 to A1", "Write7")

End Sub

Public Sub Test() MsgBox ("Hi!") End Sub

Public Sub Write7() Range("A1") = 7 End Sub

Процедура RepeatAndUndo создает соответствующие пункты меню Правка, а процедуры Test и Write7 будут вызываться при выборе пользователем этих пунктов меню. Замечу, что реально особой пользы от применения этих методов не вижу, так как при любых действиях пользователя произойдет обновление этих пунктов меню.

Методы Repeat и Undo близки по духу к рассмотренным только что методам. Они позволяют повторить или отменить последнее действие пользователя при его работе вручную.

Еще одним важным методом, позволяющим запускать макрос на выполнение, является метод Run(Macro, Arg1, Arg2, …). Метод Run позволяет выполнить макрос (процедуру или функцию) проекта рабочей книги или функцию из DLL или XLL. Макрос, запускаемый на выполнение, может находиться в той же рабочей книге, что и макрос, вызвавший метод Run, но может принадлежать и другой рабочей книге. В этом случае, естественно, проекты должны быть связаны по ссылке и в проекте, который вызывает макрос другого проекта, должна быть установлена ссылка на вызываемый проект. При вызове макросу могут быть передано произвольное число аргументов, все они передаются по значению, так что, заметьте, нельзя передать макросу сам объект, а только его значение, задаваемое свойством Value. Метод Run в свою очередь возвращает значение, являющееся результатом выполнения макроса. Приведу простой пример, демонстрирующий все особенности вызова метода Run:

Проекту документа я дал имя BookOneProject. В этом проекте объявлена глобальная переменная Option Explicit Public GlobalZ As Variant

В модуль с именем этого проекта я поместил описание процедуры PlusXY и функции Plus1. Они выполняют простые и понятные без комментариев действия. Public Function Plus1(ByVal X As Integer) As Integer Plus1 = X + 1 End Function


Public Sub PlusXY(ByVal X As Integer, Y As Integer) GlobalZ = X + Y End Sub

В этом же модуле находится и процедура testrun, демонстрирующая вызовы метода Run. Public Sub testrun() 'Запуск на выполнение функции и процедуры, 'находящихся в том же проекте Dim z As Integer z = Application.Run("Plus1", 7) Debug.Print "z = ", z z = Application.Run("PlusXY", 5, 7) Debug.Print "GlobalZ = ", GlobalZ, "z = ", z End Sub

Вот результаты ее выполнения: z = 8 GlobalZ = 12 z = 0

В проекте другой рабочей книги Excel с именем я установил ссылку на проект BookOneProject и в один из модулей поместил процедуру testrun1, вызывающую макросы проекта BookOneProject: Public Sub testrun1() 'Запуск на выполнение функции и процедуры, 'находящихся в другом проекте BookOneProject, 'на который установлена ссылка. Dim z As Integer z = Application.Run("BookOneProject.Module1.plus1", 7) MsgBox ("z= " & z) Call Application.Run("BookOneProject.Module1.plusXY", 5, 7) MsgBox ("GlobalZ = " & BookOneProject.GlobalZ) End Sub

И в этом варианте метод Run успешно справляется с вызовом макросов другого проекта. Конечно, в данном примере вместо того, чтобы применять метод Run, можно было бы непосредственно вызвать ту же функцию Plus1. Но, надеюсь, Вы понимаете, что истинная ценность метода Run в том, что имя выполняемого макроса может быть передано ему в качестве параметра, так что в зависимости от ситуации он может запускать разные макросы. Но давайте закончим с примером и вернемся к рассмотрению других методов объекта Excel.Application.

Метод Goto([Reference], [Scroll]) не выполняя макроса, позволяет перейти к его рассмотрению. Другое, может быть, основное назначение метода состоит в том, чтобы перейти в заданную точку рабочей книги Excel. Чтобы перейти к рассмотрению макроса, параметр Reference должен быть строкой, задающей имя макроса. Для перехода в заданную область документа параметр Reference задается объектом Range. Булев параметр Scroll, имеющий значение true, обеспечивает прокрутку области так, чтобы заданная точка находилась в левом верхнем углу области просмотра. Главное, на что стоит обратить внимание, - метод Goto позволяет осуществлять переходы между документами. Вот пример макросов из документа , осуществляющих соответственно переходы к заданной области и макросу документа . Public Sub GotoRange() 'Переход к заданной области другого документа Application.Goto Workbooks("BookOne.xls").Worksheets("Лист1").Range("A20"), True End Sub


Public Sub GotoMacro() 'Переход к заданному макросу в другом проекте Application.Goto "BookOneProject.Module1.testrun" End Sub

Метод MacroOptions ([Macro], [Description], [HasMenu], [MenuText], [HasShortcutKey], [ShortcutKey], [Category], [StatusBar], [HelpContextID], [HelpFile]) - это еще один метод, связанный с макросами. Он позволяет задать для макроса, указанного первым параметром, различные характеристики - описание, горячие клавиши, раздел справки, связанный с данным макросом, и другие свойства.

Метод RecordMacro([BasicCode], [XlmCode]) - также предназначен для работы с макросами. Он позволяет добавить некоторый программный код в макрос, создаваемый инструментом MacroRecorder. В момент вызова метода MacroRecorder должен быть включен и записывать макрос в модуль, не являющийся активным, другими словами, нельзя произвести запись в тот модуль, макрос которого вызвал метод RecordMacro.

Метод Wait(Time) As Boolean - это последний из описываемых мной методов объекта Excel.Application, входящих в большую группу методов, предназначенных для работы с макросами. Он позволяет организовать задержку вычислений на заданное время, указанное параметром метода. В приведенном ниже примере метод используется, чтобы открыть и показать пользователю некоторую форму, а затем закрыть ее по истечении заданного времени. Этот прием можно использовать в играх, целью которых является проверка внимательности. Вот текст соответствующего макроса: Public Sub WaitSomeTime() 'Открывает форму на ограниченное время MsgBox ("Форма будет показана на 10 секунд!") FlyForm.Show Application.Wait (Now + TimeValue("0:00:10")) FlyForm.Hide End Sub

Взгляните, как выглядит сама форма.


Рис. 3.1.  Форма FlyForm, открытая на "мгновение"

Привожу рисунок этой формы только для того, чтобы пояснить, какая цель преследуется в этом примере. Я предполагал, что при открытии формы пользователь должен успеть в предоставленное ему время ввести два числа в поля X и Y , нажать кнопку, производящую вычисления и запомнить полученный результат. Однако мои намерения не осуществились, и вот по каким причинам. Если форма имеет статус модальной формы, то выполнение макроса приостанавливается до той поры, пока пользователь не закроет форму. Так что в этом случае у пользователя время на работу с формой не ограничено. Это я понимал. Если же форма имеет статус немодальной формы (свойство ShowModal = False), то форма действительно будет открыта в течение 10 секунд. Но в этом случае пользователь не сможет работать с этой формой, вводить значения в поля ввода и нажимать командную кнопку. Хуже всего то, что при попытке ввода значений в поля формы они фактически будут попадать в произвольное место программного текста и порти ть сам проект. Так что следует быть осторожным в подобной ситуации.

Метод Help([HelpFile], [HelpContextID]) позволяет вызвать справочное руководство, указав при необходимости и соответствующий раздел в этом руководстве. Можно вызывать как стандартную справочную систему, - в этом случае не нужно задавать аргументы при вызове метода, либо, что чаще бывает, собственную справочную систему. Первый параметр метода задает имя файла, хранящего справочное руководство. Этот файл может иметь уточнение "chm" , если руководство подготовлено с помощью инструментария HTML Help Workshop, или иметь уточнение "htm", если справочная система создана с помощью инструментария Microsoft WinHelp.

Методы Intersect(Arg1 As Range, Arg2 As Range, …)As Range и Union(Arg1 As Range, Arg2 As Range, …)As Range возвращают в качестве результата объект Range, задающий прямоугольную область, представляющую соответственно пересечение или объединение областей аргументов, которых должно быть не менее двух и не более 30.

Метод InputBox, по существу, эквивалентен одноименной функции из библиотеки VBA и позволяет организовать диалог с пользователем и принять введенное им значение. Функция InputBox является одной из наиболее широко применяемых функций, и примеров ее вызова приводилось достаточно много. Не обойтись без нее и в примерах этой книги. Что вызывать метод InputBox объекта Application или функцию InputBox библиотеки VBA - дело вкуса.

Метод Volatile([Volatile]) позволяет включить или выключить принудительное вычисление для функций, вызываемых в формулах рабочего листа. Метод вызывается непосредственно в функции, которую предполагается пометить. Булев параметр Volatile помечает функцию, как принудительно вычисляемую, если он имеет значение true. Это значение является значением параметра по умолчанию.

Я рассмотрел большую часть методов объекта Application. Замечу, что в предыдущей версии этих методов было значительно больше, поскольку многие функции Excel - математические и прочие были доступны на этом уровне. Теперь, как и положено, все они находятся в специальном контейнере WorkSheetFunction.

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

Мы не будем рассматривать методы, которые так или иначе уже встречались. Рассмотрим только основные методы, определяющие новое поведение объекта Chart:

Sub ChartWizard([Source], [Gallery], [Format], [PlotBy], [CategoryLabels], [SeriesLabels], [HasLegend], [Title], [CategoryTitle], [ValueTitle], [ExtraTitle]) Этот метод позволяет построить или модифицировать существующую диаграмму. В отличие от мастера диаграмм (ChartWizard), который вызывается при построении диаграммы вручную, метод не является интерактивным, более того он не позволяет задать все возможные свойства. С его помощью можно выполнить основную работу, а детали строятся с использованием других свойств и методов объекта Chart. В одном из ранее приведенных примеров (процедуре AddChart) я демонстрировал применение этого метода для программного построения диаграммы. Дадим краткое описание параметров метода, все из которых являются необязательными:

Source - объект Range, содержащий исходные данные для построения новой диаграммы. Если параметр опущен, то метод позволяет отредактировать существующую диаграмму - либо выделенную диаграмму рабочего листа, либо диаграмму активного листа диаграмм.

Gallery - задает тип диаграммы и может быть одной из следующих констант: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, или xlDefaultAutoFormat.

Format - задает формат для данного типа диаграммы. Каждому типу диаграммы соответствует некоторое число возможных форматов. Параметр задает номер формата, по умолчанию выбирается первый формат данного типа.

PlotBy - соответствует терминальному свойству PlotBy.

CategoryLabels и SeriesLabels - целые, указывающие число строк или столбцов с метками категорий и рядов данных в области, заданной параметром Source. Указывать эти числа нужно на единицу меньше фактического значения.

Остальные параметры позволяют добавить легенду, задать название диаграммы и ее осей - они совпадают с соответствующими терминальными свойствами.

Sub SetSourceData(Source As Range, [PlotBy]) Устанавливает источник данных диаграммы. Второй параметр соответствует терминальному свойству PlotBy.

Sub ApplyCustomType(ChartType As XlChartType, [TypeName]) Метод позволяет модифицировать диаграмму, применив к ней новый тип - стандартный или настраиваемый. Если этот тип стандартный, то тогда первый параметр полностью его определяет. Его возможные значения совпадают со значениями соответствующего терминального свойства ChartType. Если же тип настраиваемый, то первый параметр должен иметь одно из следующих значений: xlBuiltIn, xlUserDefined или xlAnyGallery. В этом случае второй параметр задает имя типа диаграммы.

Function Export(Filename As String, [FilterName], [Interactive]) As Boolean Позволяет экспортировать диаграмму, преобразуя ее в графический формат. Первый параметр задает имя файла, в который будет записана диаграмма в графическом формате, второй - задает имя графического фильтра в том виде, как оно записано в системном регистре. Булев параметр Interactive должен иметь значение True, если мы хотим вызвать диалоговое окно в процессе фильтрации. Функция Export возвращает значение True в случае успешного завершения работы.

Sub GetChartElement(X As Long, Y As Long, ElementID As Long, Arg1 As Long, Arg2 As Long). Представьте себе, что пользователь щелкнул кнопку мыши где-то над диаграммой. Обработав это событие, можно получить координаты курсора мыши - X и Y. Если теперь вызвать метод GetChartElement с этими координатами, то он вернет значение параметра ElementID - идентификатор элемента диаграммы и значения двух параметров, связанных с этим элементом. Конечно, параметры зависят от типа того элемента, чьи координаты X и Y заданы.

Function Location(Where As XlChartLocation, [Name]) As Chart. Передвигает диаграмму в новое местоположение. Параметр Where имеет следующие значения: xlLocationAsNewSheet, xlLocationAsObject, или xlLocationAutomatic.

В первом случае диаграмма помещается на новый лист диаграммы и параметр Name задает имя этого листа. Во втором случае диаграмма помещается как встроенный объект и Name задает имя рабочего листа. Вот пример, в котором диаграмму, построенную на рабочем листе книги "" мы переносим на отдельный лист диаграмм:Public Sub MoveChart() Workbooks("BookOne").Worksheets("Sheet1").ChartObjects(4) _ .Chart.Location Where:=xlLocationAsNewSheet, Name:="Динамика продаж" End Sub

Sub SetSourceData(Source As Range, [PlotBy]) Позволяет установить новый источник данных. Параметры в пояснениях не нуждаются.

В заключение приведем процедуру, создающую трехмерную диаграмму по данным нашего примера с дилерами и продажами: Public Sub Chart3D() Workbooks("BookFour").Activate With Worksheets("Лист3") Dim myRange As Range Set myRange = .Range("C23:F27") Dim myChart As ChartObject 'Создаем контейнер объекта Chart Set myChart = .ChartObjects.Add(myRange.Left - 100, _ myRange.Top + myRange.Height, 400, 300) End With 'Определяем параметры объекта Chart With myChart.Chart .ChartType = xl3DColumn .SetSourceData Source:=myRange, PlotBy:=xlRows .Location Where:=xlLocationAsObject, Name:="Лист3" End With With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Динамика продаж" End With 'Диалог с пользователем If MsgBox("Хотите изменить угол зрения?", vbYesNo) = vbYes Then ActiveChart.RightAngleAxes = True End If End Sub

Дадим некоторые комментарии к этой программе:

В качестве источника данных выступает известная по предыдущим примерам таблица Excel.

Я ввел объекты myRange и myChart класса ChartObjects. Это позволило задать нужные размеры и положение диаграммы, привязанное к таблице данных.

Ранее при построении диаграмм я использовал метод ChartWizard и работу с коллекциями SEriesCollection. Теперь продемонстрирован еще один способ, когда задаются свойства и методы объекта Chart, в частности, для указания источника данных используется метод SetSourceData.

Свойство ChartType позволяет указать тип диаграммы, а метод Location определяет ее, как встроенный объект.

В диалоге с пользователем изменяется угол зрения, задаваемый булевым свойством RightAngleAxes.

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

Методы объекта 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.

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

Дадим теперь краткую характеристику основным методам объекта Workbook. Мы уже говорили о том, что создаются и открываются рабочие книги методами коллекции Workbooks - Add, Open и OpenTextFile. А вот закрываются и сохраняются, используя собственные методы. С них мы и начнем описание методов:

Save, SaveAs, SaveCopyAs - позволяют сохранить рабочую книгу, без ее закрытия и удаления из коллекции Workbooks. При первом сохранении следует применять метод SaveAs, чтобы задать имя файла, в котором книга сохраняется. Метод имеет и другие параметры - формат хранения, пароль, статус и другие характеристики. Последний из этой группы методов создает копию рабочей книги.

Close - выполняет те же функции, что и Save, но одновременно закрывает книгу и удаляет ее из коллекции.

Activate - активизирует рабочую книгу.

Route - направляет рабочую книгу по сети всем участникам совместной разработки. Список участников и другие характеристики задаются в свойствах объекта RoutingSlip. Вот пример процедуры, в которой определяется круг участников работы, после чего им пересылается текущая книга.

Public Sub BookRoute() 'Регистрация исполнителей совместной разработки 'в объекте RoutingSlip. 'Посылка книги совместно работающим исполнителям. With ThisWorkbook .HasRoutingSlip = True With .RoutingSlip .Delivery = xlOneAfterAnother .Recipients = Array("Илья Биллиг", _ "Михаил Дехтярь") .Subject = "Collaboration Test" .Message = "Это книга Excel, пересылаемая в качестве примера" .ReturnWhenDone = True End With .Route End With
End Sub
Заметьте, для того, чтобы при пересылке книги не задавались лишние вопросы, все исполнители, включенные в список Recipients, также как и сам автор документа, должны быть включены в адресную книгу. Согласно установленному порядку книга будет послана первому исполнителю, указанному в списке, и далее будет пересылаться по заданному списком маршруту. После отсылки книги свойство Routed автоматически будет установлено как True.

AcceptAllChanges, RejectAllChanges - принимает или отвергает все изменения, сделанные участниками совместной разработки документа при разделенном доступе.

RefreshAll - обновляет сводные таблицы и все области, содержащие внешние данные.

PurgeChangeHistoryNow (Days,SharingPassword) - удаляет из истории изменений все те, чей срок хранения превосходит число дней, заданных параметром Days. Второй параметр задает общий пароль.

Protect, ProtectSharing, Unprotect, UnprotectSharing - методы, включающие и выключающие пароли личные и общие рабочей книги.

ExclusiveAccess - если книга открыта с разделяемым доступом, то этот метод дает пользователю, вызвавшему его, исключительное право доступа - изменения, сделанные всеми остальными пользователями должны сохраняться в отдельных файлах.

ChangeFileAccess(Mode, WritePassword, Notify) - изменяет статус доступа. Новый статус задается параметром Mode, который может принимать одно из двух значений: xlReadWrite и xlReadOnly. Если файл снабжен паролем и получает статус для записи и чтения, то второй параметр WritePassword задает пароль на запись. Если булев параметр Notify имеет значение True, то пользователь получает уведомление, когда файл недоступен.

AddToFavorites - добавляет в папку Favorites ярлычок рабочей книги.

PivotCaches - возвращает коллекцию областей памяти, отводимых сводным таблицам данной рабочей книги. Элементами этой коллекции являются объекты PivotCache. Каждой сводной таблице - объекту PivotTable отводится своя память (кэш), которую и задает объект PivotCache.

RunAutoMacros - запускает на выполнение все автомакросы данной книги.

LinkSources([Type]), ChangeLink(Name As String, NewName As String, [Type As XlLinkType = xlLinkTypeExcelLinks]), OpenLinks(Name As String, [ReadOnly], [Type]), LinkInfo(Name As String, LinkInfo As XlLinkInfo, [Type], [EditionRef]), UpdateLink([Name], [Type]) - группа методов, позволяющих работать со ссылками. Ссылки могут быть четырех типов:

на другие рабочие книги Excel ,

на документы, связанные по протоколу OLE (например, документы Word, на которые ссылается рабочая книга) или протоколу DDE,

на издателей книги при совместной работе над книгой и ее публикации на сервере,

на подписчиков книги, опубликованной на сервере и доступной для подписчиков.

Метод LinkSources позволяет получить все ссылки типа, заданного его параметром. Если параметр не указан, то будут выданы ссылки на книги Excel. Следующий метод Change позволяет изменить ссылку, Open - открыть документ по заданной ссылке, Info - получить некоторую информацию о документе, Update - обновить ссылки.

Вот пример работы с этой группой методов объекта Workbook: Public Sub LinkDocs() 'Установление связей рабочей книги Dim BookLinks As Variant Dim i As Integer With ThisWorkbook ' связи с другими книгами Excel. BookLinks = .LinkSources(xlExcelLinks) If Not IsEmpty(BookLinks) Then Debug.Print "Существуют ссылки на рабочие книги Excel!" For i = LBound(BookLinks) To UBound(BookLinks) Debug.Print "Ссылка" & i & " : ", BookLinks(i) If BookLinks(i) = "BookTwo" Then .OpenLinks BookLinks(i) .ChangeLink BookLinks(i), "BookOne" End If Next i Else: Debug.Print "Ссылки на рабочие книги отсутствуют!" End If

BookLinks = .LinkSources(xlOLELinks) If Not IsEmpty(BookLinks) Then Debug.Print "Существуют ссылки на OLE - документы!" For i = LBound(BookLinks) To UBound(BookLinks) Debug.Print "Ссылка" & i & " : ", BookLinks(i) Next i If .LinkInfo("Word.Document.8|E:\O2000\Remarks.doc!'", _ xlUpdateState, xlOLELinks) = 1 Then Debug.Print "Автоматическое обновление данных с OLE - документами!" End If Else: Debug.Print "Ссылки на OLE - документы отсутствуют!" End If

BookLinks = .LinkSources(xlPublishers) If Not IsEmpty(BookLinks) Then Debug.Print "Существуют ссылки на издателей документа!" For i = LBound(BookLinks) To UBound(BookLinks) Debug.Print "Ссылка" & i & " : ", BookLinks(i) Next i Else: Debug.Print "Ссылки на издателей отсутствуют!" End If

BookLinks = .LinkSources(xlSubscribers) If Not IsEmpty(BookLinks) Then Debug.Print "Существуют ссылки на подписчиков документа!" For i = LBound(BookLinks) To UBound(BookLinks) Debug.Print "Ссылка" & i & " : ", BookLinks(i) Next i Else: Debug.Print "Ссылки на подписчиков отсутствуют!" End If End With End Sub

Вот как выглядят результаты отладочной печати, полученные при работе этой процедуры: Существуют ссылки на рабочие книги Excel! Ссылка1 : BookTwo Ссылка2 : E:\O2000\DsCd\Ch11\BookOne.xls Существуют ссылки на OLE - документы! Ссылка1 : Word.Document.8|E:\O2000\Remarks.doc!' Автоматическое обновление данных с OLE - документами! Ссылки на издателей отсутствуют! Ссылки на подписчиков отсутствуют! Новые методы объекта Workbook


У объекта Workbook в Excel 2000 появились два новых метода:

Sub ReloadAs(Encoding As MsoEncoding),

Sub WebPagePreview().

Оба метода, так или иначе, связаны с общей тенденцией публикации документов Excel в Интернет. Рабочие книги, публикуемые в Интернет и интранет, хранятся, естественно в формате HTML. При их чтении могут возникнуть проблемы с кодировкой. Метод ReloadAs(Encoding As MsoEncoding) позволяет перезагрузить книгу в формате HTML, используя нужную кодировку, заданную параметром метода, значением которого может быть, например, константа msoEncodingCyrillic. Метод WebPagePreview позволяет перед публикацией книги отобразить ее на дисплее в том виде, как будет выглядеть соответствующая Web-страница, открываемая в интернет для работы с рабочей книгой.

Методы объекта Workbook предназначены, как можно видеть, для выполнения общих операций над документом и по существу не определяют специфических для Excel действий. Чтобы познакомиться со спецификой, следует пойти вглубь иерархии объектов.

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

У объекта WorkSheet методов достаточно много. Часть из этих методов применима ко многим объектам и уже встречалась или еще будет встречаться при описании других объектов. Так что введенные в [3] понятия общности и схожести применимы не только к самим объектам, но и их отдельным свойствам и методам. Поэтому я разделю описание методов на две группы, и начну с более простой группы схожих методов.

Методы - свойства

Теперь я хочу рассмотреть еще несколько важных методов объекта WorkSheet, которые я выделил в отдельную группу. Эти методы похожи на свойства. В результате их работы возвращаются объекты. По-видимому, правильно считать, что возвращаемые объекты непосредственно вложены в объект WorkSheet и определяют его структуру также как объекты, возвращаемые свойствами-участниками. Вот почему я называю эти методы свойствами.
В эту группу методов входят:

Function ChartObjects([Index]) As Object- возвращает коллекцию ChartObjects. Если задан параметр Index, то возвращается элемент этой коллекции - объект ChartObject. Возможный параметр Index задает номер или имя возвращаемого объекта. Заметьте, элементами коллекции являются объекты ChartObject, а не объекты Chart. Объект ChartObject является контейнером объекта Chart. Его методы и свойства позволяют управлять внешним видом и размерами встроенной в контейнер диаграммы. Чтобы получить сам объект Chart, следует воспользоваться свойством Chart объекта ChartObject. Не следует путать метод ChartObjects со свойством Charts объекта WorkBook, которое возвращает коллекцию Charts, представляющую страницы с диаграммами рабочей книги. Я напомню, что в Excel диаграммы могут быть встроены в обычный рабочий лист и, следовательно, с объектной точки зрения быть встроенными в объект WorkSheet. С другой стороны, диаграммы могут располагаться на отдельных листах рабочей книги. Такие специальные листы для отображения диаграмм и составляют коллекцию Charts. Элементы этой коллекции - объекты Chart - представляют либо встроенные диаграммы, либо листы с диаграммами. Согласно справочной системе Excel объект Chart, задающий лист с диаграммой, также имеет метод ChartObjects, возвращающий коллекцию контейнеров. Однако, практически работать с этой коллекцией не удается, да и в этом нет никакой необходимости, поскольку сам объект Chart задает и диаграмму, расположенную на листе. Наличие объектов Chart и ChartObject, их коллекций, большого числа различных свойств и методов, возвращающих эти объекты, создает впечатление излишней сложности. Приведу сейчас два примера, демонстрирующих работу с этими объектами:

Public Sub WorkWithCharts() 'Работа с встроенными диаграммами Dim CHO As ChartObjects 'коллекция контейнеров Dim ChO1 As ChartObject 'контейнер диаграммы Dim Ch1 As Chart 'встроенная диаграмма With ThisWorkbook Set CHO = .Sheets("Лист2").ChartObjects Set ChO1 = CHO(2) 'Меняем внешний вид диаграммы ChO1.RoundedCorners = True ChO1.Select Debug.Print ChO1.Name 'Получаем диаграмму Set Ch1 = ChO1.Chart Ch1.HasTitle = True Ch1.ChartTitle.Text = "Заказы Февраля" Debug.Print Ch1.Name 'Работа с листами диаграмм Dim Ch2 As Chart, Ch3 As Chart Dim ChO2 As Object Set Ch2 = .Charts(1) 'Лист диаграммы Ch2.HasTitle = True Ch2.ChartTitle.Text = "Заказы Марта" 'Контейнер для листа диаграммы Set ChO2 = .Charts(2).ChartObjects 'Работать с этим контейнером практически невозможно! 'Но особой необходимости в этом нет. 'Set Ch3 = ChO2.Chart 'Ch3.ChartTitle = "Заказы Апреля" End With

End Sub

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

Следующий пример носит более содержательный характер. В нем вначале программно создается уже не раз упоминавшаяся последовательность чисел Фибоначчи, а затем программно строится диаграмма (график), отражающая рост этих чисел с изменением их порядкового номера. Диаграмма строится в три приема - создается контейнер, затем объект Chart , затем вызывается метод ChartWizard, который и осуществляет построение диаграммы. Вот текст соответствующей процедуры: Public Sub AddChart() 'Формируется последовательность чисел Фибоначчи. 'Вставляется диаграмма, отражающая график роста этих чисел. Dim myRange As Range Dim MySh As Worksheet Dim CHOS As ChartObjects Dim CHO As ChartObject Set MySh = ThisWorkbook.Worksheets(3) With MySh Set myRange = .Range("A1") With myRange .Value ="Числа Фибоначчи" .Offset(1, 0).FormulaR1C1 = "0" .Offset(2, 0).FormulaR1C1 = "1" .Offset(3, 0).FormulaR1C1 = "=R[-2]C +R[-1]C" .Offset(3, 0).Select Selection.AutoFill Destination:=Range("A4:A10"), _ Type:=xlFillDefault End With 'Добавление диаграммы Set CHOS = .ChartObjects Set CHO = CHOS.Add(50, 50, 250, 200) CHO.Chart.ChartWizard Source:=.Range("A2:A10"), _ Gallery:=xlLine, Title:="Числа Фибоначчи" End With


End Sub

В результате работы этой процедуры соответствующий рабочий лист Excel имеет вид:


Рис. 3.10.  Программно построенная диаграмма

Function PivotTables([Index]) As Object возвращает коллекцию PivotTables. Если задан параметр Index, то возвращается элемент этой коллекции - объект PivotTable. Возможный параметр Index задает номер или имя возвращаемого объекта. Объект класса PivotTable определяет сводную таблицу. Эти таблицы играют важную роль при представлении итоговых данных и формировании отчетов. Но о них есть смысл говорить после знакомства с базами данных. Отметив наличие такого объекта, отложив его обсуждение до той поры, пока не встретимся с ним в нужном месте и в нужное время.

Function Scenarios([Index]) As Object возвращает коллекцию Scenarios. Если задан параметр Index, то возвращается элемент этой коллекции - объект Scenario. Возможный параметр Index задает номер или имя возвращаемого объекта. Элемент класса Scenario представляет сценарий, используемый при анализе данных электронной таблицы. Я рассмотрю подробно применение сценариев на примерах в последующих главах книги.

Function OLEObjects([Index]) As Object возвращает коллекцию OLEObjects. Если задан параметр Index, то возвращается элемент этой коллекции - объект OLEObject. Возможный параметр Index задает номер или имя возвращаемого объекта. Элемент класса OLEObject представляет OLE-объект, встроенный в рабочий лист.

Объект Chart

Трудно перечислить все достоинства Excel. Конечно, на первом месте стоит возможность работы с данными, предоставляемая электронной таблицей Excel, его машиной вычислений и мощной библиотекой встроенных функций. Но на второе место по важности, несомненно, претендуют возможности Excel по графическому отображению данных. Для этого используются диаграммы Excel, позволяющие отображать одни и те же данные в самых различных форматах в зависимости от потребностей пользователя. Excel предоставляет самые широкие возможности для варьирования формой представления данных. Диаграммы могут быть плоскими и объемными, двумерными и трехмерными, круговые и цилиндрические, данные можно отображать в виде графиков или гистограмм различного типа. На одной диаграмме может отображаться несколько групп, где каждая группа содержит один или несколько рядов данных (серий), отображаемых в одном формате. Диаграммы выделяются цветом, имеют оси, сопровождаются заголовком, подписями, легендой. Легенда - это надпись на предмете, например, на монете. На диаграммах, содержащих несколько рядов данных, легенда задает название каждого ряда.
Видов диаграмм в Excel великое множество. По типу диаграммы делятся на стандартные и нестандартные или настраиваемые (Custom). Стандартных типов - 14, но каждый из них имеет до десяти форматов, так что в общей сложности их около 100. Настраиваемые типы в свою очередь разделяются на встроенные и определенные пользователем. С их помощью на одной диаграмме можно задать комбинацию нескольких стандартных типов. Мы в этой книге не собираемся останавливаться на подробном разборе всех типов, поскольку для программистов, в основном, это понятные вещи. Но несколько слов все-таки сказать следует. По сути, диаграммы предназначены для отображения графиков функций и гистограмм. Если по точкам строится график функции Y=F(X), то, как известно, необходимо задать два множества - аргументов и значений. Например, функция, определяющая объем продаж, осуществленных ее дилерами в первом квартале, имеет аргументами фамилии дилеров (Иванов, Петров, Сидоров), а значениями - объем продаж, выраженный в рублях (100, 200, 150).Множество значений называется в Excel рядом данных, а аргументы называются категориями. Соответственно, ось X (аргументов) называется осью категорий, а ось Y - осью значений. При построении графика можно опустить задание аргументов и тогда по умолчанию за их значения принимается начало натурального ряда чисел - 1, 2, 3 и т.д. Очень часто на одной диаграмме отображается несколько графиков. Если эти графики отражают некоторую тенденцию, например динамику объема продаж во времени, то лучше использовать трехмерную диаграмму, в которой появляется третья ось, чаще всего это ось времени. В Excel она называется осью рядов данных, так как фиксирует изменения ряда данных.
Понятие гистограммы пришло из теории вероятности. Можно считать, что гистограмма отличается от графика функции тем, что аргументами при построении гистограммы являются интервалы, и значение функции связывается с интервалом. Гистограмма, обычно, отображается в виде прямоугольников, каждый из которых имеет в основании заданный интервал, а высотой - значение функции. В примере с дилерами и продажами, естественно отобразить данные в виде гистограммы, поскольку объемы продаж в реальности связаны с интервалами времени (1-й квартал, 2-й, 3-й, 4-й).
Поскольку речь идет о графических объектах, то лучше на них взглянуть. На рис. 3.11 - 3.14 показаны различные диаграммы, отражающие данные с дилерами и продажами нашего простенького примера. На первом из этих рисунков показана классическая гистограмма, чаще всего применяемая в подобных ситуациях. На втором рисунке те же данные представлены в виде графиков. Конечно, чаще всего графики используются в инженерных расчетах и математических вычислениях. В экономических задачах предпочтительнее более наглядные формы отображения данных, например, круговые диаграммы, одна из которых показана на третьем рисунке. Наконец, на четвертом рисунке этой серии показана трехмерная диаграмма. Третье измерение - это, чаще всего, ось времени, что позволяет наглядно отобразить динамику объема продаж. Представленные рисунки отражают лишь малую долю тех возможностей, которые предоставляют диаграммы Excel

Рис. 3.11.  Гистограмма, отражающая динамику объема продаж

Рис. 3.12.  Представление данных о продажах в виде графиков

Рис. 3.13.  Круговая диаграмма, отражающая вклад каждого дилера


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

Объект Excel Application

Объект Excel.Application задает приложение Excel. А посему свойства, методы и события этого объекта должны характеризовать приложение в целом. Понятно, что у этого объекта должно быть свойство Workbooks, возвращающее все открытые в приложении рабочие книги, свойство Windows, возвращающее открытые окна, свойства, такие как CommandBars, возвращающие объекты интерфейса, и другие подобные свойства. Методов и событий, характерных для всего приложения в целом, по-видимому, не так уж и много. Так что, казалось бы, структура этого объекта должна быть достаточно простой. Однако реально это не так, - у объекта Excel.Application очень большое число свойств, методов и событий, что не позволяет мне описать их полностью, да и нет в этом особого смысла. Объект Excel.Application, на мой взгляд, явно перегружен, многие его свойства и методы без всякого ущерба можно было бы исключить, поскольку они оперируют с объектами, стоящими на более низких уровнях иерархии и не имеют прямого отношения ко всему приложению в целом. Приведу лишь один пример. Первое по алфавиту свойство ActiveCell возвращает объект, задающий активную ячейку. Понятно, что речь идет об активной ячейке активной страницы активной рабочей книги. Непонятно только, зачем нужно было добавлять это свойство самому приложению. Вполне достаточно, чтобы им обладал объект WorkSheet, задающий страницу книги. Более того, если в момент вызова свойства ActiveCell нет активной страницы с ячейками, то возникнет ошибка, чего не происходит, если активную ячейку вызывает объект WorkSheet. Примеров подобной перегруженности объекта Application можно привести много. Я в своем описании объектов верхнего уровня не всегда буду упоминать такие свойства, полагая, что лучше рассказать о них там, где они необходимы по существу.

Объект Workbook

Рабочая книга Excel устроена проще, чем документ Word. Как и положено книге, она состоит из страниц (листов). В терминах объектов это означает, что объект Workbook имеет свойство Sheets, возвращающее объект Sheets - коллекцию листов рабочей книги. Поскольку рабочие книги Excel содержат листы разного типа, то наряду с коллекцией Sheets у объекта Workbook имеются свойства, возвращающие коллекции листов разного типа:

Worksheets - рабочих листов;

Charts - листы, содержащие диаграммы;

Excel4MacroSheets - листы с макросами одной из предыдущих версий Excel. Реально листы такого типа уже не встречаются. Поэтому и в документации они не всегда упоминаются. Я о них также больше говорить не буду. Здесь отдана дань прошлому, когда макросы располагались на листах рабочей книги. Сейчас все макросы находятся в модулях программного проекта.

Эти коллекции и составляют в совокупности коллекцию Sheets. Чаще всего приходится работать с каждой из этих коллекции в отдельности, но иногда полезно иметь возможность выполнять операции над всеми листами, независимо от их типа. С некоторым сожалением отмечу, что отсутствует класс Sheet, обобщающий свойства классов Workbook и Chart.
Теперь, когда состоялось первое знакомство с основными объектами, составляющими рабочую книгу, давайте перейдем к более систематическому обзору всех свойств- участников объекта Workbook.

Объект WorkSheet

Объект Worksheet - рабочий лист является элементом коллекции Worksheets. Он представляет основной тип страниц рабочей книги. Именно на этих страницах разворачиваются основные действия в ячейках электронной таблицы. Основу рабочего листа составляет прямоугольная таблица ячеек. Главная особенность электронной таблицы состоит в том, что в ее ячейки можно вводить не только данные, но и формулы. Формулы Excel, также как и обычные математические формулы, также как и выражения в языках программирования, оперируют при вычислении значений константами, переменными и функциями. В электронной таблице роль переменных играют ячейки таблицы. Существует некоторый алгоритм, определяющий порядок, согласно которому вычисляются формулы в ячейках электронной таблицы. При изменении данных таблиц, инициированных пользователем, внешними ссылками или выполнением макросов программного проекта, пересчитываются и формулы. Это делает таблицу живой, - изменение значения одной ячейки приводит, возможно, к пересчету всей таблицы.
Также как для документов Word работа с текстом является главным занятием пользователей, работающих с документом, так и работа с ячейками - ввод данных и формул в ячейки, и, тем самым, инициирование вычислений лежит в основе работы с рабочим листом. С объектной точки зрения отдельные ячейки электронной таблицы и области, содержащие совокупности этих ячеек, то, что называется объектами Range, являются основными объектами рабочего листа. Но, естественно, рабочий лист состоит не только из объектов Range, есть и другие компоненты. Объектная модель рабочего листа Worksheet достаточно сложна, что отражает, впрочем, сложность изучаемого объекта. Давайте познакомимся с ней поближе.

Объектная модель Excel

Прежде всего, несколько слов о том, как устроена объектная модель Excel и других приложений Office 2000. В этой модели объекты связаны между собой отношением встраивания. На нулевом уровне иерархии существует некоторый центральный объект, в который встроены другие объекты, составляющие первый уровень иерархии. В каждый из объектов первого и последующих уровней могут быть встроены объекты следующего уровня. Так это процесс продолжается. Таким образом, объекты в этой модели "толстые", поскольку в них встроено большое число других объектов. В особенности это касается объектов, стоящих на верхних уровнях иерархии.
Формально встраивание реализуется с помощью свойств объектов. Свойства могут быть как терминальными, не являющимися объектами, и так называемыми свойствами - участниками, которые возвращают объекты при их вызове.
Давайте перейдем к рассмотрению библиотеки объектов Excel 9.0 и начнем с центрального объекта этой библиотеки - Excel.Application.

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

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

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


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


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


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


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


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


Биллиг Владимир Арнольдович
Интернет-Университет Информационных Технологий, INTUIT.ru
Приведу текст программы, рисующей стрелки: Public Sub DependArrows() 'Проведение стрелок, задающих зависимости ячеек. Dim i As Integer With ThisWorkbook.Worksheets(3) 'Установка области выделения Dim myRange As Range Set myRange = .Range("D32") 'Поочередное вычисление влияющих ячеек For i = 1 To 10 myRange.ShowPrecedents Next i 'Все стрелки можно удалить! '.ClearArrows End With End Sub
Продолжим рассмотрение методов:

Evaluate(Name) преобразует имя в объект или значение. Этот метод часто удобно применять, когда имя вводится пользователем в процессе диалога. Вот пример, а точнее два примера в одной процедуре, в первом - пользователь вводит в процессе диалога имя интересующей его ячейки, а ему возвращается ее значение. Обратите внимание, что здесь имя преобразуется в объект Range, задающий ячейку с заданным именем, и после преобразования можно использовать всю мощь этого объекта. Второй пример не менее интересен, - пользователь задает некоторое выражение, содержащее обращение к стандартным функциям и получает значение этого выражения, посчитанное при вызове метода Evaluate. Так что, по существу метод представляет реализацию интерпретатора выражений.

Public Sub Eval() 'Организация вычислений по запросу пользователя. Dim NameOfCell As String, Mes As String Dim Val As Variant 'Запрос ячейки. Mes = "Введите имя ячейки,значение которой Вас интересует" NameOfCell = InputBox(Prompt:=Mes, _ Title:="Ввод имени", Default:="A1") Val = Evaluate(NameOfCell).Value MsgBox ("Значение ячейки " & NameOfCell & " = " & Val)
'Запрос на вычисление функции. Mes = "Задайте функцию и аргумент - получите значение" NameOfCell = InputBox(Prompt:=Mes, _ Title:="Ввод функции", Default:="SIN(3)") Val = Evaluate(NameOfCell) MsgBox ("Значение функции " & NameOfCell & " = " & Val) End Sub


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


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


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


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


На рисунках показаны окна, которые открывались в процессе диалога с пользователем при вычислении значения выражения:


Рис. 3.8.  Ввод выражения, заданного строкой


Рис. 3.9.  Вычисление выражения интерпретатором формул

PivotTableWizard - создает сводную таблицу. Работу со сводными таблицами я рассмотрю в последующих разделах этой книги.

ResetAllPageBreaks - восстанавливает исходную разбивку рабочего листа на страницы, которая возможно была изменена.

SetBackgroundPicture(Filename) - устанавливает графический фон для рабочего листа или листа диаграмм. Картинка для фона берется из файла, имя которого задает параметр FileName.

ShowDataForm - показывает форму данных, связанную с данным рабочим листом. Несколько слов о том, что собой представляет форма данных. Начать нужно, по-видимому, с определения понятия список данных. Excel позволяет связывать с рабочим листом один список данных, представляющий небольшую реляционную базу данных - таблицу, состоящую из именованных столбцов. Форма данных - инструментальное средство для работы с этой таблицей. Форма позволяет добавлять и изменять записи списка. Форма строится автоматически по заголовкам списка и число полей формы совпадает с числом столбцов. Над списком определены разные операции, в частности, возможна фильтрация данных. Но пока при рассмотрении свойств и методов оставим в стороне все, что связано с работой над списком. Об этом предстоит отдельный и подробный разговор.

Объекты 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

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


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

Общие объекты и Excel.Application

Давайте начнем рассмотрение со свойств объекта Excel.Application , возвращающих уже знакомые нам общие объекты: Таблица 3.1. Общие объекты, доступные в Excel.ApplicationСвойство, возвращающее объектНазначение объектаБиблиотека

Assistant	Помощник, позволяющий организовать собственную диалоговую систему.	Office
Answer Wizard	Мастер Ответов, стоящий за спиной Помощника. Может использоваться при создании собственной справочной системы.	Office
Com AddIns	Коллекция компонент, общих для приложений Office 2000.	Office
CommandBars	Коллекция инструментальных панелей, без работы с которой не обойтись при создании собственного интерфейса документа Excel.	Office
FileSearch	Объект, используемый при поиске файлов.	Office
Language Settings	Объект, задающий языковые предпочтения, общие для приложений Office 2000.	Office
Debug	Объект, используемый при отладке программных проектов.	VBA
VBE	Корневой объект при работе с программными проектами.	VBA

Все объекты, приведенные в этой таблице, играют важную роль при программной работе с документами Excel, как, впрочем, и с другими документами Office 2000.

Оглавление

Программист, работающий в Excel, должен свободно ориентироваться в мире его объектов. Мощь офисного программирования определяется тем, что изначально в распоряжении программиста находится большое число уже готовых объектов. Чтобы с толком распорядиться предоставляемыми возможностями, объекты нужно знать.
Разговор об объектах Excel целесообразно начать с рассмотрения каркаса документа Excel. Многочисленные библиотеки объектов Office 2000, совокупность которых для программиста и представляют Office 2000, задают каркас всех документов, которые можно построить в этой среде. Когда создается новый документ, например, рабочая книга Excel, то по умолчанию из всей совокупности библиотек выбирается несколько, объекты которых и составляют каркас документа. Эти объекты доступны программисту, без каких либо дополнительных усилий. Центральную роль в каркасе документов Excel играют, конечно же, объекты библиотеки Excel. Но знание и всех других объектов, входящих в каркас, необходимо. Например, при программном создании интерфейса необходимо знание общих объектов библиотеки Office. Отмечу еще, что при желании программист всегда может расширить каркас документа, добавив в него те или иные библиотеки. Каркас, создаваемый по умолчанию в тот момент, когда открывается новая рабочая книга, состоит из объектов, входящих в состав следующих библиотек:

Excel - библиотека, задающая основу документов Excel. Здесь хранится класс, задающий корневой объект Excel.Application, и все классы объектов, вложенных в корневой объект.

Office - библиотека объектов, общих для всех приложений Office 2000. Здесь находятся классы, определяющие инструментальные панели - CommandBar и классы других общих объектов. Здесь же находятся классы, задающие Помощника (объект Assistant и все классы, связанные с ним). В частности, появился новый объект, которого не было в предыдущей версии - Мастер Ответов (Answer Wizard).

Stdole - библиотека классов, позволяющая работать с OLE - объектами и реализовать Автоматизацию.

VBA - библиотека классов, связанных с языком VBA. Здесь хранятся все стандартные функции и константы, встроенные в язык, классы Collection и ErrObject.

VBAProject - проект по умолчанию, связанный с документом. Классы, которые могут программистом создаваться в этом проекте, методы, свойства, - все это доступно для просмотра, так же, как и объекты классов, встроенных в стандартные библиотеки.

Если сравнить каркас рабочей книги Excel, например, с каркасом документа Word, то они отличаются тем, что в основе одного лежит библиотека Excel, в основе другого - библиотека Word. Эти библиотеки содержат специфические для данных приложений объекты. Что же касается интерфейсных объектов, объектов определяющих среду редактора VBA, автоматизацию, то здесь используются общие объекты. Библиотеки Office, Stdole, VBA - это общие для всех приложений Office 2000 библиотеки.
Замечу, что хотя каркас документа Excel не изменился в Office 2000 в сравнении с предыдущей версией, вместе с тем в объектной модели произошли довольно существенные изменения, появились новые объекты, новые свойства и методы у ранее существовавших объектов.

Построение обработчиков событий

Обработчики событий для объектов Workbook, Worksheet и объектов Chart, задающих листы диаграмм, построить нетрудно. Все эти события по умолчанию включены, поэтому для построения обработчика достаточно перейти в окно проектов, выбрать модуль, обрабатывающий события этого объекта, в окне объектов этого модуля выбрать нужный объект, а затем в окне событий и процедур выбрать из списка имя события. В результате этих действий появится заготовка, содержащая заголовок обработчика события, после чего останется написать код обработчика. Более сложно строятся обработчики событий для объектов Chart, задающих встроенные диаграммы. О том как создаются обработчики событий для тех объектов, у которых события по умолчанию выключены, я подробно рассказал при рассмотрении событий объекта Application. Остается только коротко напомнить схему действий:

Вначале нужно создать класс, в котором следует объявить объект Chart с событиями (With Events)

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

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

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

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

Пример обработки события Change

В качестве примера рассмотрим построение обработчиков события Change. Я рассмотрю обработку этого события на двух уровнях - уровне объекта Application, на нижнем уровне - объектом WorkSheet. Рассмотрение этого события представляет практический интерес, поскольку довольно часто в различных задачах приходится следить за изменениями, происходящими с рабочими страницами. С другой стороны, есть несколько важных нюансов, которые следует учитывать в процессе работы с этим событием.
В моем примере обработчик события Change объекта Application будет следить за всеми изменениями, которые пользователь выполняет на рабочих страницах различных документов Excel. Информация об изменениях будет регистрироваться в журнале изменений, заданным специально спроектированной формой. Как выглядит сама форма, увидим чуть позже, а сейчас замечу, что устроена она очень просто и содержит один список из пяти столбцов, в каждом из которых будут храниться данные о документе, в котором произошли изменения, странице, дате, адресе области изменения и новом значении, записанном в эту область. Вот текст обработчика события, возникающего при инициализации формы: Private Sub UserForm_Initialize() 'Задание заголовков столбцов журнала изменений. With Me.ListBox1 .ColumnCount = 5 .AddItem "Книга" .Column(1, .ListIndex + 1) = "Страница" .Column(2, .ListIndex + 1) = "Дата" .Column(3, .ListIndex + 1) = "Адрес" .Column(4, .ListIndex + 1) = "Значение" End With End Sub
Приведу теперь текст обработчика события Change для объекта Application: Private Sub ExApp_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'Запись в журнал всех изменений, проводимых пользователем. Dim RowIndex As Integer MsgBox ("Запись в журнал изменений!") RowIndex = JournalForm.ListBox1.ListCount With JournalForm.ListBox1 .AddItem Sh.Parent.Name .Column(1, RowIndex) = Sh.Name .Column(2, RowIndex) = Now .Column(3, RowIndex) = Target.Address .Column(4, RowIndex) = Target.Cells(1).Value End With End Sub

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

Рис. 3.4.  Журнал изменений, созданный обработчиком события Change
В журнале нашли отражение почти все изменения, происшедшие со страницами рабочих книг - с разными ячейками, разными страницами, разными рабочими книгами. Почему не все изменения были зафиксированы в журнале, я скажу чуть позже, специально остановившись более подробно на объяснении этой ситуации. А сейчас давайте рассмотрим специальный обработчик этого же события, предусмотренный для страницы с именем "Лист1" книги . Вот его текст: Private Sub Worksheet_Change(ByVal Target As Range) Static NumChange As Integer Dim Myr As Range NumChange = NumChange + 1 MsgBox ("Пишу Изменения!") Set Myr = ThisWorkbook.Worksheets("Лист2").Range("A1") Myr.Offset(NumChange, 0) = Target End Sub
Изменения, происходящие с ячейками этой страницы, будут фиксироваться не только в общем журнале изменений, но и заноситься на следующий лист этой же книги. Этот лист, по существу, является журналом изменений данной конкретной страницы. Такова общая схема работы с событием Change. А теперь давайте поговорим о нюансах.

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

События объекта Chart

В отличие от объекта Worksheet, все события которого могут быть обработаны на верхнем уровне, объект Chart имеет специфические события, сообщения о которых направляются только ему одному. Встроенные диаграммы и листы диаграмм, имеют одни и те же события. Разница состоит в том, что события встроенных диаграмм по умолчанию выключены, поэтому необходимо потрудиться, чтобы стало возможным их подключение и написание обработчиков событий. Рассмотрим список событий, связанных с объектом Chart: Таблица 3.9. События объекта ChartСобытиеПоявляется, когда пользователь или программа

Activate	Активизировал лист диаграмм. Естественно, его нет у встроенных диаграмм.
BeforeDoubleClick	Дважды щелкает кнопкой мыши на диаграмме
BeforeRightClick	Щелкает правой кнопкой мыши на диаграмме
Calculate	Добавил или изменил данные на диаграмме.
Deactivate	Активизировал новый лист и тем самым деактивировал старый.
DragOver	Перетащил данные, расположив их поверх диаграммы.
DragPlot	Перетащил диапазон ячеек, расположив их поверх диаграммы.
MouseDown	Нажал кнопку мыши при позиционировании ее над диаграммой.
MouseMove	Передвигает указатель мыши по диаграмме.
MouseUp	Закончил перемещение мыши и освободил кнопку.
Resize	Изменил размер диаграммы.
Select	Выделил некоторый элемент диаграммы
SeriesChange	Изменил значение точки ряда данных.

События объекта Excel.Application

Объект Excel.Application может обрабатывать 21 событие, возникающие при работе с теми или иными объектами приложения Excel. Почти половина из этих событий возникает в процессе работы с объектом Workbook, другая половина событий связана с такими объектами, как страница документа (объект Sheet) и окно (объект Window). Практически все события, за исключением одного события NewWorkBook, могут быть обработаны на двух уровнях - объектом Application и объектом Workbook. Но прежде чем поговорить, о том, какие события может обрабатывать объект Application, в каких случаях следует проводить обработку события на уровне объекта Application, а в каких - на уровне объекта Workbook, давайте разберемся, как заставить объект Excel.Application вообще реагировать на события.

События объекта Workbook

Со всеми событиями, которые может обрабатывать объект Workbook, мы уже знакомы. Всего таких событий 20, из них 9 событий связаны непосредственно с самим объектом Workbook, 8 - возникают на страницах рабочей книги и связаны также с объектом Sheet, три события связаны с объектом Window. Я напомню, что при возникновении события сообщение о нем операционная система посылает, как правило, нескольким объектам. Все они, каждый по-своему, могут обрабатывать это событие. Подробно обо всем этом рассказано при рассмотрении событий объекта Application.

События объекта Worksheet

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

События, связанные с объектом Sheet

Ряд событий, которые возникают в процессе работы со страницами той или иной рабочей книги, также могут быть обработаны на уровне объекта Application. Естественно, что эти события могут быть обработаны и на более низких уровнях - как на уровне объекта Workbook, так и на следующем уровне объектами, задающими саму страницу - Worksheet и Chart. Понятно, что на самом верхнем уровне задается обработка, общая для всех страниц всех книг, на следующем уровне обработка, общая для страниц конкретной книги, и на нижнем уровне - обработка, специфическая для данной страницы. В таблице 3.4 дана сводка событий, связанных с объектом Sheet, обрабатываемых на уровне объекта Application. Таблица 3.4. События, возникающие при работе с объектом Sheet СобытиеКогда возникаетПараметры события

SheetActivate(Sh As Object)	Страница становится активной.	Вновь активированная страница передается обработчику события в качестве параметра.
SheetBeforeDoubleClick(Sh As Object, Target As Range, Cancel As Boolean)	При двойном щелчке левой клавиши мыши на рабочей странице, но до того, как выполнится макрос, задающий реакцию на щелчок. Событие не возникает на Chart-страницах.	Первый параметр передает обработчику события объект Sh, задающий рабочую страницу, на которой был произведен двойной щелчок. Второй параметр Target возвращает ячейку (объект Range), ближайшую к указателю мыши в тот момент, когда был произведен щелчок. Если в обработчике события изменить значение параметра Cancel на True, то отменится выполнение макроса, задающего реакцию на двойной щелчок.
SheetBeforeRightClick(Sh As Object, Target As Range, Cancel As Boolean)	Аналогично двойному щелчку, но при нажатии правой клавиши мыши.	Параметры сохраняют смысл, описанный для обработчика событий двойного щелчка.
SheetCalculate(Sh As Object)	При перевычислениях рабочей страницы или при любых изменениях данных, отображаемых на диаграмме страницы диаграмм.	В зависимости от того, на странице какого типа произошло событие, параметр Sh представляет либо объект Workbook либо объект Chart.
SheetChange(Sh As Object, Target As Range)	При изменениях в ячейках рабочей книги, инициированные пользователем или внешней ссылкой. Не возникает для Chart-страниц.	Параметр Sh задает объект WorkSheet - страницу, в ячейках которой произошли изменения. Параметр Target задает область изменения - объект Range.
SheetDeactivate(Sh As Object)	Страница перестает быть активной, поскольку активной становится другая страница.	Деактивированная страница передается обработчику события в качестве параметра.
SheetFollowHyperlink(Sh As Object, Target As Hyperlink)	При щелчке по гиперссылке на рабочей странице. Не возникает для Chart-страниц.	В качестве параметров обработчику события передаются два объекта, представляющие рабочую страницу и гиперссылку, задающую переход.
SheetSelectionChange(Sh As Object, Target As Range)	При изменении области выделения рабочей страницы. Не возникает для Chart-страниц.	Параметр Sh задает объект WorkSheet -страницу, содержащую новую область выделения. Параметр Target задает новую область выделения - объект Range.

События, связанные с объектом Window

Объект Application может обработать три события, возникающие в процессе работы с окном - объектом Window. Эти события показаны в следующей таблице. Таблица 3.5. События, возникающие при работе с объектом Window СобытиеКогда возникаетПараметры события

WindowActivate(Wb As Workbook, Wn As Window)	Окно рабочей книги становится активным.	Рабочая книга и вновь активированное окно передаются обработчику события в качестве параметров.
WindowDeactivate(Wb As Workbook, Wn As Window)	Окно рабочей книги перестает быть активным.	Рабочая книга и деактивированное окно передаются обработчику события в качестве параметров.
WindowResize(Wb As Workbook, Wn As Window)	Окно рабочей книги изменяет размеры.	Рабочая книга и перестраиваемое окно передаются обработчику события.

Полагаю, что в дополнительных комментариях и примерах эти события не нуждаются.

События, связанные с рабочей книгой

В нижеследующей таблице 1 дана сводка всех событий, которые возникают при работе с рабочими книгами - объектами Workbook, и которые могут быть обработаны объектом Application. Таблица 3.3. События, возникающие при работе с объектом WorkbookСобытиеКогда возникаетПараметры события

NewWorkbook(Wb As Workbook)	При создании новой книги. Единственное событие этой группы, которое может обработать только объект Application.	Обработчику события передается объект Wb, представляющий вновь созданную книгу.
WorkbookActivate(Wb As Workbook)	Книга становится активной.	Вновь активированная книга передается обработчику события в качестве параметра.
WorkbookAddinInstall(Wb As Workbook)	При установке рабочей книги в качестве AddIn.	Рабочая книга, представляющая AddIn.
WorkbookAddinUninstall(Wb As Workbook)	Отменяется установка рабочей книги в качестве AddIn. Закрытие книги при этом не происходит.	Рабочая книга, представляющая AddIn.
WorkbookBeforeClose(Wb As Workbook, Cancel As Boolean)	При попытке закрыть рабочую книгу, но до того, как она будет закрыта.	Параметр Wb задает закрываемую книгу. Параметр Cancel позволяет отменить закрытие, если в обработчике события его значение будет установлено как True.
WorkbookBeforePrint(Wb As Workbook, Cancel As Boolean)	При попытке распечатать содержимое рабочей книги, но до того, как произойдет печать.	Параметр Wb задает печатаемую книгу. Параметр Cancel позволяет отменить печать, если в обработчике события его значение будет установлено как True.
WorkbookBeforeSave(Wb As Workbook, SaveAsUI As Boolean, Cancel As Boolean)	При попытке сохранить содержимое рабочей книги, но до того, как произойдет сохранение.	Параметр Wb задает сохраняемую книгу. Параметр Cancel позволяет отменить сохранение, если в обработчике события его значение будет установлено как True. Параметр SaveAsUI показывает, как идет сохранение, его значение равно true, если при сохранении открывается диалоговое окно "Сохранить как ".
WorkbookDeactivate(Wb As Workbook)	Книга перестает быть активной, поскольку активной становится другая книга.	Деактивированная книга передается обработчику события в качестве параметра.
WorkbookNewSheet(Wb As Workbook, Sh As Object)	При добавлении новой страницы в рабочую книгу.	Объект Wb задает книгу, а Sh - страницу, добавленную в эту книгу.
WorkbookOpen(Wb As Workbook)	При открытии уже существующей рабочей книги.	Обработчику события передается объект Wb, представляющий вновь открытую книгу.

Практически все события, происходящие с объектом Workbook, могут быть обработаны на двух уровнях - объектом Application и самим объектом Workbook. Разница лишь состоит в том, что если у объекта Application есть, например, событие WorkbookOpen, то у объекта Workbook есть событие Open. При возникновении данного события операционная система посылает соответствующее сообщение двум объектам - Application и Workbook. Обработчику сообщения WorkbookOpen передается параметр Wb, задающий открываемую книгу. Понятно, что при посылке аналогичного сообщения объекту Workbook передавать этот параметр не имеет смысла, поскольку он и так знает сам себя, так что обработчик события Open объекта Workbook параметров не имеет.
Возникает естественный вопрос, в каких случаях следует вести обработку события, происходящего с объектом Workbook, на уровне объекта Application. Ответ понятен - в тех случаях, когда обработчик события выполняет действия, общие для всех рабочих книг. В тех же случаях, когда предполагается специфическая обработка события, характерная только для данной конкретной книги, обработчик события связывается с объектом Workbook. Что происходит, если обработка одного и того же события предусмотрена на двух уровнях? В этом случае вначале выполнится обработчик события, связанный с объектом Workbook, - он выполнит специфическую для данной книги обработку, а потом начнет работать обработчик этого же события, связанный с объектом Application, выполняющий ту часть работы, которая является общей для всех рабочих книг.
Приведу пример, в котором предусмотрена общая для всех рабочих книг обработка события BeforeSave: Private Sub ExApp_WorkbookBeforeSave(ByVal Wb As Workbook, _ ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim YesNo As Variant YesNo = MsgBox("Вы действительно хотите сохранить этот документ?", vbYesNo) If YesNo = vbNo Then Cancel = True End Sub
Предупреждающее сообщение будет появляться для всех рабочих книг. Рассмотрим теперь пример, когда обработка события BeforePrint предусмотрена на двух уровнях: Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Обработка события - печать содержимого книги. MsgBox ("Эту книгу - " & ThisWorkbook.Name _ & " печатать запрещено!") Cancel = True End Sub


Private Sub ExApp_WorkbookBeforePrint( ByVal Wb As Workbook, Cancel As Boolean) 'Обработка события - печать содержимого книги. If Wb.ActiveSheet.Name = "Лист1" Then MsgBox ("Эту страницу книги - " & Wb.Name _ & " печатать запрещено!") Cancel = True End If End Sub
Специальный обработчик события объекта Workbook книги запрещает печать только этой книги, а общий для всех книг обработчик того же события, но находящийся в объекте Application, запрещает печать только первого листа, но для всех рабочих книг. Вот как выглядит сообщение, выданное для книги общим обработчиком при попытке распечатать содержимое первого листа книги:


увеличить изображение: ,
Рис. 3.3.  Сообщение, выданное обработчиком события WorkbookBeforePrint

Создание объекта Application, реагирующего на события

Для всех приложений Office 2000 соответствующие объекты Application хотя и могут реагировать на события, но появляются как объекты без событий. И нужно приложить некоторые усилия, чтобы создать новый объект Application With Events, который может реагировать на события. Причина возникающих сложностей кроется в логике построения программного проекта, изначально принятой в Office. Дело в том, что обработчики событий, возникающих при работе с теми или иными объектами, следует размещать в специальных модулях - обработчиках событий. Для большинства стандартных объектов Office эти модули создаются автоматически в момент создания основного объекта. Так, например, при создании документа в его проекте автоматически создается модуль, обрабатывающий события этого документа, - для Excel это модуль с именем "ЭтаКнига" (ThisWorkbook). Только в этом модуле и можно создать обработчики событий объекта Workbook и всех объектов, реагирующих на события, например, элементов управления, встраиваемых непосредственно в документ.
Объект Application, единый для всех рабочих книг, по понятным причинам не вписывается в эту общую схему и для него такой модуль автоматически не создается, потому, естественно, эту работу приходится выполнять программисту.
Для того чтобы заставить реагировать на события объект Excel.Application, необходимо выполнить четыре шага:

Создать модуль, задающий собственный класс для объекта Excel.Application, в котором можно будет размещать обработчики возникающих событий. Устроен этот класс очень просто и состоит в момент создания из одной строчки, поскольку класс не имеет методов и имеет ровно одно свойство, задающее объект Application с событиями. Конечно, позже этот класс будет пополняться обработчиками событий. Вот как выглядит на этапе создания описание этого класса, которому я в своем примере дал имя AppWithEvents. Option Explicit 'Класс, описывающий объект Application With Events. 'Класс не имеет методов, задается одним свойством. Public WithEvents ExApp As Application

Следующее, что необходимо сделать - это определить объект вновь созданного класса AppWithEvents. И эта задача решается в одну строчку. Объявление объекта я поместил в раздел объявлений созданного ранее стандартного модуля. Заметьте, необходимо не только объявить объект, но и создать его, используя спецификатор New. Public AppWithEv As New AppWithEvents

Третьим шагом является связывание двух объектов Application, стандартного и созданного объекта, умеющего реагировать на события. По-видимому, лучшим местом, где следует осуществить связывание, является обработчик события Open того документа, в котором описан класс AppWithEvents. Тогда после открытия этого документа и вплоть до его закрытия Excel будет реагировать на события, связанные с объектом Excel.Application. Вот как осуществляется связывание: Private Sub Workbook_Open() 'Связывание двух объектов Application - 'стандартного и реагирующего на события. Set AppWithEv.ExApp = Excel.Application End Sub

На этом шаге созданный класс AppWithEvents можно расширить, добавив в него обработчики событий объекта Application. Я приведу пока текст только одного обработчика, который обрабатывает событие NewWorkBook, возникающее при создании новой книги. Private Sub ExApp_NewWorkbook(ByVal Wb As Workbook) 'Обработка события - создание новой книги. Static Num As Integer Num = Num + 1 MsgBox ("Число вновь созданных книг - " & Num _ & vbCrLf & "Новая книга - " & Wb.Name _ & " открыта в " & Time) End Sub

Вот как выглядит сообщение, появляющееся при открытии новой книги:

Рис. 3.2.  Сообщение об открытии новой книги

Сравнение свойств объектов 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.

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

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

Свойства объекта Worksheet

Среди свойств, как всегда, наибольший интерес представляют свойства-участники, возвращающие некоторый отдельный объект или коллекцию в качестве результата. Эти свойства определяют структуру объекта Worksheet, задавая непосредственно вложенные в него объекты.

Свойства - участники объекта Chart

Объекты, вложенные в Chart, представлены на , а на следующем рисунке показана структура объектов ChartGroup и Series.

Рис. 3.18.  Структура объекта Chart

Рис. 3.19.  Структура объектов ChartGroup и Series
Давайте познакомимся с объектами, вложенными в Chart:

PivotLayout - это новый объект, появившийся в Office 2000. Его появление связано с усилением внимания к обработке многомерных источников данных, а, следовательно, и к сводным таблицам и диаграммам. Объект имеет много свойств, позволяющих добраться до полей сводной таблицы, и много методов, например, метод AddFields, позволяющий добавить новое поле. С его помощью можно также работать с осями сводной диаграммы.

ChartArea - представляет область, отведенную под диаграмму. Для двумерных диаграмм эта область включает заголовок диаграммы, легенду, оси и их заголовки, а для трехмерных - только заголовок диаграммы и легенду. Вложенные в ChartArea объекты: Border, Font, Interior, FillFormat позволяют задать границы области, используемый для подписей шрифт, формат заполнения, цвет, образец заполнения фона и другие характеристики. Терминальные свойства Height, Width, Left, Top задают границы области. Методы Select, Clear, Copy позволяют выделить область, очистить ее содержимое или поместить его в буфер.

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

ChartTitle - является "полноценным" объектом. Помимо свойства Text, задающего текст заголовка диаграммы в него встроены собственные объекты: Border, Font, Interior, FillFormat. Кроме этого можно задать ориентацию заголовка, выравнивание, расположение. Есть даже собственные методы - Select и Delete.

Axes(Axis) - С осями диаграммы есть смысл разобраться подробнее. Во-первых, есть некоторая путаница в классификации - что есть Axes -метод или свойство? Будем считать, что есть и метод и свойство. Метод Axes(Type, AxisGroup) возвращает одну ось - объект Axis. Параметр Type имеет три значения: xlValue, xlCategory, или xlSeriesAxis (только для трехмерных диаграмм), указывающих, какая из трех возможных осей возвращается. Второй параметр - AxisGroup указывает, какая ось возвращается - основная или вспомогательная. Этот параметр имеет два значения: xlPrimary или xlSecondary. Для трехмерных диаграмм возможно использование только основных осей. Свойство Axes (или метод, вызываемый без параметров) возвращает коллекцию осей. Метод Item этой коллекции позволяет добраться до нужной оси. Пример работы с этим объектом был приведен. Замечу, что вспомогательные оси полезны, когда на диаграмме отображаются несколько рядов данных, имеющих разные масштабы. Соответствующий пример построения такой оси уже был приведен.

SeriesCollection(Series) - коллекция рядов данных. Как обычно, параметр Index или метод Item позволяют добраться до элемента коллекции - объекта класса Series, представляющего конкретный ряд данных. Коллекция имеет следующие методы:

Add, позволяющий добавить новый ряд данных,

Extend, добавляющий новые точки в ряды данных коллекции,

NewSeries, создающий новый ряд данных и возвращающий в качестве результата объект класса Series.

Конечно, непросто устроен и объект Series, задающий сам ряд данных. Его структура показана на , а некоторые из основных его свойств были продемонстрированы в предыдущем примере.

DataTable- объект, представляющий таблицу данных. Основными методами являются Select и Delete, основные свойства связаны с рамкой, строящейся вокруг таблицы. Вся содержательная работа с данными таблицы ведется через другие объекты (ряды данных). Так что по существу этот объект представляет рамку таблицы данных.

Legend - задает легенду диаграммы. O сущности этого объекта мы уже говорили, так что его поведение должно быть понятно.

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

ChartGroups(ChartGroup) - возвращает коллекцию групп. Элементами коллекции являются объекты класса ChartGroup. Каждый такой объект представляет группу, связанную с одним или несколькими рядами данных, отображаемых диаграммой одного и того же типа, о чем я уже подробно рассказывал. Параметр Index позволяет добраться до конкретной группы в коллекции. Поскольку при форматировании одной из группы индексы изменяются, то иногда удобнее пользоваться специальными методами, которые возвращают группы фиксированного типа. Такими методами являются: AreaGroups, BarGroups, ColumnGroups, DoughnutGroups, LineGroups и PieGroups. Эти методы для двумерных диаграмм возвращают коллекцию групп типа, указанного методом. К конкретной группе можно добраться опять - таки с помощью индекса. Для трехмерных диаграмм может быть только одна группа определенного формата. Поэтому есть методы, возвращающие эту единственную группу: Area3DGroup, Bar3DGroup, Column3DGroup, Line3DGroup, Pie3DGroup, SurfaceGroup.

Floor, Walls и Corners объекты используются при работе с трехмерными диаграммами. При отображении таких диаграмм для создания иллюзии трехмерного пространства диаграмма отображается на фоне открытого куба, имеющего основание, заданное объектом Floor, и две боковые грани - объекты Walls. Объект Corners задает углы куба. Манипулируя этими объектами, можно, например, развернуть куб или изменить заливку и узор стенок куба, добиваясь большей наглядности изображения.

Основное содержание этой главы будет связано с рассмотрением коллекции Workbooks, а точнее с объектом Workbook и вложенными в него объектами. Но прежде чем двинуться далее, приведу все-таки краткий обзор тех вложенных в Excel.Application объектов, доступных на этом уровне, по сути, относящихся к нижним уровням иерархии объектной модели Excel:

Группа активных объектов - ActiveWorkbook, ActiveWindow, ActiveSheet, ActiveChart, ActiveCell, ActivePrinter, - возвращающих активную рабочую книгу, окно, активную рабочую страницу, диаграмму или ячейку, если таковые существуют в момент вызова соответствующего свойства. При отсутствии запрашиваемого активного объекта возникнет ошибка. Все эти объекты будут подробно рассмотрены, но чуть позже, когда мы спустимся вниз по иерархии объектов. Особняком стоит свойство, возвращающее активный принтер. Это свойство действительно имеет смысл связать с приложением. Заметьте, что объекты, стоящие на нижних уровнях иерархии, например, Workbook, этим свойством не обладают, так что добраться до принтера можно только через объект Application.

Группа коллекций и объектов Range, входящих в состав соответствующего активного объекта - Sheets, Charts, Rows, Columns, Cells, Range - возвращающие соответственно коллекции рабочих страниц, страниц диаграмм активной рабочей книги, объект Range, содержащий все строки, столбцы, ячейки или заданную область активной рабочей страницы. Также как и в случае вызова объектов предыдущей группы, при вызове этих свойств следует быть осторожным, поскольку возникает ошибка, если нет соответствующего активного объекта.

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

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

Свойства-участники объекта Workbook

Свойств, возвращающих объекты, у объекта Workbook относительно немного. Поэтому коротко можно рассказать о каждом. При их описании я разделю их на группы:

Группа свойств, возвращающих основные объекты рабочей книги. К ним я отношу следующие свойства:

Sheets, Charts, WorkSheets, Excel4MacroSheets - все они возвращают объект класса Sheets, но в зависимости от свойства этот объект представляет либо всю коллекцию листов рабочей книги, либо ту ее часть, которая содержит листы типа, заданного именем свойства

ActiveSheet - возвращает объект класса Object, содержащий активный лист рабочей книги. Тип активного листа и определяет тип возвращаемого объекта.

ActiveChart - возвращает объект класса Chart - это может быть лист-диаграмма, если активный в момент вызова лист принадлежит этому типу, либо диаграмма, встроенная в активный рабочий лист, если и сама диаграмма на этом листе является в свою очередь активной. В противном случае при вызове свойства будет возвращен результат Nothing, свидетельствующий о том, что объект не найден.

Группа свойств, возвращающих общие или схожие объекты. Это самая большая группа свойств объекта Workbook, что говорит о том, что общих черт у объекта Workbook больше чем специфических.

CommandBars - общий объект, возвращающий коллекцию инструментальных панелей приложений Office 2000.

VBProject - общий объект, описывающий программный проект приложений Office 2000. Необходим при программировании "на лету" - программной работе на VBA с программным проектом.

HMLProject - общий объект библиотеки Office, описывающий проект в формате HTML. Этот объект используется при программировании на VBScript.

WebOptions - объект, схожий во всех приложениях Office 2000, позволяющий задавать параметры Web-страниц. Действие большинства параметров распространяются на все приложения Office 2000.

Windows - коллекция окон, связанных с рабочей книгой. Особенностью документа Excel от документов других типов состоит в том, что документ Excel можно одновременно открыть в нескольких окнах, чтобы в каждом из них видеть требуемую часть рабочей книги. Сами же объекты Window схожи во всех приложениях. Эта коллекция является частью более общей коллекции окон, возвращаемой аналогичным свойством объекта Application.

RoutingSlip - объект, схожий для всех приложений Office 2000. Он определяет характеристики передачи рабочей книги по сети участникам, совместно работающим над данным документом. При работе с ним должно быть включено булево терминальное свойство HasRoutingSlip.

Styles - объект, схожий для приложений Office 2000 - определяет коллекцию применяемых для форматирования стилей. Есть набор встроенных стилей, но в коллекцию можно добавлять и собственные стили, задав в момент добавления соответствующие характеристики стиля - шрифт, размер, рамки и т.д. Обычно стили создаются руками, но, возможно и программное создание или изменение того или иного стиля.

BuiltinDocumentProperties, CustomDocumentProperties - оба эти свойства возвращают объект класса DocumentProperties из общей библиотеки Office. Этот класс определяет коллекцию свойств документов Office 2000. Эта коллекция напоминает коллекцию Sheets тем, что ее элементы могут относиться к различным типам. Свойство BuiltinDocumentProperties возвращает ту часть коллекции, которая задает стандартные свойства, заданные самим приложением, CustomDocumentProperties - свойства, определенные пользователем для конкретного документа. Свойства документов, являются схожими для всех приложений Office 2000. С двумя типами свойств работа идет по-разному. Коллекция стандартных свойств определена для каждого типа документа Office 2000, - добавлять или удалять элементы этой коллекции нельзя. В тоже время, коллекция пользовательских свойств изначально пуста и добавление или удаление свойств является основным способом работы с этой коллекцией. Большинство из стандартных свойств автоматически изменяют свои значения в процессе работы с документом, отражая, например, текущие характеристики документа - количество страниц, символов, дату последнего изменения и другие характеристики. Значения других стандартных свойств, также как и всех пользовательских свойств могут быть заданы пользователем или программно.

Mailer - схожий для всех приложений Office 2000 почтовый объект, используемый при работе на компьютерах Макинтош.

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

Names - коллекция имен, которые даны отдельным областям листов рабочей книги. Типичной является ситуация, когда при работе с рабочей страницей, выделяется та или иная область, содержащая определенные данные, и этой области даются имена. При программировании очень часто приходится использовать имена таких объектов. Полагаю, что примеров в свое время будет достаточно. Напомню, что объект Application также имеет свойство Names, возвращающее коллекцию имен, включающую коллекции имен всех открытых рабочих книг.

PublishObjects - возвращает одноименную коллекцию, элементы которой принадлежат классу PublishObject. Эти объекты являются новинкой Excel 2000, они задают элементы рабочей книги, опубликованные на Web-странице. С их помощью можно вести обновление Web-страницы при изменениях состояния опубликованных объектов.

Container - объект, который содержит рабочую книгу. Типичным примером, когда рабочая книга может быть встроена в другой объект, и являться его частью, служит подшивка - объект Binder. Следует заметить, что в отличие от предыдущей версии подшивки пока не стали частью семейства Office 2000.

CustomViews - возвращает одноименную коллекцию, элементы которой задают образ той или иной части рабочей книги в том виде, как он виден на экране дисплея. Каждому такому образу дается свое имя. Ранее я говорил, что для того, чтобы увидеть ту или иную часть рабочей книги, используются окна. Custom View - это другая возможность задать коллекцию образов экрана, отражающих заданные части рабочей книги. В нижеследующем примере первая из приведенных процедур создает объект CustomView, сохраняющий текущий образ рабочей книги. Вторая процедура, которую можно вызвать в подходящий момент, позволяет показать на экране нужную часть рабочей книги в соответствии с сохраненным образом.

Public Sub AddCustView() 'Добавить Custom View - образ экрана, 'отражающий текущий вид рабочей книги. ThisWorkbook.CustomViews.Add ("Две кнпки") Debug.Print ThisWorkbook.CustomViews.Count ThisWorkbook.CustomViews(1).Show End Sub
Public Sub ShowCustView() 'Показать Custom View - вывести на экран, 'заданный вид рабочей книги. Dim CW As CustomView If ThisWorkbook.CustomViews.Count > 0 Then For Each CW In ThisWorkbook.CustomViews If CW.Name = "Две кнопки" Then CW.Show Next CW End If End Sub

Свойства - участники объекта

Рассмотрим теперь свойства - участники объекта Excel.Application, возвращающие объекты, специфические для Excel, Как я и предупреждал, я рассмотрю лишь основные свойства, которые действительно необходимы при работе с объектом Excel.Application. Таблица 3.2. Основные свойства - участникиСвойство, возвращающее объектНазначение объекта

WorkBooks	Коллекция открытых в Excel документов - рабочих книг. Основной объект, благодаря которому можно получить доступ к любому документу Excel и далее работать с объектами этой рабочей книги.
Windows	Коллекция открытых окон во всех рабочих книгах. Дело в том, что одну и ту же рабочую книгу часто полезно открывать в нескольких окнах, что позволяет видеть разные участки рабочей книги. Коллекция Windows позволяет получить доступ к каждому такому окну. Чаще всего, свойство Windows используется при работе с объектом WorkBook, для объекта Application это один из примеров той перегрузки, о которой я упоминал выше.
WorkSheetFunction	Объект - контейнер, в котором находятся многочисленные функции Excel, начиная от обычных математических функций и кончая функциями, применяемыми для решения задач статистики, прогноза, работы с датами и прочими.
AddIns	Коллекция компонент, расширяющих возможности решения специальных задач в Excel.
AutoCorrect	Знакомый по приложению Word объект, позволяющий задавать автоматическую корректировку набираемых текстов в ячейках Excel.
DefaultWebOptions	Объект, позволяющий устанавливать параметры для документов Excel, сохраненных в виде Web-страниц. Схож с аналогичным объектом Word.Application, но имеет свою специфику.
Dialogs	Объект Dialogs также как и три предыдущих объекта - AddIns, AutoCorrect, DefaultWebOptions относится к группе схожих объектов, встречающихся в каждом из приложений Office 2000, имеющих много общего, но имеющих и отличия, связанные со спецификой приложения. Также как и в Word, объект Dialogs задает коллекцию стандартных диалоговых окон, которые могут открываться в Excel, позволяя организовать диалог с пользователем.
Names	Одно из перегруженных свойств, возвращающее коллекцию всех имен, используемых для отдельных ячеек и областей всех открытых документов Excel. Чаще всего, это свойство используется при работе с отдельной рабочей книгой или отдельной страницей.
ODBCErrors	Коллекция объектов класса ODBCError. Элементы этой коллекции создаются автоматически источником ODBC-данных, если при выполнении запроса на получение данных возникли ошибки. Если ошибок не было, то и коллекция будет пустой.
OLEDBErrors	Коллекция объектов класса OLEDBError. Аналогично предыдущей коллекции, ее элементы появляются при наличии ошибок в процессе работы с базой данных, когда используется интерфейс OLE DB.
RecentFiles	Объект, относящийся к группе схожих объектов семейства Office 2000. Он задает коллекцию файлов, хранящих документы Excel последнего использования.

Свойства - участники

Дадим краткую характеристику свойствам - участникам, входящим в рабочий лист:

Range, Cells, Rows, Columns, UsedRange, CircularReference - начнем с группы свойств, возвращающих объект Range. Я уже говорил, что объект Range - это основной объект электронной таблицы. Он позволяет задать, как отдельную ячейку таблицу, диапазоны ячеек, представляющие прямоугольную область таблицы, так и области более сложной конфигурации. Именно объект Range со своими свойствами и методами позволяет осуществлять непосредственную работу, как с отдельной ячейкой, так и с областями ячеек. Об этом объекте я еще много буду говорить, но уже сейчас хочу заметить, что большинство свойств рабочего листа Worksheet, о которых пойдет речь, характерны и для объекта Range, задающего часть рабочего листа. Объект Range возвращается в качестве результата при вызове следующих свойств:

Range(Cell1, [Cell2]) As Range - возвращает объект Range, определяемый параметрами свойства. Синтаксис параметров таков, что он позволяет определить достаточно изощренный объект. Я расскажу об этом подробнее чуть позже, когда мы займемся подробным рассмотрением объекта Range.

Cells As Range - возвращает коллекцию ячеек электронной таблицы. Вызванное объектом WorkSheet это свойство возвращает всю таблицу ячеек рабочего листа, которая, конечно, представляет собой объект Range. Поскольку Cells одновременно является объектом Range и коллекцией ячеек, то можно использовать индексы, чтобы добраться до отдельного элемента коллекции - ячейки таблицы. Важным свойством Cells обладают и объекты, стоящие на более низких ступенях иерархии, в частности, им обладает и сам объект Range, что позволяет получить коллекцию ячеек для любой заданной области таблицы.

Rows As Range и Columns As Range - соответственно возвращают коллекции строк и столбцов таблицы. По индексу можно добраться до отдельной строки или столбцу таблицы. Одновременно эти коллекции являются объектами Range, поскольку задают некоторую область рабочего листа.

UsedRange As Range - возвращает используемую область рабочего листа. Как правило, лишь небольшая часть рабочего листа занята данными, формулами, рисунками, диаграммами и графиками. Свойство UsedRange позволяет получить минимальную прямоугольную область, содержащую используемую область рабочего листа.

CircularReference As Range - возвращает объект Range, содержащий первую циклическую ссылку, если таковые имеются на рабочем листе. В противном случае возвращается значение Nothing. О циклических ссылках поговорим подробнее чуть позже.

Одну и ту же область таблицы - один и тот же объект Range - можно получить разными способами. Приведем пример, показывающий два способа получения ячейки "А1": Debug.Print ActiveSheet.Range("A1") Debug.Print ActiveSheet.Cells(1, 1) Оба оператора здесь эквивалентны, но только в одном случае используется свойство Range, в другом - Cells. В следующем примере работа идет над отдельным столбцом и строкой, но, фактически, и здесь действует тот же объект Range: ActiveSheet. Columns(2).Value = "Да" ActiveSheet.Rows(1).Font.Bold = True

Shapes - возвращает одноименную коллекцию, элементами которой являются объекты класса Shape. Эта коллекция состоит из объектов самых различных типов. По существу, все, что вставляется в рабочий лист, - рисунки, диаграммы, графики, встроенные и связанные OLE-объекты, элементы управления, размещаемые на рабочем листе, - все это объекты коллекции Shapes. При программировании нам неоднократно придется сталкиваться с этими объектами. После объектов Range объекты Shape представляют наиболее часто встречающийся тип объектов, определяющих суть рабочего листа. Замечу еще, что коллекция Shapes и объекты Shape относятся к схожим объектам, встречающимся во всех приложениях Office 2000. При описании объектов Word нам уже приходилось встречаться с этими объектами.

Names - возвращает неоднократно упоминавшуюся одноименную коллекцию. Подобное свойство имеют объекты Application и Workbook, имеет его и объект WorkSheet. Разница состоит только в том, что здесь речь идет об именах, используемых в данном рабочем листе.

Comments - возвращает одноименную коллекцию с элементами класса Comment, представляющими комментарии, которые можно привязывать к той или иной ячейке рабочего листа - объекту Range. Замечу, что комментарии несут очень важную нагрузку в создании дружелюбного интерфейса разрабатываемых документов. Их следует широко использовать. Эти объекты также относятся к схожим объектам, и встречаются во всех приложениях Office 2000. Но в Excel комментарии к ячейкам играют особую роль, значительно более важную, чем комментарии в документах Word. В текстовых документах можно использовать различные стили, чтобы непосредственно в тексте документа вставлять авторские замечания, предупреждения и пояснения. Комментарии, как правило, используются в тех случаях, когда не желательно прерывать плавное изложение материала, чтобы работающий с документом мог прочесть основной текст, лишь при желании знакомясь с комментариями. В Excel'е, когда основное содержание листа составляют таблицы, все замечания, пояснения и предупреждения оформляются в виде комментариев к тем или иным ячейкам. Они, например, могут давать подсказку, какие данные располагаются в ячейке, определять их формат и давать другую полезную информацию, необходимую пользователю при работе с таблицами рабочего листа. Есть некоторая разница в программной работе с комментариями в Word и Excel. Вот как выглядит пример введения комментариев в документ Excel: Public Sub AddComments() 'Формируется последовательность чисел Фибоначчи. 'Вставляется комментарий, поясняющий суть чисел. Dim myRange As Range Workbooks("BookThree").Activate With ActiveWorkbook.Worksheets(2) Set myRange = .Range("E1") With myRange .Value = "Числа Фибоначчи" .Offset(1, 0).FormulaR1C1 = "0" .Offset(2, 0).FormulaR1C1 = "1"

.Offset(3, 0).FormulaR1C1 = "=R[-2]C +R[-1]C" .Offset(3, 0).Select Selection.AutoFill Destination:=Range("E4:E20"), _ Type:=xlFillDefault End With 'Добавление комментария myRange.AddComment "Числа Фибоначчи - это ..." .Comments(1).Visible = False If (.Comments(1).Author = "Vladimir Billig") Then Debug.Print "OK!" End If 'Показ и удаление комментария .Comments(1).Visible = True '.Comments(1).Delete End With
End Sub В этом примере я вначале программно формирую последовательность чисел Фибоначчи, а затем к ячейке, задающей заголовок, добавляю комментарий, поясняющий сущность этих чисел. Заметьте, в Excel в отличие от Word коллекция комментариев не имеет метода Add, - они вводятся специальным методом AddComment объекта Range. Они по-другому показываются, используя свойство Visible, что, пожалуй, более естественно. Заметьте также, что свойство Author можно использовать только для чтения.

QueryTables - возвращает одноименную коллекцию с элементами класса QueryTable, каждый из которых представляет таблицу, полученную на основе запроса к внешнему источнику данных. Внешним источником данных может быть база данных, Web-страницы в Интернет. Я уже вскользь упоминал о построении Web-запроса, когда рассматривал внешние ссылки при описании объекта Application. Следует сказать, что, несмотря на то, что объект QueryTable присутствовал и в предыдущей версии Office 97, в нынешней версии этот объект претерпел существенные изменения, и у него появилось множество новых свойств, что, в первую очередь, связано с возможностью построения Web-запросов. Об этом объекте я еще скажу более подробно чуть позже.

Hyperlinks - возвращает одноименную коллекцию с элементами класса Hyperlink, - гиперссылками, задающими связи (переходы) ячеек рабочего листа с внешним миром. В качестве гиперссылки может, например, использоваться URL-адрес в Internet. Объекты Hyperlink относятся к группе схожих объектов, встречающихся во всех приложениях Office 2000.

Outline - возвращает одноименный объект, задающий структурированное представление рабочего листа. Зачастую данные, представленные на рабочем листе можно структурировать, сжимая или разворачивая их по мере необходимости. Типичной является ситуация, когда данные, отражающие работу некоторого предприятия, представлены по дням, неделям, месяцам, кварталам и годам. При глобальном анализе деятельности предприятия нас могут интересовать только сводные результаты за каждый год, в этом случае нижние уровни структуры будут свернуты, но при необходимости их всегда можно развернуть вплоть до ежедневного анализа. Поскольку таблица двумерная, то возможны два направления свертки данных. Так, например, второе направление может отражать структуру предприятия: цеха, участки, группы, отдельного работника. Метод ShowLevels(RowLevels, ColumnLevels) объекта Outline позволяет показать структуру рабочего листа, где уровни детализации по строкам и столбцам задают параметры метода. Чаще всего, для проведения подобного анализа целесообразнее использовать сводные таблицы - объект PivotTablle.

AutoFiter - возвращает одноименный объект, позволяющий производить фильтрацию данных в специального рода Excel-запросах. О фильтрах и их использовании я расскажу подробно и приведу соответствующие примеры.

Свойства Next и Previous возвращают следующую и предыдущую страницу рабочей книги.

Коллекции HPageBreaks и VpageBreaks, возвращаемые при вызове одноименных свойств, используются для того, чтобы разбить нужным образом электронную таблицу на страницы, задавая горизонтальное и вертикальное разбиение. Это бывает важно при формировании отчетов и вывода результатов на печать. О них уже шла речь, при рассмотрении коллекции WorkSheets.

Объект PageSetup позволяет установить параметры страницы при выводе на печать.

Подводя итоги, заметим, что рабочий лист помимо того, что он представляет электронную таблицу ячеек, может содержать и другие элементы: диаграммы, рисунки, OLE -объекты. В нем могут быть также расположены сводные таблицы и таблицы, построенные на основе запросов к внешним источникам данных. Некоторые из ячеек рабочего листа снабжаются комментариями и имеют ссылки на внешние адреса. Ячейки и области данных могут иметь имена. Наконец, данные разрешается свернуть и отобразить структуру такого листа с нужной степенью подробности.

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

Полное рассмотрение всех свойств объекта Range заняло бы слишком много времени, и я рассмотрю лишь группу основных, на мой взгляд, свойств, специфических для объекта Range. Таблица 3.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 позволяют определить тип значения, хранимого в ячейке.

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

Основные терминальные свойства сведены в таблицу. Таблица 3.8. Терминальные свойства объекта ChartСвойствоОписание

ChartType	Позволяет прочесть или задать тип и формат стандартной диаграммы. Возможные значения задаются константами, которых около сотни. Напомним, что с каждым из 14 стандартных типов связано до 10 форматов.
AutoScaling	Булево свойство, имеющее значение True, когда трехмерная диаграмма автоматически масштабируется так, чтобы совпадать по размеру с двумерной. Свойство RightAngleAxes должно также иметь значение True.
BarShape	Мы ранее говорили, что двумерные гистограммы изображаются в виде прямоугольников. Для изображения трехмерных гистограмм обычно используются параллелепипеды, но можно применять и другие геометрические фигуры. Свойство BarShape задает вид используемой фигуры. Оно имеет следующие значения: xlBox, xlConeToMax, xlConeToPoint, xlCylinder, xlPyramidToMax, или xlPyramidToPoint. Использовать это свойство вряд ли стоит. Все эти фигуры- "изыски от лукавого".
DepthPercent, HeightPercent	Свойства применимы только к трехмерным диаграммам. Позволяют установить глубину и высоту диаграммы в процентах относительно ее ширины.
DisplayBlanksAs	Устанавливает способ интерпретации данных при встрече с пустой ячейкой. Следующие константы: xlNotPlotted, xlInterpolated, или xlZero задают три возможные стратегии- игнорировать ячейку, провести интерполяцию или считать нулем.
Elevation, Rotation, Perspective, RightAngleAxes	Можно попытаться повысить наглядность изображения диаграммы. Свойство Elevation задает возвышение (в градусах) точки, с которой Вы смотрите на диаграмму. Rotation задает поворот вокруг оси Z, а Perspective - перспективу. Булево свойство RightAngleAxes задает "угол зрения".
GapDepth	Задает в трехмерной диаграмме расстояние между рядами данных. Значение может быть в интервале от 0 до 500.
HasAxis, HasDataTable, HasLegend, HasTitle	Булевы свойства, показывающие, какие элементы диаграммы присутствуют в ней.
PlotBy	Имеет два значения: xlColumns и xlRows, указывающие столбцы или строки следует использовать как ряды данных.
PlotVisibleOnly	Булево свойство, имеющее значение True, если отображаются только данные из видимых ячеек. В противном случае диаграмма отображает все данные, включая скрытые ячейки.
ProtectContents, ProtectData, ProtectDrawingObjects, ProtectFormatting, ProtectGoalSeek, ProtectionMode, ProtectSelection	Булевы свойства, позволяющие установить защиту соответствующих элементов диаграммы. Часть из них имеет статус "только для чтения".
ShowWindow	Булево свойство, применяемое только к встроенным диаграммам. Имеет значение True, если диаграмма отображается в отдельном окне.
Visible	Напомним, имеет три значения: True, False и xlVeryHidden.

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

Терминальных свойств, как обычно, множество. Они проще, чем свойства, задаваемые объектами. Среди них достаточно много булевых свойств, позволяющих включать или отключать то или иное свойство рабочей книги. Я приведу сводку некоторых из этих свойств, позволяющую получить общее представление о том, как можно управлять характеристиками рабочей книги Excel с помощью этих свойств. Таблица 3.6. Терминальные свойства объекта WorkbookТерминальные свойстваНазначение свойстваНовые терминальные свойства объекта Workbook в Excel 2000

AcceptLabelsInFormula	Булево свойство со значением True, если метки могут использоваться в формулах рабочего листа. По умолчанию - True.
HasRoutingSlip	Булево свойство со значением True, если книга может быть направлена по сети другим участникам разработки документа.
Routed	Булево свойство со значением True, если документ был направлен следующему участнику разработки.
MultiUserEditing	Булево свойство со значением True, если книга открыта для разделяемого доступа.
AutoUpdateFrequency	Задает частоту (в минутах), с которой сделанные изменения передаются участникам разделяемого доступа. Если свойство имеет значение 0, то книга будет пересылаться только в момент ее сохранения
AutoUpdateSaveChanges	Булево свойство со значением True, если сделанные изменения автоматически пересылаются всем участникам разработки. Предыдущий параметр должен иметь значение в пределах от 5 до 1440, чтобы это свойство оказало эффект.
ListChangesOnNewSheet	Булево свойство со значением True, если сделанные изменения показываются на отдельной странице при разделенном доступе
KeepChangeHistory	Булево свойство со значением True, если при разделенном доступе сохраняется история сделанных изменений.
ChangeHistoryDuration	Устанавливает число дней, в течение которых сохраняются изменения в их истории. Старые изменения, срок которых превышает заданную установку, из истории удаляются
CreateBackup	Булево свойство со значением True, если при сохранении книги создается ее резервная копия.
CodeName	Рабочие книги, листы и другие объекты Excel имеют два имени - собственное и кодовое. В момент создания объекта они совпадают, например "Лист1", но затем каждое из них может быть независимо изменено. Кодовое имя может быть изменено только вручную в окне свойств. Программно оно может быть использовано только для чтения. Важно то, что кодовое имя можно использовать для непосредственного именования объекта, что сокращает цепочку вызовов. Так непосредственно можно обратиться к объекту Лист1.Range(myRange)
FullName	Полное имя рабочей книги, заданное в виде строки, включающее путь к файлу, хранящему книгу. Имеет статус "только для чтения".
FileFormat	Свойство имеет статус "только для чтения" и возвращает константу, задающую формат файла и/или тип рабочей книги
HasPassword	Булево свойство со значением True, если документ имеет пароль защиты
Saved	Булево свойство со значением True, если не делалось никаких изменений с момента последнего сохранения документа.
WriteReserved	Булево свойство со значением True, если документ закрыт для записи.
EnvelopeVisible	Новое в Excel 2000 терминальное булево свойство, при включении которого появляется панель для отправки электронной почты и заголовок отправляемого сообщения.
VBASigned	Новое в Excel 2000 терминальное булево свойство, которое показывает, имеет ли программный проект данной книги цифровую подпись. Свойство имеет статус "только для чтения".

Приведу две простые процедуры, которые демонстрируют использование некоторых терминальных свойств. Первая из этих процедур выводит на печать имена рабочей книги и путь к ней: Public Sub AllNames() 'Печать имен документа With ThisWorkbook Debug.Print "Свойство Name - ", .Name Debug.Print "Свойство CodeName - ", .CodeName Debug.Print "Свойство FullName - ", .FullName Debug.Print "Свойство Path - ", .Path End With End Sub

Вот как выглядят результаты в окне отладки, полученные в результате работы этой процедуры: Свойство Name - BookOne.xls Свойство CodeName - ЭтаКнига Свойство FullName - E:\O2000\DsCd\Ch11\BookOne.xls Свойство Path - E:\O2000\DsCd\Ch11

Следующая процедура позволяет по желанию пользователя включить или отключить панель для отсылки почтового сообщения, используя новое свойство EnvelopeVisible: Public Sub EnvelopeOn() 'Включение и выключение панели и заголовка почтового сообщения Dim Answer As Long Answer = MsgBox("Включить панель отправки почтового сообщения?", _ vbYesNo) If Answer = vbYes Then ThisWorkbook.EnvelopeVisible = True Else ThisWorkbook.EnvelopeVisible = False End If End Sub

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

Перейдем теперь к рассмотрению основных терминальных свойств объекта Worksheet. Представим их, как обычно, таблицей: Таблица 3.7. Терминальные свойства объекта WorkSheetТерминальные свойстваНазначение свойства

CodeName, Name, Index	Свойство CodeName имеет статус только для чтения и позволяет установить кодовое имя рабочего листа. Мы уже говорили, что рабочие книги, рабочие листы и листы диаграмм наряду с именем имеют и кодовое имя. Свойство Name позволяет задать или изменить имя рабочего листа. Это свойство, также как и свойство Index, имеют многие объекты. Index позволяет по имени объекта получить его порядковый номер в коллекции.
ConsolidationFunction, ConsolidationOptions, ConsolidationSources	Excel имеет разные способы агрегирования данных. Мы уже говорили об объекте Outline, позволяющем структурировать данные, представляя их с разной степенью детализации. Другим средством являются сводные таблицы. Еще одну возможность объединения данных дает их консолидация. Как правило, консолидируются однотипные данные, построенные, например, на основе единого шаблона. Можно, например, консолидировать данные, представляющие результаты работы однотипных подразделений. Что реально скрывается за термином "консолидация" определяет функция консолидации - чаще всего это функция Sum, проводящая обычное суммирование. Но это может быть и нахождение среднего или минимального (максимального) значения.

Свойство ConsolidationFunction предназначенное только для чтения возвращает константу, задающую код функции консолидации: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, или xlVarP.
Свойство ConsolidationSources возвращает массив строк, содержащий имена листов, служивших источниками для консолидации данных. Свойство ConsolidationOptions возвращает трехэлементный массив булевых переменных, каждая из которых имеет значение True, если одна из трех соответствующих опций установлена. Об опциях и некоторых подробностях консолидации мы еще поговорим при рассмотрении метода Consolidate, которым обладает объект Range.ProtectContents, ProtectDrawingObjects, ProtectionMode, ProtectScenariosДля защиты книги или ее листа от случайных изменений используется метод Protect. В момент его вызова можно установить, какие именно элементы будут защищены. Имеющие статус " только для чтения" булевы свойства: ProtectContents, ProtectDrawingObjects и ProtectScenarios имеют значение True, если соответственно защищены такие элементы рабочего листа, как ячейки, графические объекты, сценарии. Если свойство ProtectionMode имеет значение True, то это означает, что макросы, представляющие часть пользовательского интерфейса, доступны для изменений. Для того чтобы это свойство было включено, необходимо, чтобы при вызове метода Protect его параметр UserInterfaceOnly был задан и получил значение True.EnableAutoFilter, EnableCalculation, EnableOutlining, EnablePivotTable, EnableSelectionБулевы свойства Enable включают или выключают ту или иную опцию. EnableCalculation включает автоматическое вычисление формул, всякий раз, когда возникает необходимость, например, изменились данные.
EnableOutlining включает показ символов структуризации на защищенном листе при условии включения параметра UserInterfaceOnly.
EnablePivotTable как и предыдущий параметр включает элементы управления сводной таблицей на защищенном листе.
EnableAutoFilter включает стрелки автофильтрации на защищенном листе.EnableSelection не является булевым параметром. В его задачу входит включить или выключить доступ к тем или иным ячейкам защищенного листа. Он имеет три возможных значения: xlNoRestrictions, xlNoSelection, or xlUnlockedCells. В первом случае для выбора доступны все ячейки, во втором - ни одна, в третьем - только "открытые" ячейки, у которых свойство Locked имеет значение False.ScrollAreaВозвращает или устанавливает строку, задающую область рабочего листа, где разрешена прокрутка. Ячейки, расположенные вне этого диапазона не могут быть выбраны. Вот пример: Public Sub StrictScrolling() 'Ограничение области скроллинга! Dim S As String S = "A1:G20" With ThisWorkbook.Worksheets(2) 'Установка области прокрутки .ScrollArea = S '.ScrollArea = "" 'Попытка выделить ячейку вне области .Range("B21").Select Selection.Value = 77 End With End SubЗаметьте, программно мы смогли выделить и записать значение в ячейку B21, находящуюся вне области прокрутки, но вручную ни эта ячейка, ни другие не будут доступны. В дальнейшем можно переопределить область прокрутки и, чтобы сделать доступными все ячейки, нужно задать пустую строку в качестве значения ScrollArea.
Замечу, что в отличие от предыдущей версии значение ScrollArea теперь можно задавать не только константой, но и переменной, как в нашем примере.StandardHeight, StandardWidthПервое из этих свойств возвращает стандартную (по умолчанию) высоту всех строк, второе - ширину всех столбцов. Разница между ними состоит в том, что первое имеет статус "только для чтения". Вы можете задать ширину всех столбцов, принимаемую по умолчанию. Для всех строк задать высоту нельзя, но это можно делать для каждой отдельно взятой строки, используя ее свойство - RowHeight.TransitionExpEval, TransitionFormEntryЭти булевы свойства обеспечивают совместимость с Lotus 1-2-3. Если они имеют значение True, то используются правила вычислений выражений и формулы Lotus 1-2-3.VisibleВ общем случае булево свойство, имеющее значение True, если объект виден. Но для рабочего листа имеется третье значение - xlSheetVeryHidden. В этом случае пользователь не сумеет вручную добраться до "очень" скрытого листа. Это можно будет сделать только программно, изменив его значение на True или False.

Терминальные свойства

Терминальных свойств много, и понятно почему. Приложение Excel, как и другие приложения Office 2000, могут быть настроены пользователем по своему усмотрению. Эту настройку можно выполнять вручную, а можно и программно. Настройка вручную большей частью проводится из меню Сервис | Параметры, используя возможности, предоставляемые различными вкладками в открывающемся окне параметров. Для программной настройки используются терминальные свойства, - в этом их основное назначение. Естественно, я не буду останавливаться на всех свойствах, - они просты. В ниже приведенном обзоре представлено выборочное описание некоторых групп терминальных свойств:

Группа свойств, задающих свойства приложения по умолчанию, - DefaultFilePath, DefaultSaveFormat, DefaultSheetDirection, - путь по умолчанию, формат по умолчанию, направление просмотра текста (слева направо или справа налево), задаваемое для некоторых языков. К этим же свойствам примыкает и ранее упоминавшееся свойство DefaultWebOptions.

Группа булевых свойств, позволяющих включить или выключить отображение на экране тех или иных элементов приложения - DisplayAlerts, DisplayCommentIndicator, DisplayFormulaBar, DisplayStatusBar и другие Display-свойства. Первое из этих свойств позволяет управлять выдачей на экран некоторых сообщений в процессе работы макросов, второе - отображать специальный индикатор при показе комментариев. Более часто приходится использовать управление показом панелей формул и статуса. Особенно часто приходится использовать эти свойства, когда документ Excel используется в специальных целях, например, при отображении различных бланков, когда внешний вид документа ничем не напоминает привычную электронную таблицу. Замечу, что используемое в этих случаях свойство DisplayGridLines, позволяющее отключать сетку, принадлежит объекту Windows, а не объекту Application.

Группа булевых свойств, позволяющих включить или выключить те или иные свойства - EnableAnimations, EnableAutoComplete, EnableCancelKey, EnableEvents, EnableSound. Первое из этих свойств позволяет управлять анимацией при добавлении или удалении строк и столбцов рабочего листа, второе - автозаполнением ячеек таблицы. Свойство EnableCancelKey не является булевым, оно принимает значения, заданные соответствующим перечислением, и позволяет управлять процессом прерывания программы при нажатии комбинации клавиш Ctrl+Break. Значение xlInterrupt, принятое по умолчанию, позволяет прервать выполнение макроса и перейти в режим отладки, где возможно пошаговое выполнение. Однако с помощью этого свойства можно задать разные режимы, как, например, передачу управления обработчику ошибок в момент прерывания. Пользоваться этим свойством следует осторожно, поскольку при зацикливании может возникнуть ситуация, когда нельзя будет прервать программу, не применяя грубых способов. Свойство EnableEvents позвол яет управлять включением событий объекта Application, а свойство EnableSound управляет включением звука в процессе работы приложений Office 2000.

Группа свойств, управляющих размерами главного окна приложения Excel - Height, Width, Left, Top, задающие высоту, ширину окна и координаты верхнего левого угла окна.

Многие другие свойства, позволяющие управлять курсором, скроллингом, характеристиками пользователя и многими другими параметрами так или иначе, характеризующими приложение Excel.

Внешние ссылки, Web-запросы и событие Change

Согласно документации, событие Change возникает при изменениях значений в ячейках рабочих страниц, производимых пользователем или внешней ссылкой. Это утверждение требует ряда уточнений.

Прежде всего, уточню, речь идет не об изменении значений, а о записи значения в ячейку рабочей страницы. Даже если значение не изменится, при записи все равно возникнет событие Change.

Изменения значений в ячейках, произошедшие в результате вычислений по формулам, связанных с этими ячейками, не приводят к возникновению события Change.

Напомню, что внешняя ссылка - это ссылка из одной рабочей книги на ячейку или, в общем случае, область другой рабочей книги Excel. Поскольку такие ссылки встречаются в формулах, то они не могут приводить к появлению события Change в том документе, где размещена такая внешняя ссылка.

Не только действия пользователя приводят к возникновению события Change - программные изменения состояния ячеек также являются причиной возбуждения этого события.

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

А теперь, в подтверждение сказанного приведу некоторые примеры:

Хочу обратить Ваше внимание на то, что при написании обработчика события Change объекта Application в качестве журнала изменений я выбрал список, помещенный в пользовательскую форму. Скажу честно, вначале я попытался использовать для этой цели отдельную страницу в одной из книг Excel, но эта попытка оказалась безуспешной и привела к зацикливанию. Дело объясняется тем, что, как Вы понимаете, не только действия пользователя приводят к возникновению события Change. Когда обработчик этого события записывает информацию о возникшем событии в журнал изменений - на одну из страниц рабочей книги, то автоматически возникает событие Change, обработка которого приведет к возникновению нового события и так далее до бесконечности. Этот пример подтверждает сделанный вывод - не только действия пользователя по изменению значений в ячейках рабочей книги приводят к возникновению события Change, - но и программное изменение состояния ячеек, в том числе производимое самой системой, также вызывает событие Change. Замечу в заключение, что вместо формы, для журнала изменений можно было бы выбрать документ Word, таблицу Access, файл, но только не страницу рабочей книги. Обратите также внимание, что для обработчика события Change, связанного с рабочей страницей - объектом WorkSheet, для журнала изменений можно использовать рабочую страницу, но, учтите, только страницу, не заданную данным объектом WorkSheet.

А теперь рассмотрим пример изменений значений в ячейках рабочей страницы, не приводящих к возникновению события Change. В книге в ячейках G1 и H1 на странице Лист1, для которой, как Вы помните, предусмотрен свой обработчик события Change, я записал соответственно формулы: = [BookOne.xls]Лист1!A1 = Лист2!A1 + Лист3!A2 Первая из этих формул задает внешнюю ссылку, вторая при вычислениях ссылается на ячейки той же книги, но, взятых из других листов. Если в указанных ячейках произойдет изменение значений, то произойдет изменение значений и в ячейках G1 и H1 на Листе1 книги , но событие Change для этой страницы не возникнет. Таким образом, ни внутренние ссылки, ни внешние ссылки, стоящие в формулах, не приводят к появлению этого события.

Внешние ссылки можно рассматривать и в более широком аспекте. Например, как внешние ссылки можно рассматривать запросы к источникам данным, выполняемые на рабочих страницах, в том числе Web-запросы, позволяющие получать данные из Интернет. Возникает ли событие Change, когда происходит обновление данных на рабочей странице, поступающих из Интернет при выполнении Web-запроса? Ответ на этот вопрос отрицателен. Событие Change не позволяет следить за обновлениями данных, приходящих из Интернет. В следующем примере я решил проследить за известными биржевыми показателями Доу-Джонса и другими. С этой целью я построил соответствующий Web-запрос, в результате которого с известного сайта , адрес которого: , таблица с нужными показателями копируется в рабочую страницу Excel. Вот как выглядит эта страница после получения данных:

Рис. 3.5.  Таблица, копируемая из Интернет в рабочую страницу Excel При первоначальном выполнении запроса событие Change возникает лишь на предварительном этапе, когда система пишет некоторое сообщение в указанную область рабочей страницы. Когда же копируются сами данные и при обновлении этих данных событие Change не возникает.

"Знакомые" методы

Вначале рассмотрим методы объекта Worksheet, действие которых так или иначе уже было описано. Это позволит нам избежать некоторых подробностей:

Activate - активизирует рабочий лист.

Delete - удаляет объект. Этот метод, также как и нижеследующие методы Copy, Move, Select и другие рассматривались при описании работы с коллекцией листов. Конечно, именно при работе с отдельным листом эти методы чаще всего применяются.

Copy - имеет два варианта. В первом варианте используется без параметров, копируя содержимое рабочего листа в буфер. Во втором варианте Copy(Before, After) создает копию листа, помещая ее перед или после листа, вызвавшего метод. Позиция вставляемого листа задается параметрами метода. Понятно, что только один из двух параметров - Before или After - может быть задан.

Move(Before, After) - перемещает рабочий лист, изменяя порядок следования листов в рабочей книге.

Paste(Destination, Link) -помещает содержимое буфера на рабочий лист. Возможный параметр Destination указывается только тогда, когда содержимое буфера представляет некоторый диапазон. В этом случае параметр задается объектом Range, определяющим диапазон, в который будет помещено содержимое буфера. Второй возможный булев параметр Link получает значение True в случае, когда устанавливается связь с источником данных. По умолчанию имеет значение False. Одновременно можно задать только один из этих параметров.

PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel) - также помещает содержимое буфера в область выделения рабочего листа. Разница состоит в том, что метод применяется тогда, когда содержимое буфера хранится в специальном формате, отличном от формата Excel. Чаще всего метод применяется для размещения объектов других приложений. Параметр Format в виде строки задает формат объекта, хранящегося в буфере. Параметр Link имеет тот же смысл, что и в предыдущем случае. Остальная группа параметров используется тогда, когда объект "приклеивается" в виде значка. В этом случае параметр DisplayAsIcon имеет значение True, параметр IconFileName задает имя файла, содержащего значки, IconIndex - индекс значка в файле, IconLabel - текст, связанный со значком. Обратите внимание, что поскольку объект или значок помещается в область выделения, то предварительно такая выделенная область Xі должна быть установлена. Рассмотрим пример помещения текста, взятого из буфера, в ячейку нашей тестовой книги . Сам текст был создан в приложении Word, - я поместил в буфер начало этого абзаца.

Public Sub PasteTextFromWord() ' В приложении Word некоторый текст документа 'был сохранен в буфере. В данной процедуре 'текст из буфера помещается в ячейку Excel. With ThisWorkbook.Worksheets(2) 'Установка области выделения .Range("B25").Select .PasteSpecial Format:="Microsoft Word 9.0 Document Object" .Range("B35").Select .PasteSpecial Format:="Microsoft Word 9.0 Document Object", _ DisplayAsIcon:=True End With End Sub

Вот как выглядит рабочий лист Excel после вставки из буфера текста, скопированного в приложении Word.


Рис. 3.6.  Рабочий лист Excel с текстом документа Word при копировании из буфера

Продолжим рассмотрение методов:

Select(Replace) - создает объект Selection. Возможный булев параметр Replace имеет значение True, если новый объект заменяет ранее существовавшее выделение, и False, когда происходит расширение области выделения, так, чтобы она охватывала и новый объект.

SaveAs - сохраняет изменения, сделанные на рабочем листе в отдельном файле. Мы не будем перечислять все возможные параметры этого метода.

PrintPreview и PrintOut - позволяют осуществит предварительный просмотр и печать содержимого рабочей книги

CheckSpelling - позволяет проверить правописание содержимого рабочего листа. Имеет ряд возможных параметров, позволяющих задать ряд опций, например, словарь, предоставленный пользователем.

Protect и Unprotect - позволяют защитить лист от несанкционированных изменений и снять эту защиту. Первый из методов имеет ряд параметров- Protect(Password, DrawingObjects, Contents, Scenarios, UserIntefaceOnly). Параметр Password задает пароль, а остальные являются булевыми и позволяют включить или отключить защиту той или иной части листа. Значение True у последнего параметра говорит о том, что на защищенном листе пользовательский интерфейс будет защищен, а макросы могут изменяться. Если этот параметр опущен, то и макросы будут защищены. По умолчанию защищены ячейки листа (Contents) и сценарии, но не графические объекты.



    Базы данных: Разработка - Управление - Excel

• Базы данных
  
• Разработка баз данных
  
• СУБД и базы данных
  
• Управление базами данных
  
• Классика баз данных
  
  
• Софт для создания базы данных
  
• SQL
  
• Access
  
• FoxProо
  
• Расширенная оптимизация подзапросов в Oracle
  
  
• Informix
  
• Линтер
  
• Postgres
  
• СУБД DB2
  
• InterBase
  
  
• Excel
  
• Таблицы Excel
  
• Справка Excel
  
• Программирование в Excel
  
• Деньги в Excel
  
  
• Задачи Excel