Събития на 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.
5. Събития в диаграмата: Това са събития, свързани с листа с диаграми. Лист с диаграма е различен от работен лист (където повечето от нас са свикнали да работят в Excel). Целта на листа с диаграма е да държи диаграма. Примери за такива събития биха включвали промяна на поредицата на диаграмата или преоразмеряване на диаграмата.
6. Събития OnTime и OnKey: Това са две събития, които не се вписват в нито една от горните категории. Затова ги изброих отделно. Събитието „OnTime“ ви позволява да изпълните код в определено време или след изтичане на определено време. Събитието „OnKey“ ви позволява да изпълните код, когато се използва конкретно натискане на клавиш (или комбинация от клавиши).

Къде да поставите свързания със събитието код

В горния раздел разгледах различните видове събития.

В зависимост от типа на събитието, трябва да поставите кода в съответния обект.

Например, ако това е събитие, свързано с работен лист, то трябва да влезе в прозореца на кода на обекта на работния лист. Ако е свързана с работна книга, тя трябва да влезе в прозореца на кода за обект на работна книга.

Във VBA различни обекти - като работни листове, работни книги, таблици, потребителски формуляри и т.н., имат свои собствени кодови прозорци. Трябва да поставите кода на събитието в прозореца на кода на съответния обект. Например - ако това е събитие на ниво работна книга, тогава трябва да имате кода на събитието в прозореца с кода на работната книга.

Следните раздели обхващат местата, където можете да поставите кода на събитието:

В прозореца с код на работен лист

Когато отворите VB Editor (използвайки клавишната комбинация ALT + F11), ще забележите обекта на работните листове в Project Explorer. За всеки работен лист в работната книга ще видите един обект.

Когато щракнете двукратно върху обекта на работния лист, в който искате да поставите кода, той ще отвори прозореца с кода за този работен лист.

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

За да направите това, първо трябва да изберете работен лист от падащото меню в горния ляв ъгъл на прозореца с кода.

След като изберете работен лист от падащото меню, получавате списък на всички събития, свързани с работния лист. Можете да изберете този, който искате да използвате от падащото меню в горния десен ъгъл на прозореца с кода.

Веднага след като изберете събитието, то автоматично ще въведе първия и последния ред на кода за избраното събитие. Сега можете да добавите кода си между двата реда.

Забележка: Веднага след като изберете Работен лист от падащото меню, ще забележите, че в кодовия прозорец се появяват два реда код. След като сте избрали събитието, за което искате кода, можете да изтриете редовете, които се появиха по подразбиране.

Имайте предвид, че всеки работен лист има собствен прозорец с код. Когато поставите кода за Sheet1, той ще работи само ако събитието се случи в Sheet1.

В този прозорец с кодове на тази работна книга

Точно като работни листове, ако имате код на събитие на ниво работна книга, можете да го поставите в прозореца с код на тази работна книга.

Когато щракнете двукратно върху ThisWorkbook, той ще отвори прозореца с кода за него.

Трябва да изберете Работна книга от падащото меню в горния ляв ъгъл на кодовия прозорец.

След като изберете Работна книга от падащото меню, получавате списък на всички събития, свързани с Работната книга. Можете да изберете този, който искате да използвате от падащото меню в горния десен ъгъл на прозореца с кода.

Веднага след като изберете събитието, то автоматично ще въведе първия и последния ред на кода за избраното събитие. Сега можете да добавите кода си между двата реда.

Забележка: Веднага щом изберете Работна книга от падащото меню, ще забележите, че в кодовия прозорец се появяват два реда код. След като сте избрали събитието, за което искате кода, можете да изтриете редовете, които се появиха по подразбиране.

В прозореца за код на потребителска форма

Когато създавате UserForms в Excel, можете също да използвате UserForm събития за изпълнение на кодове въз основа на конкретни действия. Например, можете да посочите код, който се изпълнява при щракване на бутона.

Докато обектите Sheet и ThisWorkbook вече са налични, когато отворите VB Editor, UserForm е нещо, което трябва първо да създадете.

