Події Excel VBA - простий (і повний) посібник

Коли ви створюєте або записуєте макрос в Excel, вам потрібно запустити макрос, щоб виконати кроки в коді.

Кілька способів запуску макросу включають використання діалогового вікна макросу, призначення макросу кнопці, використання ярлика тощо.

Крім цих ініційованих користувачем макросів, ви також можете використовувати події VBA для запуску макросу.

Події Excel VBA - Вступ

Спочатку дозвольте мені пояснити, що таке подія у VBA.

Подія - це дія, яка може викликати виконання зазначеного макросу.

Наприклад, коли ви відкриваєте нову книгу, це подія. Коли ви вставляєте новий аркуш, це подія. Якщо двічі клацнути клітинку, це подія.

У VBA багато таких подій, і ви можете створити коди для цих подій. Це означає, що як тільки подія станеться, і якщо ви вказали код для цієї події, цей код буде миттєво виконано.

Excel автоматично робить це, як тільки помічає, що подія відбулася. Тому вам потрібно лише написати код і розмістити його у правильній підпрограмі подій (про це йдеться далі у цій статті).

Наприклад, якщо ви вставляєте новий аркуш і хочете, щоб він мав префікс року, ви можете написати код для нього.

Тепер, коли хтось вставляє новий аркуш, цей код автоматично виконуватиметься і додаватиме префікс року до назви аркуша.

Іншим прикладом може бути те, що ви хочете змінити колір комірки, коли хтось двічі натисне на неї. Для цього можна скористатися подією подвійного клацання.

Так само ви можете створити коди VBA для багатьох таких подій (як ми побачимо далі в цій статті).

Нижче наведено короткий візуальний матеріал, який показує подію подвійного клацання в дії. Як тільки я двічі натискаю клітинку А1. Excel миттєво відкриває вікно повідомлення з адресою комірки.

Подвійне натискання-це подія, і відображення вікна повідомлення-це те, що я вказав у коді кожного разу, коли відбувається подія подвійного клацання.

Хоча наведений вище приклад є марною подією, я сподіваюся, що він допоможе вам зрозуміти, що насправді є подіями.

Різні типи подій Excel VBA

В Excel є різні об’єкти - наприклад, сам Excel (до якого ми часто звертаємось як до програми), робочі зошити, робочі аркуші, діаграми тощо.

Кожен із цих об’єктів може мати різні події, пов’язані з ним. Наприклад:

• Якщо ви створюєте нову книгу, це подія на рівні програми.
• Якщо ви додаєте новий аркуш, це подія на рівні книги.
• Якщо ви зміните значення в комірці на аркуші, це подія на рівні аркуша.

Нижче наведені різні типи подій, які існують у Excel:

1. Події на рівні робочого аркуша: Це типи подій, які ініціюються на основі дій, зроблених на аркуші. Приклади цих подій включають зміну комірки на робочому аркуші, зміну виділення, подвійне клацання по клітинці, клацання правою кнопкою миші клітинку тощо.
2. Події на рівні робочого зошита: Ці події будуть ініційовані на основі дій на рівні робочої книги. Приклади цих подій включають додавання нового аркуша, збереження книги, відкриття книги, друк частини або всієї книги тощо.
3. Події на рівні застосування: Ці події відбуваються у програмі Excel. Прикладом цього може бути закриття будь -якої з відкритих книг або відкриття нової книги.
4. Події на рівні UserForm: Ці події будуть ініційовані на основі дій у "UserForm". Приклади цього включають ініціалізацію UserForm або натискання кнопки у UserForm.
5. Події діаграми: Це події, пов'язані з аркушем діаграми. Аркуш діаграми відрізняється від аркуша (саме там більшість із нас використовується для роботи в Excel). Мета аркушів діаграми - зберігати діаграму. Приклади таких подій включають зміну серії діаграми або зміну розміру діаграми.
6. Події OnTime та OnKey: Це дві події, які не входять у жодну з наведених вище категорій. Тому я перерахував їх окремо. Подія "OnTime" дозволяє виконувати код у певний час або після того, як минув певний час. Подія "OnKey" дозволяє виконувати код під час використання певного натискання клавіші (або комбінації натискань клавіш).

Де розмістити код, пов’язаний із подіями

У наведеному вище розділі я розглянув різні типи подій.

Виходячи з типу події, потрібно розмістити код у відповідному об’єкті.

Наприклад, якщо це подія, пов’язана з робочим аркушем, вона повинна знаходитись у вікні коду об’єкта аркуша. Якщо це пов’язано з робочою книгою, вона повинна знаходитися у вікні коду об’єкта книги.

У VBA різні об’єкти, такі як робочі аркуші, робочі зошити, таблиці діаграм, користувальницькі форми тощо, мають власні вікна коду. Вам потрібно розмістити код події у вікні коду відповідного об’єкта. Наприклад - якщо це подія на рівні книги, то вам потрібно мати код події у вікні коду книги.

Наступні розділи охоплюють місця, де можна розмістити код події:

У вікні коду робочого аркуша

