Пример обработки события 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 …
( задание лабораторной работы – заполнить колонки требуемыми данными…)
…
…
…
End With End Sub
При
каждом изменении на страницах любой из
открытых рабочих книг, с которыми
работает пользователь, соответствующая
запись будет добавлена в список формы
JournalForm. Заметьте, параметры
Sh и Target,
переданные обработчику события Change,
позволяют однозначно задать всю
информацию, требуемую для журнала.
Взгляните, как выглядит наш журнал в
процессе работыс ним:
Рис.4
Журнал изменений, созданный обработчиком
события Change
В журнале нашли отражение почти все изменения, происшедшие со страницами рабочих книг – с разными ячейками, разными страницами, разными рабочими книгами. Почему не все изменения были зафиксированы в журнале, я скажу чуть позже, специально остановившись более подробно на объяснении этой ситуации. А сейчас давайте рассмотрим специальный обработчик этого же события, предусмотренный для страницы с именем «Лист1» книги BookThree. Вот его текст:
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. А теперь давайте поговорим о нюансах.
Внешние ссылки, Web-запросы и событие Change
Согласно документации, событие Change возникает при изменениях значений в ячейках рабочих страниц, производимых пользователем или внешней ссылкой. Это утверждение требует ряда уточнений.
Прежде всего, уточню, речь идет не об изменении значений, а о записи значения в ячейку рабочей страницы. Даже если значение не изменится, при записи все равно возникнет событие Change.
Изменения значений в ячейках, произошедшие в результате вычислений по формулам, связанных с этими ячейками, не приводят к возникновению события Change.
Напомню, что внешняя ссылка – это ссылка из одной рабочей книги на ячейку или, в общем случае, область другой рабочей книги Excel. Поскольку такие ссылки встречаются в формулах, то они не могут приводить к появлению события Change в том документе, где размещена такая внешняя ссылка.
Не только действия пользователя приводят к возникновению события Change - программные изменения состояния ячеек также являются причиной возбуждения этого события.
Подводя итог, уточню формулировку, сделанную в документации. Событие Change возникает при записи в ячейки рабочей книги, сделанные программно или пользователем. Вычисления по формулам в ячейках рабочей книги не приводят к возникновению этого события.
А теперь, в подтверждение сказанного приведу некоторые примеры:
Хочу обратить Ваше внимание на то, что при написании обработчика события Change объекта Application в качестве журнала изменений я выбрал список, помещенный в пользовательскую форму. Скажу честно, вначале я попытался использовать для этой цели отдельную страницу в одной из книг Excel, но эта попытка оказалась безуспешной и привела к зацикливанию. Дело объясняется тем, что, как Вы понимаете, не только действия пользователя приводят к возникновению события Change. Когда обработчик этого события записывает информацию о возникшем событии в журнал изменений – на одну из страниц рабочей книги, то автоматически возникает событие Change, обработка которого приведет к возникновению нового события и так далее до бесконечности. Этот пример подтверждает сделанный вывод – не только действия пользователя по изменению значений в ячейках рабочей книги приводят к возникновению события Change, - но и программное изменение состояния ячеек, в том числе производимое самой системой, также вызывает событие Change. Замечу в заключение, что вместо формы, для журнала изменений можно было бы выбрать документ Word, таблицу Access, файл, но только не страницу рабочей книги. Обратите также внимание, что для обработчика события Change, связанного с рабочей страницей – объектом WorkSheet, для журнала изменений можно использовать рабочую страницу, но, учтите, только страницу, не заданную данным объектом WorkSheet.
А теперь рассмотрим пример изменений значений в ячейках рабочей страницы, не приводящих к возникновению события Change. В книге BookThree в ячейках G1 и H1 на странице Лист1, для которой, как Вы помните, предусмотрен свой обработчик события Change, я записал соответственно формулы:
= [BookOne.xls]Лист1!A1
= Лист2!A1 + Лист3!A2
Первая из этих формул задает внешнюю ссылку, вторая при вычислениях ссылается на ячейки той же книги, но, взятых из других листов. Если в указанных ячейках произойдет изменение значений, то произойдет изменение значений и в ячейках G1 и H1 на Листе1 книги BookThree, но событие Change для этой страницы не возникнет. Таким образом, ни внутренние ссылки, ни внешние ссылки, стоящие в формулах, не приводят к появлению этого события.
Внешние ссылки можно рассматривать и в более широком аспекте. Например, как внешние ссылки можно рассматривать запросы к источникам данным, выполняемые на рабочих страницах, в том числе Web-запросы, позволяющие получать данные из Интернет. Возникает ли событие Change, когда происходит обновление данных на рабочей странице, поступающих из Интернет при выполнении Web-запроса? Ответ на этот вопрос отрицателен. Событие Change не позволяет следить за обновлениями данных, приходящих из Интернет. В следующем примере я решил проследить за известными биржевыми показателями Доу-Джонса и другими. С этой целью я построил соответствующий Web-запрос, в результате которого с известного сайта , адрес которого: http://investor.msn.com, таблица с нужными показателями копируется в рабочую страницу Excel. Вот как выглядит эта страница после получения данных:
Рис. 5 Таблица, копируемая из Интернет в рабочую страницу Excel
При первоначальном выполнении запроса событие Change возникает лишь на предварительном этапе, когда система пишет некоторое сообщение в указанную область рабочей страницы. Когда же копируются сами данные и при обновлении этих данных событие Change не возникает.
События, связанные с объектом Window
Объект Application может обработать три события, возникающие в процессе работы с окном – объектом Window. Эти события показаны в следующей таблице.
Таблица 5. События, возникающие при работе с объектом Window
Событие |
Когда возникает |
Параметры события |
WindowActivate(Wb As Workbook, Wn As Window) |
Окно рабочей книги становится активным. |
Рабочая книга и вновь активированное окно передаются обработчику события в качестве параметров. |
WindowDeactivate(Wb As Workbook, Wn As Window) |
Окно рабочей книги перестает быть активным. |
Рабочая книга и деактивированное окно передаются обработчику события в качестве параметров. |
WindowResize(Wb As Workbook, Wn As Window) |
Окно рабочей книги изменяет размеры. |
Рабочая книга и перестраиваемое окно передаются обработчику события. |
Полагаю, что в дополнительных комментариях и примерах эти события не нуждаются.
Коллекция 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, проще использовать буфер и стандартную технику «Копировать» - «Вставить».