За да създадете UserForm, щракнете с десния бутон върху някой от обектите, отидете на Insert и щракнете върху UserForm.

Това ще вмъкне обект UserForm в работната книга.

Когато щракнете двукратно върху UserForm (или който и да е от обекта, който добавяте към UserForm), той ще отвори прозореца с код за UserForm.

Сега, също като работни листове или ThisWorkbook, можете да изберете събитието и то ще вмъкне първия и последния ред за това събитие. И тогава можете да добавите кода в средата му.

В прозореца с код на диаграма

В Excel можете също да вмъкнете листове с диаграми (които са различни от работни листове). Листът с диаграма трябва да съдържа само диаграми.

Когато вмъкнете лист с диаграма, ще можете да видите обекта от лист с диаграми във VB Editor.

Можете да добавите кода на събитието към прозореца с кодове на диаграма, точно както направихме в работния лист.

Щракнете двукратно върху обекта лист диаграма в Project Explorer. Това ще отвори прозореца с кодове за листа с диаграми.

Сега трябва да изберете Диаграма от падащото меню в горния ляв ъгъл на прозореца с кода.

След като изберете диаграма от падащото меню, получавате списък на всички събития, свързани с листа с диаграми. Можете да изберете този, който искате да използвате от падащото меню в горния десен ъгъл на прозореца с кода.

Забележка: Веднага след като изберете диаграма от падащото меню, ще забележите, че в кодовия прозорец се появяват два реда код. След като сте избрали събитието, за което искате кода, можете да изтриете редовете, които се появиха по подразбиране.

В клас модул

Класовите модули трябва да бъдат вмъкнати точно като 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 End Sub

Горният код използва аргумента Sh, който е дефиниран като тип обект. Аргументът Sh може да бъде работен лист или лист с диаграма, тъй като горното събитие се задейства, когато се добави нов лист.

Присвоявайки новия лист, добавен към работната книга, към променливата на обекта Sh, VBA ни позволи да го използваме в кода. Така че, за да се позова на името на новия лист, мога да използвам Sh.Name.

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

Събития на ниво работна книга (обяснено с примери)

По -долу са най -често използваните събития в работна книга.

ИМЕ НА СЪБИТИЕТО	КАКВО ПРЕДИМСТВА СЪБИТИЕТО
Активирате	Когато е активирана работна книга
AfterSave	Когато работна книга е инсталирана като добавка
Преди Запазване	При запазване на работна книга
ПредиЗатвори	Когато работната книга е затворена
Преди Печат	Когато се отпечатва работна книга
Деактивирайте	Когато работната книга е деактивирана
Нов лист	Когато се добави нов лист
Отворено	Когато се отвори работна книга
SheetActivate	Когато всеки лист в работната книга е активиран
SheetBeforeDelete	Когато всеки лист е изтрит
SheetBeforeDoubleClick	Когато всеки лист е щракнат двукратно
SheetBeforeRightClick	Когато някой лист е щракнат с десен бутон
SheetCalculate	Когато всеки лист се изчислява или преизчислява
SheetDeactivate	Когато работната книга е деактивирана
SheetPivotTableUpdate	Когато се актуализира работна книга
SheetSelectionChange	При смяна на работна книга
WindowActivate	Когато е активирана работна книга
WindowDeactivate	Когато работната книга е деактивирана

Имайте предвид, че това не е пълен списък. Пълният списък можете да намерите тук.

Не забравяйте, че кодът за събитие на работна книга се съхранява в прозореца за код на обекти на тази работна книга.

Сега нека да разгледаме някои полезни служебни събития и да видим как те могат да бъдат използвани в ежедневната ви работа.

Работна книга Отворено събитие

Да приемем, че искате да покажете на потребителя приятелско напомняне да попълни техните разписания, когато отварят конкретна работна книга.

Можете да използвате кода по -долу, за да направите това:

Private Sub Workbook_Open () MsgBox "Не забравяйте да попълните табела за време" End Sub