Коли ви відкриваєте редактор VB (за допомогою комбінації клавіш ALT + F11), ви помітите об’єкт аркушів у Провіднику проектів. Для кожного аркуша у книзі ви побачите один об’єкт.

Якщо двічі клацнути на об’єкті робочого аркуша, в якому потрібно розмістити код, він відкриє вікно коду для цього аркуша.

Хоча ви можете почати писати код з нуля, набагато краще вибрати подію зі списку опцій і дозволити VBA автоматично вставити відповідний код для вибраної події.

Для цього вам потрібно спочатку вибрати аркуш зі спадного меню у верхньому лівому куті вікна коду.

Після вибору робочого аркуша зі спадного меню ви отримаєте список усіх подій, пов'язаних з аркушем. Ви можете вибрати той, який ви хочете використовувати, зі спадного меню у верхньому правому куті вікна коду.

Як тільки ви вибираєте подію, вона автоматично вводить перший і останній рядок коду для вибраної події. Тепер ви можете додати свій код між двома рядками.

Примітка: Щойно ви виберете Робочий лист зі спадного меню, ви помітите, що у вікні коду з’являться два рядки коду. Вибравши подію, для якої потрібно отримати код, можна видалити рядки, які з’явилися за замовчуванням.

Зауважте, що кожен аркуш має власне вікно коду. Коли ви введете код для Sheet1, він працюватиме лише в тому випадку, якщо подія відбудеться у Sheet1.

У вікні коду цієї робочої книги

Як і аркуші, якщо у вас є код події на рівні книги, ви можете розмістити його у вікні коду ThisWorkbook.

Якщо двічі клацнути на цій книзі, вона відкриє вікно коду для неї.

Вам потрібно вибрати Робочу книгу зі спадного меню у верхньому лівому куті вікна коду.

Вибравши Робочу книгу зі спадного меню, ви отримаєте список усіх подій, пов’язаних із Книгою. Ви можете вибрати той, який ви хочете використовувати, зі спадного меню у верхньому правому куті вікна коду.

Як тільки ви вибираєте подію, вона автоматично вводить перший і останній рядок коду для вибраної події. Тепер ви можете додати свій код між двома рядками.

Примітка: Щойно ви виберете Робоча книга зі спадного меню, ви помітите, що у вікні коду з’являться два рядки коду. Вибравши подію, для якої потрібно отримати код, можна видалити рядки, які з’явились за замовчуванням.

У вікні коду форми користувача

Коли ви створюєте UserForms у Excel, ви також можете використовувати події UserForm для виконання кодів на основі певних дій. Наприклад, можна вказати код, який виконується при натисканні на кнопку.

Хоча об’єкти «Аркуш» та об’єкти «Ця робоча книга» вже доступні під час відкриття редактора VB, спочатку потрібно створити UserForm.

Щоб створити UserForm, клацніть правою кнопкою миші на будь-якому об’єкті, перейдіть до Insert і натисніть UserForm.

Це додасть об’єкт UserForm до книги.

Якщо двічі клацнути на UserForm (або на будь-якому об’єкті, який ви додаєте до UserForm), відкриється вікно коду для UserForm.

Тепер, як і робочі аркуші або ThisWorkbook, ви можете вибрати подію, і вона вставить перший та останній рядки для цієї події. І тоді ви можете додати код посередині нього.

У вікні коду діаграми

В Excel можна також вставляти аркуші діаграм (які відрізняються від аркушів). Аркуш діаграми має містити лише діаграми.

Коли ви вставите аркуш діаграми, ви зможете побачити об’єкт аркуша діаграми у редакторі VB.

Ви можете додати код події у вікно коду аркуша діаграми так само, як ми це зробили на робочому аркуші.

Двічі клацніть об’єкт аркуша діаграми в Провіднику проектів. Відкриється вікно коду аркуша діаграми.

Тепер вам потрібно вибрати діаграму зі спадного меню у верхньому лівому куті вікна коду.

Після вибору діаграми зі спадного меню ви отримаєте список усіх подій, пов’язаних із аркушем діаграми. Ви можете вибрати той, який ви хочете використовувати, зі спадного меню у верхньому правому куті вікна коду.

Примітка: Щойно ви виберете діаграму зі спадного меню, ви помітите, що у вікні коду з’являться два рядки коду. Вибравши подію, для якої потрібно отримати код, можна видалити рядки, які з’явилися за замовчуванням.

Модуль у класі

Модулі класу потрібно вставляти так само, як і UserForms.

Модуль класу може містити код, пов'язаний із додатком - яким буде сам Excel, та вбудовані діаграми.

Найближчими тижнями я висвітлю модульний клас як окремий підручник.

Зауважте, що окрім подій OnTime та OnKey, жодна з вищезгаданих подій не може бути збережена у звичайному модулі VBA.

Розуміння послідовності подій

Коли ви ініціюєте подію, це не відбувається окремо. Це також може призвести до послідовності декількох тригерів.

Наприклад, коли ви вставляєте новий аркуш, відбуваються такі речі:

1. Додано новий робочий лист
2. Попередній аркуш деактивується
3. Новий робочий лист активується

