События происходят постоянно, когда пользователь открывает книгу Excel и начинает выполнять различные действия, такие как ввод данных в ячейки или перемещение между листами.
В редакторе Visual Basic (ALT + F11) уже настроены подпрограммы, которые могут запускаться, когда пользователь что-то делает, например. ввод данных в ячейку. Подпрограмма не предоставляет никакого кода действия, только оператор «Sub» и оператор «End Sub» без кода между ними. Они фактически бездействуют, поэтому ничего не происходит, пока вы не введете код.
Вот пример, основанный на событии «Изменить» на листе:
Как программист VBA, вы можете добавить код, чтобы определенные вещи происходили, когда пользователь выполняет определенное действие. Это дает вам возможность контролировать пользователя и предотвращать его действия, которые вы не хотите, чтобы они выполняли и которые могут повредить вашу книгу. Например, вы можете захотеть, чтобы они сохранили свою индивидуальную копию книги под другим именем, чтобы они не влияли на оригинал, который может использоваться несколькими пользователями.
Если они закроют книгу, им автоматически будет предложено сохранить изменения. Однако в книге есть событие «BeforeClose», и вы можете ввести код, чтобы предотвратить закрытие книги и запуск события «Сохранить». Затем вы можете добавить кнопку на сам рабочий лист и поместить на нее свою собственную процедуру «Сохранить». Вы также можете отключить подпрограмму «Сохранить» с помощью события «Перед сохранением».
Понимание того, как работают события, абсолютно необходимо программисту VBA.
Типы событий
Рабочая тетрадь События - эти события запускаются в зависимости от того, что пользователь делает с самой книгой. Они включают в себя действия пользователя, такие как открытие книги, закрытие книги, сохранение книги, добавление или удаление листа.
События рабочего листа - эти события запускаются пользователем, выполняющим действия на определенном листе. Каждый рабочий лист в книге имеет отдельный модуль кода, который содержит различные события специально для этого рабочего листа (не для всех листов). К ним относятся действия пользователя, такие как изменение содержимого ячейки, двойной щелчок по ячейке или щелчок правой кнопкой мыши по ячейке.
События Active X Control - Элементы управления Active X можно добавить на рабочий лист с помощью значка «Вставить» на вкладке «Разработчик» на ленте Excel. Часто это кнопки управления, позволяющие пользователю выполнять различные действия под управлением вашего кода, но они также могут быть такими объектами, как раскрывающиеся списки. Использование элементов управления Active X в отличие от элементов управления формы на листе дает широкие возможности для программирования. Элементы управления Active X дают вам гораздо больше гибкости с точки зрения программирования по сравнению с использованием элементов управления формы на листе.
Например, на вашем листе может быть два раскрывающихся элемента управления. Вы хотите, чтобы доступный список во втором раскрывающемся списке основывался на том, что пользователь выбрал в первом раскрывающемся списке. Используя событие «Изменить» в первом раскрывающемся списке, вы можете создать код для чтения того, что выбрал пользователь, а затем обновить второе раскрывающееся меню. Вы также можете деактивировать второй раскрывающийся список, пока пользователь не сделает выбор в первом раскрывающемся списке.
События UserForm - Вы можете вставить и создать профессионально оформленную форму для использования в качестве всплывающего окна. Все элементы управления, которые вы размещаете в форме, являются элементами управления Active X и имеют те же события, что и элементы управления Active X, которые вы можете разместить на листе.
График событий - Эти события относятся только к листу диаграммы, а не к диаграмме, появляющейся как часть рабочего листа. Эти события включают изменение размера диаграммы или выбор диаграммы.
События приложений - Они используют объект Application в VBA. Примеры позволяют запускать код при нажатии определенной клавиши или по достижении определенного времени. Вы можете запрограммировать ситуацию, когда книга остается открытой 24/7 и импортирует данные из внешнего источника в течение ночи в заранее определенное время.
Опасности использования кода в событиях
Когда вы пишете код, чтобы что-то делать, когда пользователь выполняет определенное действие, вы должны помнить, что ваш код может запускать другие события, которые могут превратить ваш код в непрерывный цикл.
Например, предположим, что вы используете событие «Изменить» на листе, чтобы, когда пользователь помещает значение в ячейку, вычисление на основе этой ячейки помещается в ячейку непосредственно справа от нее.
Проблема здесь в том, что размещение вычисленного значения в ячейке запускает другое событие 'Change', которое затем, в свою очередь, запускает еще одно событие 'Change', и так далее, пока в вашем коде не закончатся столбцы для использования, и оно не вызовет сообщение об ошибке.
Вам нужно хорошо подумать при написании кода для события, чтобы гарантировать, что другие события не будут запущены непреднамеренно.
Отключить события
Вы можете использовать код для отключения событий, чтобы обойти эту проблему. Что вам нужно сделать, так это включить код для отключения событий во время выполнения кода события, а затем повторно включить события в конце кода. Вот пример того, как это сделать:
| 1234 | Sub DisableEvents ()Application.EnableEvents = FalseApplication.EnableEvents = TrueКонец подписки |
Имейте в виду, что это отключает все события прямо в приложении Excel, поэтому это также повлияет на другие функции в Excel. Если вы используете это по какой-либо причине, убедитесь, что впоследствии события снова включаются.
Важность параметров в событиях
У событий обычно есть параметры, которые можно использовать, чтобы узнать больше о том, что делает пользователь, и о местоположении ячейки, в которой он находится.
Например, событие «Изменение рабочего листа» выглядит так:
| 1 | Private Sub Worksheet_Change (значение ByVal как диапазон) |
Используя объект диапазона, вы можете узнать координаты строки / столбца ячейки, в которой на самом деле находится пользователь.
| 1234 | Private Sub Worksheet_Change (значение ByVal как диапазон)MsgBox Target.ColumnMsgBox Target.RowКонец подписки |
Если вы хотите, чтобы ваш код работал только с определенным номером столбца или строки, вы добавляете условие, которое завершает подпрограмму, если столбец не является обязательным.
| 123 | Private Sub Worksheet_Change (значение ByVal как диапазон)Если Target.Column 2, то выйдите из SubКонец подписки |
Это позволяет решить проблему запуска нескольких событий в вашем коде, поскольку он будет работать только в том случае, если пользователь изменил ячейку в столбце 2 (столбец B).
Примеры событий в книге (не исчерпывающие)
События книги находятся в объекте ThisWorkbook в обозревателе проектов VBE. Вам нужно будет выбрать «Workbook» в первом раскрывающемся списке окна кода, а затем во втором раскрывающемся списке будут показаны все доступные события.
Событие открытия книги
Это событие запускается всякий раз, когда книга открывается пользователем. Вы можете использовать его, чтобы отправить приветственное сообщение пользователю, указав его имя пользователя.
| 123 | Частная вспомогательная книга_Open ()MsgBox "Добро пожаловать" и Application.UserNameКонец подписки |
Вы также можете сравнить их имя пользователя со списком на скрытом листе, чтобы узнать, имеют ли они право доступа к книге. Если они не являются авторизованными пользователями, вы можете отобразить сообщение и закрыть книгу, чтобы они не могли ее использовать.
Событие нового листа книги
Это событие запускается, когда пользователь добавляет новый лист в книгу.
Вы можете использовать этот код только для того, чтобы позволить себе добавлять новый лист, а не для того, чтобы разные пользователи добавляли листы и создавали беспорядок в книге.
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh как объект)Application.DisplayAlerts = FalseЕсли Application.UserName "Ричард" ТогдаШ.ДелетеКонец, еслиApplication.DisplayAlerts = TrueКонец подписки |
Обратите внимание, что вам необходимо отключить предупреждения, поскольку при удалении листа появится предупреждение пользователя, что позволяет пользователю обойти ваш код. Убедитесь, что вы снова включили оповещения!
Устали искать примеры кода VBA? Попробуйте AutoMacro!
Рабочая книга перед сохранением события
Это событие запускается, когда пользователь нажимает на значок «Сохранить», но до того, как «Сохранить» на самом деле происходит.
Как описано ранее, вы можете запретить пользователям сохранять свои изменения в исходной книге и заставить их создавать новую версию с помощью кнопки на листе. Все, что вам нужно сделать, это изменить параметр «Отмена» на «Истина», и книга никогда не сможет быть сохранена обычным способом.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Отменить как Boolean)Отмена = ИстинаКонец подписки |
Рабочая книга перед событием закрытия
Вы можете использовать это событие, чтобы запретить пользователям закрывать книгу и снова заставить их выйти с помощью кнопки рабочего листа. Опять же, вы устанавливаете для параметра «Отмена» значение «True». Красный крестик в правом верхнем углу окна Excel больше не работает.
| 123 | Private Sub Workbook_BeforeClose (Отменить как логическое)Отмена = ИстинаКонец подписки |
Примеры событий рабочего листа (не исчерпывающие)
События рабочего листа находятся под конкретным объектом имени листа в обозревателе проектов VBE. Вам нужно будет выбрать «Рабочий лист» в первом раскрывающемся списке окна кода, а затем во втором раскрывающемся списке будут показаны все доступные события.
Событие изменения рабочего листа
Это событие запускается, когда пользователь вносит изменения в рабочий лист, например вводит новое значение в ячейку.
Вы можете использовать это событие, чтобы добавить дополнительное значение или комментарий рядом с измененной ячейкой, но, как обсуждалось ранее, вы не хотите запускать цикл событий.
| 12345 | Private Sub Worksheet_Change (значение ByVal как диапазон)Если Target.Column 2, то выйдите из SubActiveSheet.Cells (Target.Row, Target.Column + 1). Значение = _ActiveSheet.Cells (Target.Row, Target.Column). Значение * 1,1Конец подписки |
В этом примере код будет работать, только если значение введено в столбец B (столбец 2). Если это правда, то он добавит 10% к числу и поместит его в следующую доступную ячейку.
Рабочий лист перед событием двойного щелчка
Это событие вызовет запуск кода, если пользователь дважды щелкнет ячейку. Это может быть чрезвычайно полезно для финансовых отчетов, таких как баланс или отчет о прибылях и убытках, где цифры могут быть оспорены менеджерами, особенно если чистая прибыль отрицательная!
Вы можете использовать это для обеспечения возможности детализации, так что, когда менеджер запрашивает конкретный номер, все, что ему нужно сделать, это дважды щелкнуть номер, и разбивка появится как часть отчета.
Это очень впечатляет с точки зрения пользователя и избавляет его от постоянных вопросов: «Почему это число так велико?»
Вам нужно будет написать код, чтобы узнать заголовок / критерии для числа (используя свойства целевого объекта), а затем отфильтровать табличные данные и затем скопировать их в отчет.
Программирование на VBA | Генератор кода действительно работает для вас!
Рабочий лист активировать событие
Это событие происходит, когда пользователь переходит с одного листа на другой. Это относится к новому листу, на который переходит пользователь.
Его можно использовать, чтобы гарантировать, что новый лист полностью рассчитан, прежде чем пользователь начнет что-либо с ним делать. Его также можно использовать только для пересчета этого конкретного листа без пересчета всей книги. Если рабочая книга большая и в ней есть сложная формула, то повторный расчет одного листа экономит много времени.
| 123 | Личный вспомогательный лист_активировать ()ActiveSheet.CalculateКонец подписки |
События Active X Control (не исчерпывающий)
Как обсуждалось ранее, вы можете добавлять элементы управления Active X прямо на рабочий лист. Это могут быть командные кнопки, раскрывающиеся списки и списки.
События Active X находятся под конкретным объектом имени листа (куда вы добавили элемент управления) в обозревателе проектов VBE. Вам нужно будет выбрать имя элемента управления Active X в первом раскрывающемся списке окна кода, а затем во втором раскрывающемся списке будут показаны все доступные события.
Событие нажатия кнопки управления
Когда вы поместили командную кнопку в электронную таблицу, вы захотите, чтобы она выполняла какое-то действие. Вы делаете это, помещая код в событие Click.
Вы можете легко поставить здесь сообщение «Вы уверены?», Чтобы проверить его перед запуском кода.
| 12345 | Частная подпрограмма CommandButton1_Click ()Тусклая кнопка: вернуть как вариантButtonRet = MsgBox («Вы уверены, что хотите это сделать?», VbQuestion или vbYesNo)Если ButtonRet = vbNo, то выйти из SubКонец подписки |
Выпадающий список (поле со списком) Изменить событие
В раскрывающемся списке Active X есть событие изменения, поэтому, если пользователь выбирает конкретный элемент из раскрывающегося списка, вы можете зафиксировать его выбор с помощью этого события, а затем написать код для соответствующей адаптации других частей листа или книги.
| 123 | Приватный суб ComboBox1_Change ()MsgBox "Вы выбрали" и ComboBox1.TextКонец подписки |
Программирование на VBA | Генератор кода действительно работает для вас!
Установите флажок (флажок) Щелкните событие
Вы можете добавить галочку или флажок на лист, чтобы предоставить пользователю возможность выбора. Вы можете использовать событие щелчка на нем, чтобы увидеть, изменил ли пользователь что-либо в нем. Возвращаемые значения - Истина или Ложь, в зависимости от того, отмечен он или нет.
| 123 | Private Sub CheckBox1_Click ()MsgBox CheckBox1.ValueКонец подписки |
События UserForm (не исчерпывающий)
Excel позволяет создавать собственные формы. Они могут быть очень полезны для использования в качестве всплывающих окон для сбора информации или предоставления пользователю нескольких вариантов выбора. Они используют элементы управления Active X, как описано ранее, и имеют точно такие же события, хотя события во многом зависят от типа элемента управления.
Вот пример простой формы:
Когда он отображается, это то, как он выглядит на экране
Вы можете использовать события в форме, чтобы делать такие вещи, как ввод названия компании по умолчанию при открытии формы, чтобы проверить, что введенное название компании соответствует тому, которое уже есть в электронной таблице и не было неправильно написано, и чтобы добавить код для щелчка события на кнопках «ОК» и «Отмена»
Код и события, стоящие за формой, можно просмотреть, дважды щелкнув в любом месте формы.
Первое раскрывающееся меню дает доступ ко всем элементам управления в форме. Второй раскрывающийся список предоставит доступ к событиям.
Событие активации пользовательской формы
Это событие запускается при активации формы, обычно при ее отображении. Это событие можно использовать для установки значений по умолчанию, например. название компании по умолчанию в текстовом поле названия компании
| 123 | Частная подписка UserForm_Activate ()TextBox1.Text = "Название моей компании"Конец подписки |
Программирование на VBA | Генератор кода действительно работает для вас!
Изменить событие
Большинство элементов управления в форме имеют событие изменения, но в этом примере текстовое поле названия компании может использовать событие, чтобы установить ограничение на длину вводимого названия компании.
| 123456 | Частное под-текстовое поле1_Change ()Если Len (TextBox1.Text)> 20, тоMsgBox "Имя ограничено 20 символами", vbCriticalTextBox1.Text = ""Конец, еслиКонец подписки |
Нажмите Событие
Вы можете использовать это событие, чтобы предпринять действия, когда пользователь нажимает на элементы управления в форме или даже на саму форму.
В этой форме есть кнопка «ОК», и, собрав название компании, мы хотели бы поместить его в ячейку электронной таблицы для дальнейшего использования.
| 1234 | Частная подпрограмма CommandButton1_Click ()ActiveSheet.Range ("A1"). Значение = TextBox1.TextMe.HideКонец подписки |
Этот код действует, когда пользователь нажимает кнопку «ОК». Он помещает значение в поле ввода названия компании в ячейку A1 на активном листе, а затем скрывает форму, чтобы пользовательский элемент управления возвращался обратно на рабочий лист.
График событий
События диаграммы работают только с диаграммами, которые находятся на отдельном листе диаграммы, а не с диаграммой, которая включена в стандартный рабочий лист.
События диаграммы несколько ограничены и не могут использоваться на листе, где у вас может быть несколько диаграмм. Кроме того, пользователи не обязательно хотят переключаться с рабочего листа, содержащего числа, на лист диаграммы - здесь нет немедленного визуального воздействия.
Самым полезным событием было бы узнать компонент диаграммы, на который щелкнул пользователь, например сегмент круговой диаграммы или столбец на гистограмме, но это событие недоступно для стандартного диапазона событий.
Эту проблему можно решить, используя модуль класса для добавления события «Mouse Down», которое будет возвращать сведения о компоненте диаграммы, на котором щелкнул пользователь. Это используется на диаграмме на листе.
Это связано с очень сложным кодированием, но результаты впечатляют. Вы можете создавать детализацию, например пользователь щелкает сегмент круговой диаграммы, и эта диаграмма мгновенно скрывается, а на ее месте появляется вторая диаграмма, показывающая круговую диаграмму с подробностями для исходного сегмента, или вы можете создать табличные данные, поддерживающие этот сегмент круговой диаграммы.
События приложения
Вы можете использовать объект Application в VBA для запуска кода в соответствии с определенным событием.
Программирование на VBA | Генератор кода действительно работает для вас!
Application.OnTime
Это может позволить вам запускать фрагмент кода через равные промежутки времени, пока книга загружена в Excel. Возможно, вы захотите автоматически сохранять свою книгу в другую папку каждые 10 минут или оставить рабочий лист на ночь, чтобы получать последние данные из внешнего источника.
В этом примере подпрограмма вводится в модуль. Он отображает окно сообщения каждые 5 минут, хотя это может быть другая закодированная процедура. В то же время он сбрасывает таймер на текущее время плюс еще 5 минут.
Каждый раз, когда он запускается, таймер сбрасывается, чтобы запустить ту же подпрограмму еще через 5 минут.
| 1234 | Sub TestOnTime ()MsgBox «Тестирование в режиме реального времени»Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"Конец подписки |
Application.OnKey
Эта функция позволяет создавать собственные горячие клавиши. Вы можете сделать вызов любой комбинации клавиш подпрограммой вашего творчества.
В этом примере буква «а» перенаправлена так, что вместо размещения «а» в ячейке отображается окно сообщения. Этот код необходимо поместить во вставленный модуль.
| 123456 | Sub TestKeyPress ()Application.OnKey "a", "TestKeyPress"Конец подпискиSub TestKeyPress ()MsgBox "Вы нажали 'a'"Конец подписки |
Сначала вы запускаете подпрограмму TestKeyPress. Вам нужно запустить это только один раз. Он сообщает Excel, что каждый раз, когда нажимается буква «а», он вызывает подпрограмму «TestKeyPress». Подпрограмма «TestKeyPress» просто отображает окно сообщения, чтобы сообщить вам, что вы нажали клавишу «a». Конечно, он мог загружать форму или делать много чего другого.
Вы можете использовать любую комбинацию клавиш, которую вы можете использовать с функцией «SendKeys».
Чтобы отменить эту функцию, вы запускаете оператор OnKey без параметра «Процедура».
| 123 | Sub CancelOnKey ()Application.OnKey "а"Конец подписки |
Теперь все вернулось к норме.
