Събитията се случват през цялото време, когато потребител отваря работна книга на Excel и започва да извършва различни действия, като например въвеждане на данни в клетки или преместване между листове
В редактора на Visual Basic (ALT+F11) вече са настроени подпрограми, които могат да бъдат уволнени, когато потребителят направи нещо, напр. въвеждане на данни в клетка. Подпрограмата не предоставя никакъв код за действие, а само оператор „Sub“ и „End Sub“ без код между тях. Те са в латентно състояние, така че нищо не се случва, докато не въведете някакъв код.
Ето пример, базиран на събитието „Промяна“ в работен лист:
Като VBA програмист можете да добавите код, за да направите някои неща да се случат, когато потребителят предприеме конкретно действие. Това ви дава възможност да контролирате потребителя и да му попречите да предприема действия, които не искате от тях и които могат да повредят работната ви книга. Например може да искате да запазят собственото си индивидуално копие на работната книга под друго име, така че да не засягат оригинала, който може да се използва от редица потребители.
Ако затворят работната книга, те автоматично ще бъдат подканени да запазят промените си. Работната книга обаче има събитие „BeforeClose“ и можете да въведете код, за да предотвратите затварянето на работната книга и задействане на събитие „Запазване“. След това можете да добавите бутон към самия работен лист и да поставите своя собствена програма „Запазване“. Можете също да деактивирате рутината „Запазване“, като използвате събитието „Преди запазване“
Разбирането за това как работят събитията е абсолютно необходимо за VBA програмист.
Видове събития
Работна тетрадка Събития - тези събития се задействат въз основа на това, което потребителят прави със самата работна книга. Те включват действия на потребителя, като отваряне на работната книга, затваряне на работна книга, запазване на работната книга, добавяне или изтриване на лист
Събития на работен лист - тези събития се задействат от потребител, предприемащ действия върху конкретен работен лист. Всеки работен лист в работната книга има отделен кодов модул, който съдържа различни събития специално за този работен лист (не за всички работни листове). Те включват действия на потребителя, като например промяна на съдържанието на клетка, двойно щракване върху клетка или щракване с десен бутон върху клетка.
Събития за активен X контрол - Активните контроли X могат да се добавят към работен лист с помощта на иконата „Вмъкване“ в раздела „Разработчик“ в лентата на Excel. Това често са бутони за управление, които позволяват на потребителя да предприема различни действия под контрола на вашия код, но те също могат да бъдат обекти като падащи менюта. Използването на Active X контроли за разлика от контролите за формуляри на работния лист дава пълна възможност за програмиране. Активните контроли X ви дават много по -голяма гъвкавост от гледна точка на програмирането спрямо използването на контролите за формуляри в работен лист.
Например, можете да имате два падащи контрола на вашия работен лист. Искате наличният списък във второто падащо меню да се основава на това, което потребителят е избрал в първото падащо меню. Използвайки събитието „Промяна“ на първото падащо меню, можете да създадете код, за да прочетете какво е избрал потребителят и след това да актуализирате второто падащо меню. Можете също така да деактивирате второто падащо меню, докато потребителят направи избор в първото падащо меню
Събития на UserForm - Можете да вмъкнете и проектирате професионално изглеждаща форма, която да използвате като изскачащ прозорец. Всички контроли, които поставяте във формуляра си, са Active X контроли и те имат същите събития като контролите Active X, които можете да поставите в работен лист
Събития в диаграма - Тези събития са свързани само с лист с диаграма, а не с диаграма, която се появява като част от работен лист. Тези събития включват преоразмеряване на диаграмата или избор на диаграмата.
Събития за кандидатстване - Те използват обекта Application във VBA. Примерите биха позволили кодът да се задейства, когато се натисне определен клавиш или когато се достигне определено време. Можете да програмирате ситуация, при която работната книга да е отворена денонощно и да импортира данни от външен източник за една нощ в предварително определен час.
Опасности от използването на код в събития
Когато пишете код, за да направите нещо, когато потребителят предприема определено действие, трябва да имате предвид, че вашият код може да задейства други събития, които биха могли да поставят кода ви в непрекъснат цикъл.
Да предположим например, че използвате събитието „Промяна“ в работен лист, така че когато потребителят постави стойност в клетка, изчисление въз основа на тази клетка се поставя в клетката непосредствено вдясно от нея.
Проблемът тук е, че поставянето на изчислената стойност в клетката задейства друго събитие „Промяна“, което след това от своя страна задейства още едно събитие „Промяна“ и така нататък, докато кодът ви изтече от колони за използване и изхвърля нагоре съобщение за грешка.
Трябва да помислите внимателно, когато пишете кода за събитието, за да сте сигурни, че други събития няма да бъдат задействани по невнимание
Деактивиране на събития
Можете да използвате код, за да деактивирате събитията, за да заобиколите този проблем. Това, което ще трябва да направите, е да включите код, за да деактивирате събития, докато кодът на вашето събитие се изпълнява, и след това да разрешите отново събития в края на кода. Ето пример за това как да го направите:
| 1234 | Sub DisableEvents ()Application.EnableEvents = FalseApplication.EnableEvents = ВярноEnd Sub |
Имайте предвид, че това деактивира всички събития в цялото приложение на Excel, така че това би повлияло и на други функции в Excel. Ако използвате това по някаква причина, уверете се, че след това събитията се включват отново.
Значението на параметрите в събитията
Събитията обикновено имат параметри, които можете да използвате, за да научите повече за това, което потребителят прави и местоположението на клетката, в което се намират.
Например събитието за смяна на работния лист изглежда така:
| 1 | Частен под работен лист_Смяна (ByVal Target As Range) |
Използвайки обекта range, можете да разберете координатите на клетъчния ред/колона, в които всъщност е потребителят.
| 1234 | Частен под работен лист_Смяна (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowEnd Sub |
Ако искате кодът ви да работи само с определена колона или номер на ред, тогава добавяте условие, което излиза от подпрограмата, ако колоната не е необходимата.
| 123 | Частен под работен лист_Смяна (ByVal Target As Range)Ако Target.Column 2 След това излезте от SubEnd Sub |
Това заобикаля проблема с кода, който задейства множество събития, тъй като той ще работи само ако потребителят е променил клетка в колона 2 (колона B)
Примери за събития от работна книга (не са изчерпателни)
Събитията от работната книга се намират под обекта „ThisWorkbook“ във VBE Project Explorer. Ще трябва да изберете „Работна книга“ при първото падащо меню в прозореца на кода и след това второто падащо меню ще ви покаже всички налични събития
Работна книга Отворено събитие
Това събитие се задейства всеки път, когато работната книга е отворена от потребител. Можете да го използвате, за да изпратите приветствено съобщение до потребител, като заснемете неговото потребителско име
| 123 | Private Sub Workbook_Open ()MsgBox "Добре дошли" & Application.UserNameEnd Sub |
Можете също да проверите потребителското им име в списък, който се държи на скрит лист, за да видите дали са упълномощени да имат достъп до работната книга. Ако те не са оторизиран потребител, можете да покажете съобщение и да затворите работната книга, така че да не могат да я използват.
Събитие Нов лист на работната книга
Това събитие се задейства, когато потребител добави нов лист към работната книга
Можете да използвате този код, само за да си позволите да добавите нов лист, вместо да накарате различни потребители да добавят листове и да объркат работната книга
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh As Object)Application.DisplayAlerts = FalseАко Application.UserName "Ричард" ТогаваSh.DeleteКрай АкоApplication.DisplayAlerts = ВярноEnd Sub |
Имайте предвид, че трябва да изключите сигналите, тъй като предупреждението на потребителя ще се появи, когато листът бъде изтрит, което позволява на потребителя да заобиколи вашия код. Уверете се, че след това отново сте включили сигналите!
Уморени ли сте от търсене на примери за VBA код? Опитайте AutoMacro!
Работна книга преди записване на събитие
Това събитие се задейства, когато потребителят кликне върху иконата „Запазване“, но преди действително да се извърши „Запазване“
Както е описано по -рано, може да искате да попречите на потребителите да запазват промените си в оригиналната работна книга и да ги принудите да създадат нова версия, като използвате бутон на работния лист. Всичко, което трябва да направите, е да промените параметъра „Cancel“ на True и работната книга никога не може да бъде запазена по конвенционалния метод.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI като Boolean, Cancel As Boolean)Отказ = ВярноEnd Sub |
Работна книга преди затваряне на събитието
Можете да използвате това събитие, за да предотвратите затварянето на работната книга от потребителите и отново да ги принудите да излязат чрез бутона на работен лист. Отново задавате параметъра „Cancel“ на „True“. Червеният X в горния десен ъгъл на прозореца на Excel вече не работи.
| 123 | Private Sub Workbook_BeforeClose (Отказ като булев)Отказ = ВярноEnd Sub |
Примери за събития на работен лист (не са изчерпателни)
Събитията на работния лист се намират под конкретния обект с име на лист във VBE Project Explorer. Ще трябва да изберете „Работен лист“ в първото падащо меню в прозореца на кода и след това второто падащо меню ще ви покаже всички налични събития
Събитие за смяна на работен лист
Това събитие се задейства, когато потребител направи промяна в работен лист, като например въведе нова стойност в клетка
Можете да използвате това събитие, за да поставите допълнителна стойност или коментар до променената клетка, но както беше обсъдено по -рано, не искате да започнете да задавате цикъл от събития.
| 12345 | Частен под работен лист_Смяна (ByVal Target As Range)Ако Target.Column 2 След това излезте от SubActiveSheet.Cells (Target.Row, Target.Column + 1). Стойност = _ActiveSheet.Cells (Target.Row, Target.Column). Стойност * 1.1End Sub |
В този пример кодът ще работи само ако стойността е въведена в колона В (колона 2). Ако това е вярно, то ще добави 10% към номера и ще го постави в следващата налична клетка
Работен лист преди събитие с двойно щракване
Това събитие ще задейства кода, ако потребител кликне два пъти върху клетка. Това може да бъде изключително полезно за финансови отчети като баланс или сметка за печалби и загуби, където числата е вероятно да бъдат оспорени от мениджърите, особено ако крайният резултат е отрицателен!
Можете да използвате това, за да осигурите подробна информация, така че когато мениджърът предизвика определен номер, всичко, което трябва да направи, е да кликнете два пъти върху номера и разбивката да се появи като част от доклада.
Това е много впечатляващо от гледна точка на потребителя и ги спестява постоянно да питат „защо този брой е толкова висок?“
Ще трябва да напишете код, за да разберете заглавието / критериите за номера (използвайки свойствата на обекта Target) и след това да филтрирате табличните данни и след това да ги копирате в отчета.
VBA програмиране | Code Generator работи за вас!
Работен лист Активиране на събитие
Това събитие възниква, когато потребителят се премести от един лист на друг. Прилага се за новия лист, към който се премества потребителят.
Може да се използва, за да се гарантира, че новият лист е изчислен изцяло, преди потребителят да започне да прави нещо по него. Може да се използва и само за преизчисляване на този конкретен лист, без да се изчислява отново цялата работна книга. Ако работната книга е голяма и има сложна формула, повторното изчисляване на един лист спестява много време
| 123 | Частен под работен лист_Activate ()ActiveSheet.CalculateEnd Sub |
Събития за активен X контрол (не са изчерпателни)
Както бе обсъдено по -рано, можете да добавите Active X контроли директно към работен лист. Това могат да бъдат командни бутони, падащи менюта и списъчни полета
Събитията Active X се намират под конкретния обект с име на лист (където сте добавили контролата) във VBE Project Explorer. Ще трябва да изберете името на Active X контролата при първото падащо меню в прозореца с кода и след това второто падащо меню ще ви покаже всички налични събития
Команден бутон Щракнете върху събитие
Когато поставите команден бутон върху електронна таблица, ще искате той да предприеме някои действия. Можете да направите това, като поставите код на събитието Click.
Можете лесно да поставите „Сигурно ли сте съобщение?“ Върху това, така че да се направи проверка, преди кодът ви да се изпълни
| 12345 | Private Sub CommandButton1_Click ()Dim ButtonRet As VariantButtonRet = MsgBox ("Сигурни ли сте, че искате да направите това?", VbQuestion Или vbYesNo)Ако ButtonRet = vbNo След това излезте от SubEnd Sub |
Падащо меню (Комбинирано поле) Промяна на събитие
Падащото меню Active X има събитие за промяна, така че ако потребител избере конкретен елемент от падащия списък, можете да уловите избора му с помощта на това събитие и след това да напишете код, за да адаптирате съответно други части на листа или работната книга.
| 123 | Частен под ComboBox1_Change ()MsgBox "Вие избрахте" & ComboBox1.TextEnd Sub |
VBA програмиране | Code Generator работи за вас!
Поставете отметка в квадратчето (квадратче за отметка) Щракнете върху Събитие
Можете да добавите отметка или квадратче за отметка към работен лист, за да предоставите на потребителя опции за избор. Можете да използвате събитието щракване върху него, за да видите дали потребителят е променил нещо по този въпрос. Върнатите стойности са True или False в зависимост от това дали е отметнато или не.
| 123 | Private Sub CheckBox1_Click ()MsgBox CheckBox 1. СтойностEnd Sub |
Събития на UserForm (не са изчерпателни)
Excel ви дава възможност да проектирате свои собствени формуляри. Те могат да бъдат много полезни за използване като изскачащи прозорци за събиране на информация или за предоставяне на множество възможности за избор на потребителя. Те използват Active X контроли, както е описано по -горе и имат абсолютно същите събития, въпреки че събитията зависят много от вида на контролата.
Ето пример за проста форма:
Когато се показва така изглежда на екрана
Бихте използвали събития във формуляра, за да правите неща като въвеждане на име на фирма по подразбиране, когато формулярът е отворен, за да проверите дали въвеждането на името на компанията отговаря на това, което вече е в електронната таблица и не е написано погрешно, и да добавите код към щракването събития на бутоните „OK“ и „Отказ“
Кодът и събитията зад формуляра могат да се видят чрез двойно щракване навсякъде във формуляра
Първото падащо меню дава достъп до всички контроли във формуляра. Второто падащо меню ще даде достъп до събитията
Събитие за активиране на UserForm
Това събитие се задейства, когато формулярът е активиран, обикновено когато се показва. Това събитие може да се използва за настройване на стойности по подразбиране, напр. име на фирма по подразбиране в текстовото поле за името на компанията
| 123 | Private Sub UserForm_Activate ()TextBox1.Text = "Име на моята компания"End Sub |
VBA програмиране | Code Generator работи за вас!
Промяна на събитието
Повечето контроли на формуляра имат събитие за промяна, но в този пример текстовото поле за името на компанията може да използва събитието, за да постави ограничение за дължината на въведеното име на компанията
| 123456 | Private Sub TextBox1_Change ()Ако Len (TextBox1.Text)> 20 ТогаваMsgBox "Името е ограничено до 20 знака", vbCriticalTextBox1.Text = ""Край АкоEnd Sub |
Щракнете върху Събитие
Можете да използвате това събитие, за да предприемете действия от потребителя, щракнал върху контроли върху формуляра или дори самия формуляр
На този формуляр има бутон „OK“ и след като сме събрали име на фирма, бихме искали да го поставим в клетка в електронната таблица за бъдещи справки
| 1234 | Частна подкоманда CommandButton1_Click ()ActiveSheet.Range ("A1"). Стойност = TextBox 1. ТекстМе.СкриванеEnd Sub |
Този код действа, когато потребителят кликне върху бутона „OK“. Той поставя стойността в полето за въвеждане на името на компанията в клетка A1 на активния лист и след това скрива формуляра, така че потребителският контрол да се върне обратно в работния лист.
Събития в диаграма
Събитията с диаграми работят само върху диаграми, които са на отделен лист с диаграми, а не върху диаграма, която е включена в стандартен работен лист
Събитията с диаграми са донякъде ограничени и не могат да се използват на работен лист, където може да имате няколко диаграми. Също така, потребителите не е задължително да преминат от работен лист, съдържащ числа, към лист с диаграма - тук няма непосредствено визуално въздействие
Най -полезното събитие би било да разберете компонента на диаграма, върху който потребителят е кликнал, напр. сегмент в кръгова диаграма или лента в стълбовидна диаграма, но това не е събитие, налично в стандартния диапазон от събития.
Този проблем може да бъде решен чрез използване на модул клас, за да се добави събитие „Mouse Down“, което ще върне подробности за компонента на диаграмата, върху който потребителят е кликнал. Това се използва на диаграма в работен лист.
Това включва много сложно кодиране, но резултатите са впечатляващи. Можете да създавате пробиви, напр. потребителят кликва върху сегмент от кръгова диаграма и моментално тази диаграма е скрита и на нейно място се появява втора диаграма, показваща кръгова диаграма с детайли за оригиналния сегмент, или можете да създадете таблични данни, поддържащи този сегмент от кръговата диаграма.
Събития за кандидатстване
Можете да използвате обекта Application във VBA, за да задействате код според конкретно събитие
VBA програмиране | Code Generator работи за вас!
Application.OnTime
Това може да ви позволи да задействате парче код на редовни интервали, докато работната книга е заредена в Excel. Може да искате автоматично да запишете работната си книга в друга папка на всеки 10 минути или да оставите работния лист да работи през нощта, за да въведете най-новите данни от външен източник.
В този пример подпрограма се въвежда в модул. Той показва поле за съобщения на всеки 5 минути, въпреки че това лесно може да бъде друга кодирана процедура. В същото време той връща таймера към текущото време плюс още 5 минути.
Всеки път, когато работи, таймерът се нулира, за да стартира същата подпрограма след още 5 минути.
| 1234 | Sub TestOnTime ()MsgBox "Тестване на време"Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"End Sub |
Application.OnKey
Тази функция ви позволява да проектирате свои собствени клавишни комбинации. Можете да направите всяко извикване на комбинация от клавиши подпрограма на вашето творение.
В този пример буквата „а“ се пренасочва така, че вместо да постави „а“ в клетка, тя ще покаже поле за съобщение. Този код трябва да бъде поставен във вмъкнат модул.
| 123456 | Sub TestKeyPress ()Application.OnKey "a", "TestKeyPress"End SubSub TestKeyPress ()MsgBox „Натиснахте„ a ““End Sub |
Изпълнявате подпрограмата „TestKeyPress“ преди всичко. Трябва да стартирате това само веднъж. Той казва на Excel, че всеки път, когато буквата „а“ се натисне, тя ще извика подпрограмата „TestKeyPress“. Подпрограмата „TestKeyPress“ просто показва поле за съобщение, което ви казва, че сте натиснали клавиш „а“. Разбира се, може да зареди формуляр или да направи всякакви други неща.
Можете да използвате всяка комбинация от клавиши, която можете да използвате с функцията „SendKeys“
За да отмените тази функционалност, стартирате оператора „OnKey“ без параметъра „Процедура“.
| 123 | Sub CancelOnKey ()Application.OnKey "а"End Sub |
Сега всичко се нормализира.