Хоча в більшості випадків вам може не потрібно турбуватися про послідовність, якщо ви створюєте складні коди, які спираються на події, краще знати послідовність, щоб уникнути несподіваних результатів.

Розуміння ролі аргументів у подіях VBA

Перш ніж перейти до прикладів подій та чудових речей, які ви можете з нею зробити, мені потрібно охопити одну важливу концепцію.

У подіях VBA існує два типи кодів:

• Без жодних аргументів
• З аргументами

І в цьому розділі я хочу швидко висвітлити роль аргументів.

Нижче наведено код, у якому немає аргументів (дужки порожні):

Private Sub Workbook_Open () MsgBox "Не забудьте заповнити табель робочого часу" End Sub

За допомогою вищевказаного коду, коли ви відкриваєте книгу, вона просто показує вікно повідомлення з повідомленням - “Не забудь заповнити табель робочого часу”.

Тепер давайте подивимось на код, який має аргумент.

Private Sub Workbook_NewSheet (ByVal Sh As Object) Sh.Range ("A1") = Кінцевий підрозділ Sh.Name

У наведеному вище коді використовується аргумент Sh, який визначається як тип об’єкта. Аргументом Sh може бути аркуш або аркуш діаграми, оскільки вищезгадана подія ініціюється при додаванні нового аркуша.

Призначивши новий аркуш, який додається до книги, до об’єктної змінної Sh, VBA дозволила нам використовувати його у коді. Тож для посилання на нову назву аркуша я можу використовувати Sh.Name.

Поняття аргументів стане в нагоді, коли ви переглянете приклади подій VBA у наступних розділах.

Події на рівні робочого зошита (пояснюється прикладами)

Нижче наведені найбільш часто використовувані події у робочому зошиті.

НАЗВА ПОДІЇ	ЩО ТРИГГУЄ ПОДОБУ
Активувати	Коли робочу книгу активовано
Після збереження	Коли книга встановлена ​​як надбудова
Перед збереженням	Коли книга зберігається
ПередЗакрити	Коли робочу книгу закрито
Перед друком	Коли друкується робоча книга
Деактивувати	Коли книгу вимкнено
Новий аркуш	Коли додається новий аркуш
відчинено	Коли відкривається робоча книга
SheetActivate	Коли будь -який аркуш у книзі активовано
SheetBeforeDelete	Коли будь -який аркуш видаляється
SheetBeforeDoubleClick	Якщо двічі клацнути на будь-якому аркуші
SheetBeforeRightClick	Коли будь-який аркуш натискається правою кнопкою миші
АркушВирахувати	Коли будь -який аркуш розраховується або перераховується
SheetDeactivate	Коли книгу вимкнено
SheetPivotTableUpdate	Коли книга оновлюється
SheetSelectionChange	При зміні робочої книжки
WindowActivate	Коли робочу книгу активовано
WindowDeactivate	Коли книгу вимкнено

Зауважте, що це не повний список. Повний список можна знайти тут.

Пам’ятайте, що код події Книги зберігається у вікні коду об’єктів ThisWorkbook.

Тепер давайте подивимось на деякі корисні події з робочої книги та подивимось, як їх можна використовувати у повсякденній роботі.

Відкрита подія робочого зошита

Скажімо, ви хочете показати користувачеві дружнє нагадування про заповнення його табелів обліку робочого часу, коли вони відкривають певну книгу.

Для цього можна скористатися наведеним нижче кодом:

Private Sub Workbook_Open () MsgBox "Не забудьте заповнити табель робочого часу" End Sub

Тепер, як тільки ви відкриєте книгу з цим кодом, вона покаже вам вікно повідомлення із зазначеним повідомленням.

Під час роботи з цим кодом (або кодами подій у робочій книзі загалом) потрібно знати кілька речей:

• Якщо у книзі є макрос, і ви хочете його зберегти, його потрібно зберегти у форматі .XLSM. Інакше код макросу буде втрачено.
• У наведеному вище прикладі код події виконуватиметься лише тоді, коли макроси активовані. Ви можете побачити жовту смужку з проханням надати дозвіл на ввімкнення макросів. Поки це не ввімкнено, код події не виконується.
• Код події робочої книги розміщується у вікні коду об'єкта ThisWorkbook.

Ви можете ще більше уточнити цей код і показати повідомлення лише за п’ятницю.

Нижче наведений код зробить це:

Private Sub Workbook_Open () wkday = День тижня (дата) Якщо wkday = 6 Тоді MsgBox "Не забудьте заповнити таблицю часу" End Sub

Зауважте, що у функції «День тижня» неділі присвоюється значення 1, понеділку - 2 тощо.

Тому за п’ятницю я використав 6.

Відкритий захід робочої книги може бути корисним у багатьох ситуаціях, таких як:

• Коли ви хочете показати привітальне повідомлення людині під час відкриття книги.
• Коли ви хочете відобразити нагадування під час відкриття книги.
• Коли ви хочете завжди активувати один конкретний аркуш у книзі, коли вона відкривається.
• Якщо ви хочете відкрити пов’язані файли разом із книгою.
• Якщо ви хочете фіксувати дату та час при кожному відкритті книги.