Сега, веднага щом отворите работната книга с този код, тя ще ви покаже поле за съобщение с посоченото съобщение.

Има няколко неща, които трябва да знаете, когато работите с този код (или кодовете на събитията в работната книга като цяло):

• Ако работната книга има макрос и искате да я запишете, трябва да я запишете във формат .XLSM. В противен случай кодът на макроса ще бъде загубен.
• В горния пример кодът на събитието ще се изпълнява само когато са разрешени макросите. Може да видите жълта лента с искане за разрешение за активиране на макроси. Докато това не е разрешено, кодът на събитието не се изпълнява.
• Кодът на събитието на работната книга се поставя в кодовия прозорец на обекта ThisWorkbook.

Можете допълнително да прецизирате този код и да покажете съобщението само за петък.

Кодът по -долу ще направи това:

Private Sub Workbook_Open () wkday = Делничен ден (Дата) Ако wkday = 6 Тогава MsgBox "Не забравяйте да попълните таблицата с времена" End Sub

Имайте предвид, че във функцията „Делничен ден“ неделята се присвоява на стойност 1, понеделник е 2 и така нататък.

Затова за петък използвах 6.

Събитието „Отворена работна книга“ може да бъде полезно в много ситуации, като например:

• Когато искате да покажете приветствено съобщение на човека, когато се отвори работна книга.
• Когато искате да покажете напомняне при отваряне на работната книга.
• Когато искате винаги да активирате един конкретен работен лист в работната книга, когато тя се отвори.
• Когато искате да отворите свързани файлове заедно с работната книга.
• Когато искате да заснемете датата и часа при всяко отваряне на работната книга.

Събитие NewSheet на работната книга

Събитието NewSheet се задейства, когато вмъкнете нов лист в работната книга.

Да приемем, че искате да въведете стойността на датата и часа в клетка A1 на ново вмъкнатия лист. Можете да използвате кода по -долу, за да направите това:

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Resume Next Sh.Range ("A1") = Format (Now, "dd-mmm-yyyy hh: mm: ss") End Sub

Горният код използва „Следващо възобновяване на грешката“ за обработка на случаи, когато някой вмъква лист с диаграма, а не работен лист. Тъй като листът с диаграма няма клетка A1, той ще покаже грешка, ако не се използва „On Error Resume Next“.

Друг пример може да бъде, когато искате да приложите някои основни настройки или форматиране към нов лист веднага след добавянето му. Например, ако искате да добавите нов лист и искате той автоматично да получи сериен номер (до 100), тогава можете да използвате кода по -долу.

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Resume Next With Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vb Бял край с For i = 1 до 100 Sh.Range ("A1"). Offset (i, 0) .Value = i Next i Sh.Range ("A1", Range ("A1"). End (xlDown)). Borders.LineStyle = xlContinuous End Sub

Горният код също прави малко форматиране. Той придава на клетката на заглавката син цвят и прави шрифта бял. Той също така прилага граница към всички запълнени клетки.

Горният код е пример за това как кратък VBA код може да ви помогне да откраднете няколко секунди всеки път, когато вмъквате нов работен лист (в случай, че това е нещо, което трябва да правите всеки път).

Работна книга Преди събитие

Преди да се задейства събитие Запазване, когато запазвате работна книга. Обърнете внимание, че събитието първо се задейства и след това работната книга се записва.

При запазване на работна книга на Excel може да има два възможни сценария:

1. Запазвате го за първи път и той ще покаже диалоговия прозорец Запиши като.
2. Вече сте го запазили по -рано и той просто ще запише и замени промените във вече запазената версия.

Сега нека да разгледаме няколко примера, където можете да използвате събитието BeforeSave.

Да предположим, че имате нова работна книга, която запазвате за първи път, и искате да напомните на потребителя да я запише в K устройството, тогава можете да използвате кода по -долу:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI като Boolean, Cancel As Boolean) If SaveAsUI Тогава MsgBox "Запазване на този файл в K Drive" End Sub

