Программирование событий excel

Программирование событий excel

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

Выбор правильного объекта

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

Хотя некоторые события Excel распознают лишь определенные объекты, большинство из них образует иерархию от объекта Worksheet до объектов Workbook и Application. Например, изменения, внесенные в рабочий лист, инициируют событие Change (Изменение) объекта Worksheet, который в свою очередь инициирует событие Sheet Change (Изменение листа) для объектов Workbook и Application.

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

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

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

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

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

1. Откройте окно редактирования кода для объекта.

2. Выберите объект, выбрав его имя из раскрывающегося списка Объект в левом верхнем углу окна.

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

4. При этом в окне появляется каркас выбранной процедуры сообщения.

Кроме написания самого кода, единственным сложным моментом может быть вынос окна редактирования кода на первое место (п. 1 в приведенном выше списке). Для объектов Worksheet, Workbook и Chart, занимающих отдельный лист, никаких особенностей нет- необходимо просто выбрать объект в окне обозревателя и щелкнуть на кнопке View Code. Объекты представляются в алфавитном порядке, а их имена можно изменять в окне Options.

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

Если необходимо, чтобы код реагировал на действия, производимые пользователем с рабочим листом, потребуются такие инструменты, как свойства Change, Calculate и SelectionChange (для объектов Worksheet) и соответствующие события SheetCnange, Sheet Calculate и SheetselectionChange (для объектов Workbook и Application). Для запуска пользовательских процедур в то время, когда сами рабочие листы или диаграммы активизированы или не активизированы, используются события Activate и DeActivate.

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

Private Sub Worksheet_Change(ByVal Target As Range)

For Each oCell In Target

If oCell> 4 And oCell < 11 Then

With oCell.Font

.Bold = True

.Size = 16

.Color = RGB(0, 255, 0) ‘ Зеленый

End With

End If

Next oCell

End Sub

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

For Each ... Next; в таком варианте процедура применима как отдельной ячейке, так и к нескольким ячейкам, что и демонстрирует приведенный выше пример.

В действительности, события Change и Sheet Change могут активизироваться даже тогда, когда значение не изменялось. Данные события готовы произойти, когда пользователь начинает редактирование ячейки (после щелчка в панели формул или нажатия клавиши <F2>), даже если сразу после этого он прекратил редактирование, не внеся никаких изменений (нажав клавишу <Enter>, щелкнув на кнопке Ввод или на рабочем листе). Событие не активизируется, если пользователь прекращает редактирование нажатием клавиши <Esc> или щелчком на кнопке Отмена.

Событие Calculate распознаваемое как объектом Worksheet, так и Chart, происходит при каждом обновлении программой Excel рабочего листа или диаграммы.

Событие Sheet Calculate для объектов Workbook и Application происходит в ответ. Если средство автоматического вычисления включено, данное событие активизируется, как только изменяется значение любой ячейки, т.е. происходит в тандеме с событием Change. Когда средство пересчета включено в ручном режиме, событие Calculate происходит лишь тогда, когда пользователь инициализирует пересчет нажатием клавиши <F9>.

Процедуры событий для событий Calculate и SheetCalculate используются для изменения рабочего листа в соответствии с результатами вычислений. Например, если известно, что пересчет может изменить элементы упорядоченного списка, целесообразно использовать процедуру события Worksheet _Calculate для упорядочения списка после проведения вычислений. Поскольку данные процедуры не сообщают, какая из ячеек изменялась в результате вычислений, необходимо поместить в код адреса ячеек, которые требуется изменить.

При каждом перемещении активной ячейки, а также при расширении или сжатии выделения Excel вызывает событие SelectionChange для Worksheet. Параллельно происходит событие Sheet SelectionChange для объектов Workbook и Application. Процедуры событий для данных событий можно использовать для обратной связи с текущим выделением. В приведенном ниже примере событие Selection Change используется для отображения в левой верхней ячейке текущего листа адреса активной ячейки, а также для помещения имени рабочего листа и адреса выделения в строку состояния. Обратите внимание на то, как аргумент Sh позволяет идентифицировать и вести работу с текущим листом:

Private Sub Workbook Sheet SelectionChange (ByVal Sh As Object, _

ByVal Tarqet As Excel.Range)

Sh.Range("Al") = ActiveCell.Address

Application.StatusBar = Sh.Name & " : " & Target.Address

End Sub

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

Private Sub Worksheet_SelectionCharge (ByVal Target As Excel.Range)

If Target.Address = "$3$2" Then

MsgBox "Вы нашли нужную ячейку!"

End If

End Sub

Поскольку объекты диаграмм Excel способны распознавать большое число событий, многие из которых связаны с мышкой, их можно считать большим элементом управления ActiveX, помещенным в рабочей книге. Диаграммы распознают события Activate, DeActivate и Calculate. Вот еще некоторые события, для которых возможно написание кодов.

* DragOver и DragPlot. Происходят, когда ячейки перемешаются над внедренной диаграммой или опускаются на нее соответственно.

* MouseDown. MouseUp и MouseMove. Происходят в ответ на действия мыши.

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

* Series Change. Происходит, когда пользователь изменяет значение в диаграмме через саму диаграмму (а не изменяя значения, содержащиеся в листе).