Робочий зошит NewSheet Подія

Подія NewSheet запускається, коли ви вставляєте новий аркуш у книгу.

Скажімо, ви хочете ввести значення дати та часу в клітинку А1 щойно вставленого аркуша. Для цього можна скористатися наведеним нижче кодом:

Private Sub Workbook_NewSheet (ByVal Sh As Object) Про помилку Відновлення Наступний Sh.Range ("A1") = Формат (Зараз, "dd-mmm-yyyy hh: mm: ss") Кінцевий підрозділ

У наведеному вище коді використовується "Наступне відновлення помилки" для обробки випадків, коли хтось вставляє аркуш діаграми, а не аркуш. Оскільки аркуш діаграми не містить осередку A1, він відображатиме помилку, якщо не використовуватиметься "On Error Resume Next".

Іншим прикладом може бути, коли ви хочете застосувати деякі основні параметри або форматування до нового аркуша, як тільки він буде доданий. Наприклад, якщо ви хочете додати новий аркуш і хочете, щоб він автоматично отримував серійний номер (до 100), то ви можете скористатися наведеним нижче кодом.

Private Sub Workbook_NewSheet (ByVal Sh As Object) Про помилку Відновити далі з Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbБілий кінець з For i = 1 До 100 Sh.Range ("A1"). Зсув (i, 0) .Value = i Next i Sh.Range ("A1", Діапазон ("A1"). End (xlDown)). Borders.LineStyle = xl Безперервний кінцевий підрозділ

Наведений вище код також трохи форматує. Він надає осередку заголовка блакитний колір і робить шрифт білим. Він також застосовує рамку до всіх заповнених клітинок.

Наведений вище код є прикладом того, як короткий код VBA може допомогти вам вкрасти кілька секунд кожного разу, коли ви вставляєте новий аркуш (на випадок, якщо це потрібно робити щоразу).

Робочий зошит Перед збереженням події

Під час збереження події спрацьовує подія Збереження. Зауважте, що спочатку запускається подія, а потім книга зберігається.

Під час збереження книги Excel можливі два сценарії:

1. Ви зберігаєте його вперше, і він покаже діалогове вікно Зберегти як.
2. Ви вже зберегли його раніше, і він просто збереже та перезапише зміни у вже збереженій версії.

Тепер давайте подивимося на кілька прикладів, де можна використовувати подію BeforeSave.

Припустимо, у вас є нова книга, яку ви зберігаєте вперше, і хочете нагадати користувачеві зберегти її на диску K, тоді ви можете скористатися наведеним нижче кодом:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI як Boolean, Скасувати як Boolean) Якщо SaveAsUI То MsgBox "Зберегти цей файл на диску K" End Sub

У наведеному вище коді, якщо файл ніколи не зберігався, SaveAsUI має значення True і відкриває діалогове вікно Зберегти як. Наведений вище код відображатиме повідомлення до появи діалогового вікна Зберегти як.

Іншим прикладом може бути оновлення дати та часу збереження файлу у певній комірці.

Код, наведений нижче, вставлятиме дату та час у клітинку A1 аркуша 1 при збереженні файлу.

Приватна підручникова книга_Береозбереження (ByVal SaveAsUI як булеве, Скасувати як логічне) Робочі аркуші ("Лист1"). Діапазон ("A1") = Формат (Зараз "dd-mmm-yyyy hh: mm: ss") End Sub

Зауважте, що цей код виконується, як тільки користувач зберігає книгу. Якщо книга зберігається вперше, у ній відобразиться діалогове вікно Зберегти як. Але код вже виконується до того часу, коли ви побачите діалогове вікно Зберегти як. На цьому етапі, якщо ви вирішите скасувати та не зберегти книгу, дата та час уже будуть введені в клітинку.

Робочий зошит Перед закритою подією

Перед подією Закриття відбувається безпосередньо перед закриттям книги.

Нижче наведений код захищає всі аркуші до закриття книги.

Private Sub Workbook_BeforeClose (Скасувати як логічне значення) Dim sh як робочий аркуш для кожного sh у цій робочій книзі.

Пам’ятайте, що код події спрацьовує, щойно ви закриєте книгу.

Важливо знати про цю подію те, що їй байдуже, чи справді книга закрита чи ні.

Якщо книгу не було збережено, і вам відображається запит із запитом, чи зберегти книгу, чи ні, і ви натискаєте Скасувати, це не збереже вашу книгу.Однак до того часу код події вже був би виконаний.

Робочий зошит Перед подією

Коли ви подаєте команду print (або команду Preview Print), спрацьовує подія перед друком.

Код, наведений нижче, перерахує всі аркуші перед друком вашої книжки.

Private Sub Workbook_BeforePrint (Скасувати як логічне значення) Для кожного ws у робочих аркушах ws.Вирахувати наступний ws End Sub

Коли користувач друкує книгу, подія буде активована незалежно від того, друкує вона цілу книгу або лише її частину.

Інший приклад нижче - це код, який би додав дату та час до нижнього колонтитула під час друку книги.