В горния код, ако файлът никога не е бил запазен, SaveAsUI е True и извежда диалоговия прозорец Save As. Горният код ще покаже съобщението, преди да се появи диалоговият прозорец Запиши като.

Друг пример може да бъде актуализиране на датата и часа, когато файлът е записан в определена клетка.

Кодът по -долу би вмъкнал датата и часа в клетка A1 на Sheet1 всеки път, когато файлът се запише.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI като Boolean, Cancel As Boolean) Работни листове ("Sheet1"). Диапазон ("A1") = Формат (Сега, "dd-mmm-yyyy hh: mm: ss") End Sub

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

Работна книга Преди събитие

Преди да се случи събитие Close, точно преди затварянето на работната книга.

Кодът по -долу защитава всички работни листове преди затварянето на работната книга.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Dim sh As Worksheet for each sh В ThisWorkbook .Worksheets sh.Protect Next sh End Sub

Не забравяйте, че кодът на събитието се задейства веднага щом затворите работната книга.

Едно важно нещо, което трябва да знаете за това събитие, е, че не му пука дали работната книга всъщност е затворена или не.

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

Работна книга преди събитие

Когато дадете команда за печат (или команда за предварителен преглед), се задейства събитието Преди печат.

Кодът по -долу ще преизчисли всички работни листове, преди да се отпечата вашата работна книга.

Private Sub Workbook_BeforePrint (Cancel As Boolean) За всяка ws в работни листове ws.Calculate Next ws End Sub

Когато потребителят отпечатва работната книга, събитието ще се задейства независимо дали той/тя отпечатва цялата работна книга или само част от нея.

Друг пример по -долу е кодът, който би добавил датата и часа в долния колонтитул, когато се отпечата работната книга.

Private Sub Workbook_BeforePrint (Cancel As Boolean) Dim ws като работен лист за всеки ws в ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Отпечатано на-" & Форматиране (Сега, "dd-mmm-yyyy hh: mm")

Събития на ниво работен лист (обяснено с примери)

Събитията на работния лист се извършват въз основа на тригерите в работния лист.

По -долу са най -често използваните събития в работен лист.

Име на събитието	Какво предизвиква събитието
Активирате	Когато работният лист е активиран
Преди Изтриване	Преди изтриването на работния лист
ПредиDoubleClick	Преди двукратно щракване върху работния лист
Преди Прав Клик	Преди да щракнете с десен бутон върху работния лист
Изчисли	Преди работният лист да бъде изчислен или преизчислен
Промяна	Когато клетките в работния лист се променят
Деактивирайте	Когато работният лист е деактивиран
PivotTableUpdate	Когато обобщената таблица в работния лист се актуализира
SelectionChange	Когато се промени изборът на работния лист

Имайте предвид, че това не е пълен списък. Пълният списък можете да намерите тук.

Не забравяйте, че кодът за събитие на работен лист се съхранява в прозореца с код на обекта на работния лист (в този, в който искате събитието да се задейства). В работна книга може да има няколко работни листа и вашият код ще се задейства само когато събитието се проведе в работния лист, в който е поставено.

Сега нека да разгледаме някои полезни събития от работен лист и да видим как те могат да бъдат използвани в ежедневната ви работа.

Работен лист Активиране на събитие

Това събитие се задейства, когато активирате работен лист.

Кодът по -долу премахва защитата на лист веднага след активирането му.

Private Sub Worksheet_Activate () ActiveSheet.Unprotect End Sub

Можете също да използвате това събитие, за да сте сигурни, че определена клетка или диапазон от клетки (или именован диапазон) е избрана веднага след като активирате работния лист. Кодът по -долу би избрал клетка D1 веднага щом активирате листа.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Изберете End Sub

Събитие за смяна на работен лист

Събитие за промяна се задейства всеки път, когато направите промяна в работния лист.

Е … не винаги.

Има някои промени, които предизвикват събитието, а други не. Ето списък на някои промени, които няма да предизвикат събитието:

• Когато промените форматирането на клетката (размер на шрифта, цвят, рамка и т.н.).
• Когато обединявате клетки. Това е изненадващо, тъй като понякога обединяването на клетки също премахва съдържанието от всички клетки, с изключение на горната лява.
• Когато добавяте, изтривате или редактирате коментар в клетка.
• Когато сортирате диапазон от клетки.
• Когато използвате Търсене на цел.

Следните промени биха задействали събитието (въпреки че може да мислите, че не трябва):

• Форматирането на копиране и поставяне ще задейства събитието.
• Изчистването на форматирането би задействало събитието.
• Извършването на проверка на правописа ще задейства събитието.

По -долу е код, който ще покаже поле за съобщение с адреса на клетката, която е променена.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox „Току -що променихте“ & 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. Сигурни ли сте, че го искате?", vbYesNo) Край Ако Ако Ans = vbNo Тогава Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

В горния код проверяваме дали клетката Target е в първите 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

Горният код проверява дали клетката/диапазонът, в който сте направили промените, има общи клетки за обхвата на данните. Ако е така, показва полето със съобщение.

Избор на работна книга Промяна на събитие

Събитието за промяна на селекцията се задейства винаги, когато има промяна в селекцията в работния лист.

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

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Приложение. Изчисляване на края на Sub

Друг пример за това събитие е, когато искате да маркирате активния ред и колона на избраната клетка.

Нещо, както е показано по -долу:

Следният код може да направи това:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173). End Sub

Кодът първо премахва цвета на фона от всички клетки и след това прилага този, споменат в кода, към активния ред и колона.

И това е проблемът с този код. Че премахва цвета от всички клетки.

Ако искате да подчертаете активния ред/колона, като запазите цвета в други клетки непокътнат, използвайте техниката, показана в този урок.

Работна книга DoubleClick събитие

Това е едно от любимите ми събития на работен лист и ще видите много уроци, където съм използвал това (като този или този).

Това събитие се задейства, когато щракнете двукратно върху клетка.

Нека ви покажа колко страхотно е това.

С кода по-долу можете да щракнете двукратно върху клетка и тя ще приложи цвят на фона, ще промени цвета на шрифта и ще направи текста в клетката удебелен;

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True With Target .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End With End Sub

Това може да бъде полезно, когато преминавате през списък с клетки и искате да подчертаете няколко избрани. Въпреки че можете да използвате клавиша F4, за да повторите последната стъпка, той би могъл да приложи само един вид форматиране. С това събитие с двойно щракване можете да приложите и трите само с двойно щракване.

Обърнете внимание, че в горния код направих стойността на Cancel = True.

Това се прави така, че действието по подразбиране на двойното щракване е деактивирано - това е да влезете в режим на редактиране. С Cancel = True, Excel няма да ви въведе в режим на редактиране, когато щракнете двукратно върху клетката.

Ето още един пример.

Ако имате списък със задачи в Excel, можете да използвате двукратно щракване върху събитие, за да приложите зачеркнатия формат, за да маркирате задачата като завършена.

Нещо, както е показано по -долу:

Ето кода, който ще направи това:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat then Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End If End Sub

Обърнете внимание, че в този код направих двукратно щракване като събитие за превключване. Когато щракнете двукратно върху клетка, тя проверява дали зачеркнатият формат вече е приложен. Ако е имало, щракнете двукратно, за да премахнете зачеркнатия формат, а ако не е, тогава се прилага зачеркнатият формат.

Събитие OnTime на Excel VBA

Събитията, които видяхме досега в тази статия, бяха свързани с един от обектите на Excel, било то работна книга, работен лист, лист с диаграми или потребителски формуляри и т.н.

Събитието OnTime е различно от другите събития, тъй като може да се съхранява в обикновения модул VBA (докато другите трябваше да бъдат поставени в кодовия прозорец на обекти като ThisWorkbook или Worksheets или UserForms).

В рамките на обикновения модул VBA той се използва като метод на обекта на приложение.

Причината това да се счита за събитие е, че може да се задейства въз основа на посочения от вас час. Например, ако искам листът да се преизчислява на всеки 5 минути, мога да използвам събитието OnTime за него.

