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

2006 г.

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

Биллиг Владимир Арнольдович
Интернет-Университет Информационных Технологий, 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

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

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

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

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

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

Теперь я хочу рассмотреть еще несколько важных методов объекта 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-объект, встроенный в рабочий лист.

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

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

Назад Оглавление Вперёд