Private Sub Workbook_BeforePrint (Скасувати як логічне значення) Dim ws як робочий аркуш для кожного ws у ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Надруковано-" & Формат (Зараз, "dd-mmm-yyyy hh: mm") Наступний ws End Sub

Події на рівні робочого аркуша (пояснюється прикладами)

Події робочого аркуша відбуваються на основі тригерів на аркуші.

Нижче наведені найбільш часто використовувані події на аркуші.

Назва події	Що викликає подію
Активувати	Коли робочий аркуш активовано
Перед видаленням	Перед видаленням аркуша
ПередDoubleClick	Перш ніж двічі клацнути аркуш
ПередRightClick	Перед робочим аркушем клацніть правою кнопкою миші
Обчисліть	Перед розрахунком або перерахунком робочого аркуша
Змінити	Коли клітинки на робочому аркуші змінюються
Деактивувати	Коли аркуш деактивовано
Зведена таблицяОновлення	Коли оновлюється зведена таблиця на аркуші
SelectionChange	При зміні виділення на аркуші

Зауважте, що це не повний список. Повний список можна знайти тут.

Пам’ятайте, що код події аркуша зберігається у вікні коду об’єкта робочого аркуша (у тому, у якому потрібно, щоб подія ініціювалася). У робочій книзі може бути кілька робочих аркушів, і ваш код буде запущено лише тоді, коли подія має місце на робочому аркуші, у якому вона розміщена.

Тепер давайте подивимося на деякі корисні події на робочому аркуші та подивимось, як їх можна використовувати у повсякденній роботі.

Робочий лист Активувати подію

Ця подія запускається під час активації робочого аркуша.

Код нижче знімає захист аркуша, як тільки він активується.

Private Sub Worksheet_Activate () ActiveSheet.Unprotect End Sub

Ви також можете використовувати цю подію, щоб переконатися, що вибрано певну клітинку або діапазон клітинок (або іменований діапазон), як тільки ви активуєте аркуш. У наведеному нижче коді буде вибрано клітинку D1, як тільки ви активуєте аркуш.

Приватний додатковий робочий аркуш_Activate () ActiveSheet.Range ("D1"). Виберіть End Sub

Подія зміни робочого аркуша

Подія зміни запускається щоразу, коли ви вносите зміни на аркуші.

Ну … не завжди.

Деякі зміни викликають подію, а деякі - ні. Ось список деяких змін, які не спричинять подію:

• При зміні форматування комірки (розмір шрифту, колір, рамка тощо).
• При об’єднанні клітинок. Це дивно, оскільки іноді об’єднання клітинок також видаляє вміст з усіх комірок, крім верхньої лівої.
• Під час додавання, видалення або редагування коментаря до комірки.
• Коли ви сортуєте діапазон клітинок.
• Коли ви використовуєте пошук цілей.

Наступні зміни спричинять подію (навіть якщо вам здається, що це не повинно бути):

• Копіювання та вставлення форматування ініціює подію.
• Очищення форматування ініціює подію.
• Виконання перевірки правопису викликає подію.

Нижче наведено код, який відображатиме вікно повідомлення з адресою комірки, яку було змінено.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "You just changed" & Target.Address End Sub

Хоча це марний макрос, він показує, як використовувати аргумент Target, щоб дізнатися, які клітинки були змінені.

Тепер розглянемо ще пару корисних прикладів.

Припустимо, у вас є діапазон клітинок (скажімо, A1: D10), і ви хочете показати запит і запитати користувача, чи дійсно вони хочуть змінити клітинку в цьому діапазоні чи ні, ви можете скористатися наведеним нижче кодом.

Він показує підказку з двома кнопками - Так і Ні. Якщо користувач вибирає «Так», зміна буде зроблена, інакше вона буде скасована.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Тоді Ans = MsgBox ("Ви вносите зміни в клітинки в A1: D10. Ви впевнені, що цього хочете?", vbТакНі) Закінчити, якщо Якщо Ans = vbNo То Application.EnableEvents = False Application.Undo Application.EnableEvents = True End Якщо End Sub

У наведеному вище коді ми перевіряємо, чи є клітинка -ціль у перших 4 стовпцях та перших 10 рядках. Якщо це так, відобразиться вікно повідомлення. Крім того, якщо користувач вибрав Ні у вікні повідомлення, зміна скасовується (за допомогою команди Application.Undo).

Зауважте, що я використовував Application.EnableEvents = False перед рядком Application.Undo. І тоді я змінив його, використовуючи Application.EnableEvent = True у наступному рядку.

Це потрібно, оскільки коли відбувається скасування, воно також запускає подію зміни. Якщо я не встановлю для EnableEvent значення False, воно продовжуватиме активувати подію зміни.

Ви також можете контролювати зміни іменованого діапазону за допомогою події зміни. Наприклад, якщо у вас є іменований діапазон під назвою “DataRange” і ви хочете показати запит на випадок, якщо користувач внесе зміни в цей іменований діапазон, ви можете скористатися наведеним нижче кодом:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange As Range Set DRange = Range ("DataRange") If Not Intersect (Target, DRange) is Nothing then MsgBox "Ви щойно змінили діапазон даних" End If End Sub