Или, ако искам да покажа съобщение/напомняне в определен час от деня, мога да използвам събитието OnTime.

По -долу е код, който ще показва съобщение всеки ден в 14 часа.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" Край на Sub Sub ShowMessage () MsgBox "Време е за обяд" End Sub

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

Също така, докато събитието OnTime ще се задейства в определеното време, трябва да стартирате макроса ръчно по всяко време. След като стартирате макроса, той ще изчака 14 часа и след това ще извика макроса „ShowMessage“.

След това макросът ShowMessage ще покаже съобщението.

Събитието OnTime приема четири аргумента:

Application.OnTime (Най -ранно време, Процедура, Последно време, График)

• Най -ранен час: Времето, когато искате да изпълните процедурата.
• Процедура: Името на процедурата, която трябва да бъде изпълнена.
• LatestTime (по избор): В случай, че се изпълнява друг код и посоченият код не може да бъде изпълнен в определеното време, можете да посочите LatestTime, за който трябва да изчака. Например, това може да е EarliestTime + 45 (което означава, че ще изчака 45 секунди, за да приключи другата процедура). Ако дори след 45 секунди процедурата не може да се изпълни, тя се изоставя. Ако не посочите това, Excel ще изчака, докато кодът може да бъде стартиран, и след това го стартира.
• График (по избор): Ако е зададено на True, той планира нова времева процедура. Ако е невярно, то отменя предварително зададената процедура. По подразбиране това е Истина.

В горния пример използвахме само първите два аргумента.

Нека разгледаме друг пример.

Кодът по -долу ще опреснява работния лист на всеки 5 минути.

Dim 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 (Cancel As Boolean) Call StopRefresh End Sub

Горният код на събитието „BeforeClose“ отива в прозореца с код на ThisWorkbook.

Събитие OnKey на Excel VBA

Когато работите с Excel, той непрекъснато следи натисканията на клавиши, които използвате. Това ни позволява да използваме натискания на клавиши като спусък за събитие.

Със събитието OnKey можете да посочите натискане на клавиш (или комбинация от натискания на клавиши) и кода, който трябва да се изпълни, когато този натиск на клавиш се използва. Когато тези натискания на клавиши са натиснати, той ще изпълни кода за него.

Точно като събитието OnTime, трябва да имате начин да отмените събитието OnKey. Също така, когато зададете събитието OnKey за конкретно натискане на клавиш, то става достъпно във всички отворени работни книги.

Преди да ви покажа пример за използване на събитието OnKey, позволете ми първо да споделя ключовите кодове, които са ви достъпни във VBA.

КЛЮЧ	КОД
Backspace	{BACKSPACE} или {BS}
Прекъсване	{BREAK}
Клавиш за главни букви	{КЛАВИШ ЗА ГЛАВНИ БУКВИ}
Изтрий	{DELETE} или {DEL}
Стрелка надолу	{НАДОЛУ}
Край	{КРАЙ}
Въведете	~
Enter (на нуричната клавиатура)	{ENTER}
Бягство	{ESCAPE} или {ESC}
У дома	{У ДОМА}
Ins	{INSERT}
Лява стрелка	{НАЛЯВО}
NumLock	{NUMLOCK}
PageDown	{PGDN}
Страница нагоре	{PGUP}
Дясна стрелка	{RIGHT}
Scroll Lock	{SCROLLOCK}
Раздел	{РАЗДЕЛ}
Стрелка нагоре	{UP}
F1 до F15	{F1} до {F15}

Когато трябва да използвате някакво събитие onkey, трябва да използвате кода за него.

Горната таблица съдържа кодовете за единични натискания на клавиши.

Можете също да ги комбинирате със следните кодове:

• Смяна: + (Знак плюс)
• Контрол: ^ (Карет)
• Alt: % (Процент)

Например, за Alt F4, трябва да използвате кода: „%{F4}” - където % е за клавиша ALT и {F4} е за клавиша F4.

