Enableevents excel что это
Несколько советов по работе с VBA в Excel
Добрый день!
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.
Visual Basic
Опции
Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
Так же рекомендуется прописать:
В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).
Ещё одним важным оператором является ON ERROR. Привожу варианты:
Возможности языка
Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):
Ускорение работы макросов
Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).
Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:
Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.
Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.
Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.
Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.
Загрузка книги и события
При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().
Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.
Защита
Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.
Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.
Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.
Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.
Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.
Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.
Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:
Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).
Заключение
VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.
Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.
Enableevents excel что это
Объект Excel.Application, свойства, методы и события
Многие свойства, методы и события объекта Excel.Application совпадают с аналогичными в Word.Application. Однако поскольку информация приводится здесь для справки для тех пользователей, которым трудно свободно читать по английски, здесь будут приведены наиболее часто используемые свойства и метода объекта Application в Excel, вне зависимости от того, встречались ли они нам в Word, или нет.
Вначале — о свойствах объекта Application:
В данном случае мы выделили полужирным ячейку на пересечении первой строки и второго столбца.
а для строки (второй) можно воспользоваться очень похожей командой
Еще раз отметим, что свойства Cells, Columns и Rows возвращают вовсе не наборы объектов Cell, Column и Row, как считают многие пользователи, а наборы объектов Range. На использовании объекта Range построена в Excel почти вся работа с ячейками и их значениями. Ниже в этой главе объекту Range будет посвящен отдельный раздел.
Похожий пример с возможностью выбрать сразу несколько файлов приведен в справке по этому свойству.
Самые важные методы объекта Excel.Application перечислены ниже:
Эту функцию можно вызвать и неявно — просто заключив имя объекта в квадратные скобки. Например, такие строки будут абсолютно одинаковыми по значению:
Поскольку синтаксис с квадратными скобками короче, чаще всего используется именно он.
Таким образом, метод Evaluate() — это самый простой и естественный метод обратиться к ячейке или диапазону в своей или чужой книге Excel.
If Filename <> False Then
If Application.Wait(Now + TimeValue(«0:00:5»)) Then
MsgBox «Пять секунд прошло»
Enableevents excel что это
Объект Excel.Application, свойства, методы и события
Многие свойства, методы и события объекта Excel.Application совпадают с аналогичными в Word.Application. Однако поскольку информация приводится здесь для справки для тех пользователей, которым трудно свободно читать по английски, здесь будут приведены наиболее часто используемые свойства и метода объекта Application в Excel, вне зависимости от того, встречались ли они нам в Word, или нет.
Вначале — о свойствах объекта Application:
В данном случае мы выделили полужирным ячейку на пересечении первой строки и второго столбца.
а для строки (второй) можно воспользоваться очень похожей командой
Еще раз отметим, что свойства Cells, Columns и Rows возвращают вовсе не наборы объектов Cell, Column и Row, как считают многие пользователи, а наборы объектов Range. На использовании объекта Range построена в Excel почти вся работа с ячейками и их значениями. Ниже в этой главе объекту Range будет посвящен отдельный раздел.
Похожий пример с возможностью выбрать сразу несколько файлов приведен в справке по этому свойству.
Самые важные методы объекта Excel.Application перечислены ниже:
Эту функцию можно вызвать и неявно — просто заключив имя объекта в квадратные скобки. Например, такие строки будут абсолютно одинаковыми по значению:
Поскольку синтаксис с квадратными скобками короче, чаще всего используется именно он.
Таким образом, метод Evaluate() — это самый простой и естественный метод обратиться к ячейке или диапазону в своей или чужой книге Excel.
If Filename <> False Then
If Application.Wait(Now + TimeValue(«0:00:5»)) Then
MsgBox «Пять секунд прошло»
Как отследить событие(например выделение ячеек) в любой книге?
Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в Excel и сделать какое-то действие в зависимости от имени открытой книги? Или как отследить выделение ячейки в любой книге? Изменение значений ячеек?
На самом деле все до смешного просто:
В модуле ЭтаКнига главной книги(надстройка либо PERSONAL.XLS) необходимо создать переменную, которая будет ссылкой на все приложение Excel
Private WithEvents App As Application
Private Sub Workbook_Open() Set App = Application End Sub
Вот так, например, будет выглядеть код отслеживания открытия любой книги :
Private Sub App_WorkbookOpen(ByVal Wb As Workbook) MsgBox «Вы открыли книгу:» & Wb.Name End Sub
Сам по себе код не заработает. Т.к. назначение значения переменной App происходит только при открытии самой книги(надстройки или PERSONAL), то после создания кодов надо будет сохранить эту книгу и открыть заново
А с помощью этого кода можно отследить создание новой книги :
Private Sub App_NewWorkbook(ByVal Wb As Workbook) MsgBox «Вы создали новую книгу» End Sub
Отслеживаем выделение ячеек во всех открытых книгах :
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) MsgBox «Вы выделили ячейку с адресом: » & Target.Address End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = «Отчет» Then MsgBox «Вы выделили ячейку с адресом: » & Target.Address End If End Sub
А процедура ниже поможет отследить изменение значений ячеек во всех открытых книгах и отменить нежелательные изменения:
В приложенном к статье файле будет чуть более понятно что делает эта процедура.
Естественно, в таких процедурах можно назначить выполнение и других(нужных) действий. Например, вызов макроса ( Call ИмяМакроса ). Макрос в таком случае должен быть размещен в стандартном модуле и иметь статус Public (или вовсе без статуса). Сам модуль должен тоже находится в той же книге.
Tips_Macro_How_Catch_Events.xls (60,5 KiB, 4 381 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Using Application.EnableEvents in VBA in Excel (Explained with Examples)
Application.EnableEvents is a property in Excel VBA where you can specify whether you want events to take place when the VBA code is running or not.
An event is usually attached to an object. VBA Events could include things such as activating a worksheet or saving a workbook or double-clicking on a cell.
Excel VBA allows you to create event-specific code, where you can specify what code to run in case the user double-clicks on a cell. In this case, this would be the double-click event.
Similarly, you can have a code that is run as soon as a new worksheet is activated in the workbook.
Now, these events are useful, but sometimes you may not want these to work.
For example, if you’re running a code that will activate and loop through all the open worksheets one by one, you may not want to execute the event code (in case you have it) when each sheet is activated.
In such cases, you can set the Application.EnableEvents property to false, making sure the events are turned off when the code is running.
Now that you have a better understanding of what Application.EnableEvents does (I hope), let’s see the syntax and some examples.
Application.EnableEvents Syntax
Below is the syntax of the Application.Events
where, the application is the object (when using this in Excel VBA, Excel is the application), and Events is the property.
So you can set the Application.Events property to either True or False.
When you set this to TRUE, events will run as usual in Excel VBA, and if you set this to FALSE, events would stop working (for the whole VBA).
One caveat here is that if you set this to FALSE, remember to set it back to TRUE again (which is the default and expected behavior of this property)
Now let’s see an example of how to use it
Application.EnableEvents Example
Below is a code where the main code is flanked by the Application.EnableEvents property.
In the above code, the ThisWorkbook.Save is flanked by Application.EnableEvents properties being set first to FALSE and then to TRUE.
When you run this code, in case there are any events associated with the workbook getting saved, it will not be run. And since you have set the property back to TRUE in the last line, this will not impact other macros in your workbook.
Application.EnableEvents Not Working
Remember that Application.EnableEvents would not work with Userforms events and controls.
Here is an article that talks about some of the disadvantages of using Application.EnableEvents and an alternative way to handle events in Excel VBA.
So this is all that you needed to know about the Application.EnableEvents property in Excel VBA.
Hope you found this tutorial useful!
Other Excel tutorials you may like: