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

13.1. Особенности программирования для MS Excel

Microsoft Office Excel - это популярные электронные таблицы. Обычно, программируя для этой программы, преследуют такие цели:

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

Для выполнения большинства операций в MS Excel применяются следующие объекты.

Давайте начнем рассмотрение объектной модели MS Excel с объекта Application.

13.2. Объект Application

13-01-Excel Application.xlsm - пример к п. 13.2.

Принципы работы с объектом Application в MS Excel очень похожи на таковые в MS Word.

Рассмотрим пример. Откроем MS Excel, добавим на рабочий лист кнопку, добавим в обработчик щелчка по кнопке такой код (листинг 13.1.).

MsgBox Excel.Application.Name

После выполнения программы в окне сообщения отобразится имя приложения - в данном случае - Microsoft Excel. Очевидно, что свойство Name объекта Application возвращает имя приложения.

Теперь рассмотрим наиболее важные методы и свойства Application. Некоторые из них похожи на таковые в MS Word. Например, метод Quit точно так же, как в Word, закрывает приложение, свойство Visible отвечает за видимость окна программы и т.д.

13.3. Методы Application

13.3.1. Calculate - принудительный пересчет

Этот метод, вызываемый для объекта Application, позволяет пересчитать все открытия книги. Его же можно вызывать для отдельных книг (объект Workbook ) листов ( Worksheet ), ячеек и их диапазонов ( Range ). Например, код из листинга 13.2. позволяет пересчитать все открытые книги.

Application.Calculate

13.3.2. GoTo - переход в ячейку

13-02-Excel GoTo.xlsm - пример к п. 13.3.2.

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

Полный вызов метода выглядит так:

Goto(Reference, Scroll)

В качестве параметра Reference используется ссылка на ячейку или диапазон ячеек, которые должны быть выделены после вызова метода. Так же здесь может быть использовано имя макроса - тогда он будет запущен.

Параметр Scroll отвечает за "перемотку" листа Excel к выделенным ячейкам - так, чтобы левый верхний угол выделения совпадал бы с левым верхним углом отображаемой области листа. Если Scroll установлен в True - лист перематывается, если в False - нет.

Например, такой вызов (листинг 13.3.) позволяет выделить ячейку H500 на активном листе.

Application.Goto _
    Reference:=ActiveSheet.Range("H500"), _
    Scroll:=True

Как видите, обращение к активному листу очень напоминает обращение к активному документу в MS Word. Обратите внимание на то, что мы используем полное обращение к методу - Application.GoTo - как вы знаете, обычно свойства и методы объекта Application можно использовать в коде, не указывая этого объекта. Однако, если в этом случае не указать Application, то вместо метода GoTo программа попытается выполнить оператор безусловного перехода GoTo.

13.3.3. SendKeys - имитация нажатий на клавиши клавиатуры

13-03-Excel SendKeys.xlsm - пример к п. 13.3.3.

Очень интересный метод - позволяет передавать в активное окно приложения нажатия клавиш. Полный вызов метода выглядит так:

SendKeys(Keys, Wait)

Параметр Keys позволяет задавать клавиши, нажатия которых будут переданы приложению. Метод поддерживает эмуляцию как алфавитно-цифровых, так и управляющих клавиш, для которых применяется специальная кодировка. Алфавитно-цифровые клавиши указываются при вызове в своем обычном виде Например, для передачи символа "Ф" нужно указать его при вызове метода и т.д.

Чтобы передать приложению нажатия клавиши Backspace - используйте код {BS}. Для передачи нажатия кнопки Enter используйте значок ~ (тильда), для клавиши Del - код {DEL}.

Для передачи приложению нажатий клавиш Shift, Ctrl или Alt, можно воспользоваться следующими кодами этих клавиш:

Параметр Wait может принимать значения True или False. Если он будет установлен в True - макрос подождет, пока приложение обработает введенные с "клавитуры" данные, после чего продолжит выполнение. Если в False - макрос ждать не будет.

Рассмотрим пример. С помощью метода SendKeys введем в ячейку A1 текст "Сейчас закрою программу!", после чего попытаемся закрыть программу, "нажав" сначала Alt, потом Ф (для открытия главного меню Office), и потом - Ы - для выхода из программы. Подразумевается, что при запуске макроса (листинг 13.4.) указанная ячейка активна - в ней находится рамка выделения.

Application.SendKeys ("Сейчас закрою программу!")
    Application.SendKeys ("%ФЫ")

После выполнения этого кода вы увидите окно со стандартным вопросом о сохранении документа.

13.3.4. Wait - пауза при выполнении макроса

13-04-Excel Wait.xlsm - пример к п. 13.3.4.

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

При вызове этого метода указывается время, на которое должно быть приостановлено выполнение макроса. После того, как истекает время, заданное при вызове метода, он возвращает True и выполнение программы продолжается. Например, в листинге 13.5. так можно остановить выполнение прораммы на 7 секуд, после чего - вывести сообщение.

If Application.Wait(Now + TimeValue("0:00:7")) Then
    MsgBox "Семь секунд прошло!"
End If

Здесь мы получаем значение типа Date с помощью функции TimeValue. После того, как пройдут 7 секунд, выполнение макроса возобновится, метод возвратит True, будет выполнено условие и выведено сообщение.

13.4. Свойства Application

13.4.1. ActiveCell, ActiveChart, ActivePrinter, ActiveSheet, ActiveWindow, ActiveWorkbook - активные объекты