Сега нека да разгледаме един пример (не забравяйте, че кодът за събития OnKey е поставен в обикновения модул VBA).

Когато натиснете клавиша PageUp или PageDown, той прескача 29 реда над/под активната клетка (поне това прави на моя лаптоп).

Ако искате да прескача само 5 реда наведнъж, можете да използвате кода по -долу:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" Прекратете Sub Sub PageUpMod () On Error Resume Next ActiveCell.Offset (-5, 0). Sub Sub PageDownMod () On Error Resume Next ActiveCell.Offset (5, 0). Активирайте End Sub

Когато стартирате първата част на кода, тя ще стартира събитията OnKey. След като това се изпълни, използването на PageUp и клавиша PageDown ще накара курсора да прескача само 5 реда наведнъж.

Имайте предвид, че сме използвали „Следващо възобновяване на грешката“, за да сме сигурни, че грешките са игнорирани. Тези грешки могат да възникнат, когато натиснете клавиша PageUp, дори когато сте в горната част на работния лист. Тъй като няма повече редове за прескачане, кодът ще покаже грешка. Но тъй като сме използвали „Следващо възобновяване на грешката“, това ще бъде игнорирано.

За да сте сигурни, че тези OnKey събития са налични, трябва да изпълните първата част на кода. В случай, че искате това да е достъпно веднага щом отворите работната книга, можете да я поставите в прозореца с кодове на тази работна книга.

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.

Под Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" End Sub

Деактивиране на събития във 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Не След това Приложение. Отмяна Край Ако край Sub

Проблемът с този код е, че когато потребителят избере Не в полето за съобщение, действието се обръща (както използвах Application.Undo).

Когато се отмени и стойността се промени обратно към първоначалната, събитието за смяна на VBA отново се задейства и на потребителя отново се показва същото поле за съобщение.

Това означава, че можете да продължите да щраквате върху НЕ в полето за съобщение и то ще продължи да се показва. Това се случва, тъй като в този случай сте заседнали в безкрайния цикъл.

За да избегнете такива случаи, трябва да деактивирате събития, така че събитието за промяна (или всяко друго събитие) да не се задейства.

Следният код би работил добре в този случай:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Тогава Ans = MsgBox ("Правите промяна в клетки в A1: D10. Сигурни ли сте, че го искате?", vbYesNo) Край Ако Ако Ans = vbNo Тогава Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

В горния код, точно над реда Application.Undo, сме използвали - Application.EnableEvents = False.

Задаването на EnableEvents на False няма да задейства никакво събитие (в текущата или отворените работни книги).

След като приключим операцията за отмяна, можем да превключим обратно свойството EnableEvents на True.

Имайте предвид, че деактивирането на събития влияе върху всички работни книги, които в момента са отворени (или отворени, докато EnableEvents е зададено на False). Например, като част от кода, ако отворите нова работна книга, събитието Отворена работна книга няма да работи.

Въздействие на събития Undo Stack

Нека първо ви кажа какво е Undo Stack.

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

Ако натиснете Control + Z два пъти, това ще ви върне две стъпки назад. Тези стъпки, които сте извършили, се съхраняват като част от стека за отмяна.

Всяко събитие, което променя работния лист, унищожава този стек за отмяна.Това означава, че ако съм направил 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.

Например, кодът по -долу просто въвежда текста „Здравей“ в клетка A1, но дори изпълнението на това би унищожило стека за отмяна.

Sub TypeHello () Range ("A1"). Value = "Hello" End Sub

Може да харесате и следните уроци по Excel VBA:

• Работа с клетки и диапазони в Excel VBA.
• Работа с работни листове в Excel VBA.
• Работа с работни книги в Excel VBA.
• Цикли на Excel VBA - Най -доброто ръководство.
• Използване на IF след това Друг статистика в Excel VBA.
• За следващия цикъл в Excel.
• Създаване на дефинирани от потребителя функции в Excel VBA.
• Как да създавате и използвате добавки в Excel.
• Създавайте и използвайте повторно макроси, като ги запазвате в лична работна книга за макроси.