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

Вначале рассмотрим методы объекта 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і должна быть установлена. Рассмотрим пример помещения текста, взятого из буфера, в ячейку нашей тестовой книги BookOne. Сам текст был создан в приложении 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. У рабочего листа их не так и много. Вот эти методы:

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

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

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

Приведу текст программы, рисующей стрелки:

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