13-05-Excel Active.xlsm - пример к п. 13.4.1.

Cвойства, имена которых начинаются с Active, позволяют обращаться к различным активным объектам.

ActiveCell возвращает объект типа Range, который представляет собой активную (выделенную) ячейку рабочего листа, отображаемого в данный момент на экране. Если при вызове этого свойства на экране нет открытого листа - произойдет ошибка.

Например, такой код (листинг 13.6.) выводит данные из активной ячейки в окне сообщения, после чего предлагает пользователю ввести в эту ячейку новые данные с помощью окна ввода.

MsgBox ("В ячейке с именем " + Application.ActiveCell.Address + " хранится значение " +   Application.ActiveCell.Value)
ActiveCell.Value = InputBox("Введите новое значение для ячейки " + ActiveCell.Address)

Очевидно, что свойство Value объекта ActiveCell содержит данные, которые записаны в ячейку, а свойство Address - адрес ячейки.

Остальные свойства этой группы предназначены для обращения к следующим объектам:

MsgBox Application.ActiveSheet.Name
ActiveWindow - к активному окну.
ActiveWorkbook - к активной рабочей книге.

13.4.2. Cells, Columns, Rows, Sheets, Workbooks, Worksheets, Names - наборы объектов и коллекции

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

13.4.3. Range - ячейка или группа ячеек

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

13.4.4. ScreenUpdating - обновление экрана

13-06-Application ScreenUpdating.xlsm - пример к п. 13.4.4.

Позволяет включать (присвоением свойству True ) и отключать (присвоением False ) обновление экрана. Имеет смысл отключить обновление экрана перед теми частями программы, которые интенсивно пользуются данными на листе. Благодаря тому, что системные ресурсы не будут тратиться на обновление экрана, программа будет работать быстрее. Этот метод весьма актуален, так как MS Excel часто используют для проведения ресурсоемких расчетов.

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

Например, ниже (листинг 13.8.) приведен код, который два раза повторяет процедуру 100-кратного вывода на экран 400 целых случайных чисел и выводит время, требующееся для выполнения этих действий с обновлением экрана и без него.

'Массив для значений времени
    Dim WorkTime(2)
    'Время начала теста
    Dim StartTime
    'Время окончания теста
    Dim StopTime
    'Включаем обновление
    Application.ScreenUpdating = True
    For i = 1 To 2
        'Во втором проходе цикла
        'выключим обновление
        If i = 2 Then _
            Application.ScreenUpdating = False
        End If
        'Запишем текущее время
        StartTime = Time
        'Перейдем на лист для теста
        Worksheets("Тест скорости").Activate
        'Выведем 100 раз целые случайные
        'числа в область 20х20
        For y = 1 To 100
            For p = 1 To 20
                For j = 1 To 20
                    ActiveSheet.Cells(p, j) = _
                        Int(Rnd * 100)
                Next j
            Next p
        Next y
        'Запишем время окончания
        StopTime = Time
        'Для корректного представления
        'в виде секунд
        WorkTime(i) = _
            (StopTime - StartTime) * 24 * 60 * 60
    Next i
    Application.ScreenUpdating = True
    MsgBox "Время выполнения программы."  Chr(13) + _
        "При включенном обновлении: "  _
        Round(WorkTime(1),2)  " сек."  Chr(13)  _
        "При выключенном обновлении: "  _
        Round(WorkTime(2),2)  " сек."

13.4.5. Selection - ссылка на выделенный объект

Это очень важное свойство возвращает ссылку на выделенный объект. Чаще всего это - ячейка или группа ячеек. Например, это свойство удобно использовать при работе с выделенным диапазоном ячеек (или отдельной выделенной ячейкой). Ниже мы коснемся его подробнее.

13.4.6. WorksheetFunction - формулы Excel в коде VBA

Возвращает объект WorksheetFunction, методы которого представляют собой формулы Excel, которые можно использовать в коде VBA. Использование этого свойства позволяет облегчить выполнение сложных расчетов.

13.5. События Application

13-07-Excel Application Events.xlsm - пример к п. 13.5.

Объект Excel.Application поддерживает множество событий. Работа с ними аналогична работе с событиями Word.Application, которыми мы занимались в соответствующем разделе предыдущей главы.

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

Создайте новый модуль класса. Добавьте в него объявление объекта типа Excel.Application с событиями (листинг 13.9.).

Public WithEvents obj_ExApp As Excel.Application

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

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

Итак, вот (листинг 13.10.) код процедуры, который связывает объект созданного нами класса AppEvents с приложением:

Dim obj_ExcelAppEv As New AppEvents
Sub EventsInit()
    Set obj_ExcelAppEv.obj_ExApp = Excel.Application
End Sub

А вот (листинг 13.11.) полный код модуля класса AppEvents с объявлением объектной переменной и обработчиком события.

Public WithEvents obj_ExApp As Excel.Application
Private Sub obj_ExApp_NewWorkbook(ByVal Wb As Workbook)
    'Выполняется при создании новой книги
    MsgBox "Вы создали новую книгу"
End Sub

13.6. Выводы

В этой лекции мы обсудили методы, свойства и события объекта Application приложения Microsoft Excel. Можно заметить, что некоторые из этих методов прямо указывают на то, что Excel - это приложение, рассчитанное на проведение достаточно серьезных расчетов. В следующей лекции мы рассмотрим особенности работы с документами MS Excel.