Наведений вище код перевіряє, чи є у комірці/діапазоні, де ви внесли зміни, будь -які комірки, загальні для діапазону даних. Якщо це так, то відображається вікно повідомлення.

Вибір робочої книгиЗмінити подію

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

Наведений нижче код перерахує аркуш, як тільки ви зміните вибір.

Приватний додаток Sub Worksheet_SelectionChange (ByVal Target As Range) Application.

Інший приклад цієї події - коли потрібно виділити активний рядок і стовпець вибраної комірки.

Щось, як показано нижче:

Наступний код може це зробити:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Кінець на End Sub

Код спочатку видаляє колір фону з усіх комірок, а потім застосовує той, що згадується у коді, до активного рядка та стовпця.

І це проблема з цим кодом. Що він видаляє колір з усіх клітин.

Якщо ви хочете виділити активний рядок/стовпець, зберігаючи колір в інших клітинках недоторканим, скористайтеся технікою, показаною у цьому посібнику.

Робоча книга DoubleClick Подія

Це одна з моїх улюблених подій на робочому аркуші, і ви побачите багато підручників, де я це використовував (наприклад, цей чи цей).

Ця подія запускається, якщо двічі клацнути по клітинці.

Дозвольте мені показати вам, як це круто.

За допомогою наведеного нижче коду ви можете двічі клацнути клітинку, і вона застосує колір фону, змінить колір шрифту та зробить текст у комірці жирним;

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True з ціллю .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End з End Sub

Це може бути корисним, коли ви переглядаєте список клітинок і хочете виділити кілька вибраних. Хоча ви можете використовувати клавішу F4, щоб повторити останній крок, вона зможе застосувати лише один вид форматування. За допомогою цієї події подвійного клацання ви можете застосувати всі три просто двічі.

Зауважте, що у наведеному вище коді я зробив значення Cancel = True.

Це робиться для того, щоб дію подвійного клацання за замовчуванням було вимкнено - це означає, щоб увійти в режим редагування. Якщо Скасувати = Істина, Excel не переведе вас у режим редагування, якщо двічі клацнути по клітинці.

Ось ще один приклад.

Якщо у вас є список справ у Excel, ви можете скористатися подією подвійного клацання, щоб застосувати формат закреслення, щоб позначити завдання як завершене.

Щось, як показано нижче:

Ось код, який буде це робити:

Приватний підробочий аркуш_BeforeDoubleClick (ціль ByVal як діапазон, скасувати як логічне значення) Скасувати = Істинно CurrFormat = Target.Font.Strikethrough Якщо CurrFormat То Target.Font.Strikethrough = False Інше Target.Font.Strikethrough = True End Якщо End Sub

Зауважте, що в цьому коді я двічі клацнув як подію перемикання. Якщо двічі клацнути клітинку, вона перевірить, чи вже застосовано закреслений формат. Якщо це було так, двічі клацніть, щоб видалити формат закреслення, а якщо цього не було, застосовується формат закреслення.

Подія OnTime Excel VBA

Події, які ми бачили досі в цій статті, були пов’язані з одним із об’єктів Excel, будь то робоча книга, аркуш, аркуш діаграми чи UserForms тощо.

Подія OnTime відрізняється від інших подій, оскільки її можна зберігати у звичайному модулі VBA (тоді як інші повинні бути розміщені у вікні коду таких об'єктів, як ThisWorkbook або Worksheets або UserForms).

У межах звичайного модуля VBA він використовується як метод об’єкта програми.

Причина, чому це вважається подією, полягає в тому, що вона може бути ініційована на основі вказаного вами часу. Наприклад, якщо я хочу, щоб аркуш перераховувався кожні 5 хвилин, я можу використовувати для цього подію OnTime.

Або, якщо я хочу показати повідомлення/нагадування в певний час доби, я можу скористатися подією OnTime.

Нижче наведено код, який буде показувати повідомлення о 14:00 щодня.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" Закінчити субпідпис ShowMessage () MsgBox "Час обіду" Кінець Sub

Пам’ятайте, що вам потрібно розмістити цей код у звичайному модулі VBA,

Крім того, хоча подія OnTime буде спрацьовувати у вказаний час, вам потрібно в будь -який час запустити макрос вручну. Після того, як ви запустите макрос, він почекає до 14:00, а потім викликає макрос "ShowMessage".

Тоді макрос ShowMessage відобразить повідомлення.

Подія OnTime бере чотири аргументи:

Application.OnTime (Найраніший час, Процедура, LatestTime, Розклад)

• Найдавніший час: Час, коли ви хочете запустити процедуру.
• Процедура: Назва процедури, яку слід запустити.
• LatestTime (необов’язково): Якщо інший код працює, а вказаний код неможливо запустити у вказаний час, ви можете вказати останній час, на який він повинен чекати. Наприклад, це може бути EarliestTime + 45 (що означає, що він буде чекати 45 секунд, поки інша процедура завершиться). Якщо навіть через 45 секунд процедуру не вдається запустити, її припиняють. Якщо ви не вкажете це, Excel зачекає, поки код можна буде запустити, а потім запустить його.
• Розклад (необов’язково): Якщо встановлено значення True, він планує нову процедуру часу. Якщо значення False, це скасовує раніше встановлену процедуру. За замовчуванням це Істина.

У наведеному вище прикладі ми використовували лише перші два аргументи.

Розглянемо ще один приклад.

Наведений нижче код оновлюватиме аркуш кожні 5 хв.

Затемнити NextRefresh як дату Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Calculate NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End sub Sub StopRefresh () On Error Resume Next Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

Наведений вище код оновлюватиме аркуш кожні 5 хвилин.

Він використовує функцію Now для визначення поточного часу, а потім додає 5 хвилин до поточного часу.

Подія OnTime продовжуватиме працювати, доки ви її не зупините. Якщо ви закриєте книгу, а програма Excel все ще працює (інші книги відкриті), книга, у якій буде запущена подія OnTime, знову відкриється.

Це краще вирішити, спеціально припинивши подію OnTime.

У наведеному вище коді у мене є код StopRefresh, але вам потрібно його виконати, щоб зупинити подію OnTime. Ви можете зробити це вручну, призначити його кнопці та зробити це, натиснувши кнопку або викликаючи її з події Закрити книгу.

Private Sub Workbook_BeforeClose (Скасувати як логічне значення) Call StopRefresh End Sub

Наведений вище код події "BeforeClose" міститься у вікні коду ThisWorkbook.

Подія OnKey Excel VBA

Коли ви працюєте з Excel, він постійно контролює натискання клавіш, які ви використовуєте. Це дозволяє нам використовувати натискання клавіш як тригер для події.

За допомогою події OnKey можна вказати натискання клавіші (або комбінацію натискань клавіш) та код, який слід виконати під час використання цього натискання клавіші. Якщо натиснути ці натискання клавіш, він виконає код для них.

Так само, як подія OnTime, вам потрібно мати спосіб скасувати подію OnKey. Крім того, якщо ви встановите подію OnKey для певного натискання клавіші, вона стане доступною у всіх відкритих книгах.

Перш ніж я покажу вам приклад використання події OnKey, дозвольте мені спочатку поділитися кодами ключів, доступними вам у VBA.

КЛЮЧ	КОД
Backspace	{BACKSPACE} або {BS}
Перерва	{ПЕРЕРВА}
Caps Lock	{CAPSLOCK}
Видалити	{DELETE} або {DEL}
Стрілка вниз	{ВНИЗ}
Кінець	{END}
Введіть	~
Enter (на цифровій клавіатурі)	{ENTER}
Втеча	{ESCAPE} або {ESC}
Додому	{HOME}
Ins	{INSERT}
Стрілка вліво	{LEFT}
Num Lock	{NUM LOCK}
Сторінка вниз	{PGDN}
PageUp	{PGUP}
Стрілка вправо	{RIGHT}
Scroll Lock	{SCROLLOCK}
Вкладка	{TAB}
Стрілка вгору	{UP}
F1 - F15	{F1} - {F15}

Коли вам потрібно використати будь -яку подію на клавіші, вам потрібно використовувати код для неї.

У таблиці вище наведені коди для окремих натискань клавіш.

Ви також можете поєднати їх з такими кодами:

• Зсув: + (Знак плюса)
• Контроль: ^ (Карет)
• Alt: % (Процент)

Наприклад, для Alt F4 вам потрібно використати код: “%{F4}” - де % для клавіші ALT і {F4} для клавіші F4.

Тепер давайте розглянемо приклад (пам’ятайте, що код подій OnKey розміщено у звичайному модулі VBA).

Коли ви натискаєте клавішу PageUp або PageDown, вона перескакує на 29 рядків вище/нижче активної клітинки (принаймні це те, що вона робить на моєму ноутбуці).

Якщо ви хочете, щоб він перескакував лише 5 рядків одночасно, ви можете скористатися наведеним нижче кодом:

Допоміжний PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" Закінчити допоміжний допоміжний PageUpMod () Увімкнено Помилка Відновити наступний ActiveCell.Offset (-5, 0). Sub Sub PageDownMod () On Помилка Відновлення Наступний ActiveCell.Offset (5, 0). Активувати End Sub

Коли ви запускаєте першу частину коду, вона запускатиме події OnKey. Після того, як це буде виконано, використання PageUp і клавіші PageDown призведе до того, що курсор буде перестрибувати лише 5 рядків одночасно.

Зауважте, що ми використовували функцію "Увімкнення помилки далі", щоб переконатися, що помилки ігноруються. Ці помилки можуть виникати під час натискання клавіші PageUp, навіть якщо ви у верхній частині аркуша. Оскільки немає більше рядків для переходу, код покаже помилку. Але оскільки ми використовували «Далі відновити помилку», це буде проігноровано.

Щоб переконатися, що ці події OnKey доступні, вам потрібно запустити першу частину коду. Якщо ви хочете, щоб ця книга стала доступною, як тільки ви відкриєте книгу, ви можете розмістити її у вікні коду ThisWorkbook.

Private Sub Workbook_Open () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

Нижче наведений код поверне ключі до їх нормальних функцій.

Sub Cancel_PageUpDownKeysMod () Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" End Sub

Якщо ви не вказуєте другий аргумент у методі OnKey, він поверне натиснуту клавішу до звичайної функціональності.

Якщо ви хочете скасувати функціональні можливості натискання клавіші, щоб Excel нічого не робив під час використання цього натискання клавіші, вам потрібно використовувати порожній рядок як другий аргумент.

У наведеному нижче коді Excel нічого не зробить, коли ми використовуємо клавіші PageUp або PageDown.

Sub Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" Закінчити суб

Відключення подій у VBA

Іноді вам може знадобитися відключити події, щоб ваш код працював належним чином.

Наприклад, припустимо, що у мене є діапазон (A1: D10), і я хочу показувати повідомлення щоразу, коли клітинку змінюють у цьому діапазоні. Тому я показую вікно повідомлень і запитую користувача, чи впевнений він, що хоче внести зміни. Якщо відповідь "Так", зміна вноситься, а якщо відповідь "Ні", VBA скасовує її.

Ви можете використовувати код нижче:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Тоді Ans = MsgBox ("Ви вносите зміни в клітинки в A1: D10. Ви впевнені, що цього хочете?", vbТакНі) Закінчити, якщо Якщо Ans = vbНі Тоді застосунок. Скасувати завершення, якщо закінчити суб

Проблема з цим кодом полягає в тому, що коли користувач вибирає Ні у вікні повідомлення, дія змінюється (як я використовував Application.Undo).

Коли відбувається скасування і значення повертається до вихідного, знову запускається подія зміни VBA, і користувачеві знову показується те саме вікно повідомлення.

Це означає, що ви можете продовжувати натискати «НІ» у вікні повідомлення, і воно продовжуватиме відображатися. Це відбувається, коли ви застрягли в нескінченному циклі в цьому випадку.

Щоб уникнути таких випадків, вам потрібно відключити події, щоб подія зміни (або будь -яка інша подія) не запускалася.

У цьому випадку такий код буде добре працювати:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Тоді Ans = MsgBox ("Ви вносите зміни в клітинки в A1: D10. Ви впевнені, що цього хочете?", vbТакНі) Закінчити, якщо Якщо Ans = vbNo То Application.EnableEvents = False Application.Undo Application.EnableEvents = True End Якщо End Sub

У наведеному вище коді, прямо над рядком Application.Undo, ми використовували - Application.EnableEvents = False.

Якщо встановити для EnableEvents значення False, це не спричинить жодної події (у поточній або відкритій книзі).

Після того, як ми завершимо операцію скасування, ми можемо повернути властивість EnableEvents на True.

Майте на увазі, що відключення подій впливає на всі книги, які наразі відкриті (або відкриті, коли для EnableEvents встановлено значення False). Наприклад, як частина коду, якщо ви відкриєте нову книгу, подія Відкриття робочої книги не працюватиме.

Вплив подій Скасувати стек

Спочатку дозвольте мені розповісти, що таке Undo Stack.

Коли ви працюєте в Excel, він постійно контролює ваші дії. Коли ви робите помилку, ви завжди можете використовувати Control + Z, щоб повернутися до попереднього кроку (тобто скасувати поточну дію).

Якщо двічі натиснути Control + Z, це поверне вас на два кроки назад. Ці кроки, які ви виконали, зберігаються у складі стеку Undo.

Будь -яка подія, що змінює аркуш, руйнує цей стек скасування.Це означає, що якщо я зробив 5 дій перед тим, як ініціювати подію, я не зможу використовувати Control + Z, щоб повернутися до цих попередніх кроків. Активація події знищила цей стек для мене.

У наведеному нижче коді я використовую VBA для введення мітки часу в клітинку A1, коли на робочому аркуші відбуваються зміни.

Private Sub Worksheet_Change (ByVal Target As Range) Application.EnableEvents = False Range ("A1"). Value = Format (Now, "dd-mmm-yyyy hh: mm: ss") Application.EnableEvents = True End Sub

Оскільки я вношу зміни в аркуш, це знищить стек скасування.

Також зверніть увагу, що це не обмежується лише подіями.

Якщо у вас є код, який зберігається у звичайному модулі VBA, і ви вносите зміни в аркуш, це також знищить стек скасування в Excel.

Наприклад, у наведеному нижче коді просто введіть текст “Hello” у клітинку A1, але навіть запуск цього знищить стек скасування.

Діапазон підтипу Hello () ("A1"). Value = "Hello" End Sub

Вам також можуть сподобатися такі підручники Excel VBA:

• Робота з клітинками та діапазонами в Excel VBA.
• Робота з робочими листами в Excel VBA.
• Робота з робочими книгами в Excel VBA.
• Цикли VBA Excel - Повний посібник.
• Використовуючи IF then Else Statment у Excel VBA.
• Для наступного циклу в Excel.
• Створення визначених користувачем функцій у Excel VBA.
• Як створювати та використовувати надбудови в Excel.
• Створюйте та повторно використовуйте макроси, зберігаючи їх у особистій книзі макросів.