Как запускать макросы на базе пользовательских событий

Результат: в данной для нас статье мы сравниваем разные места для хранения макросов, функций и кода VBA в Excel. Мы специально смотрим на модули кода, листовой модуль и модуль ThisWorkbook, чтоб выяснить различия меж тем, как работает любой из их. Мы также узнаем, как запускать макросы на базе событий либо действий юзера.

Уровень мастерства: Средний

Видео: разъяснение кодовых модулей VBA

Скачать файл

Загрузите файл Excel.

VBA Code Modules.xlsm (25.3 KB)

5 мест для хранения кода VBA в рабочей книжке

По сути есть 5 разных модулей, в которых мы можем хранить код VBA в рабочей книжке. Любой из их виден в окне Project Explorer (Ctrl + R) в редакторе VB. Вот лаконичный обзор всякого типа объекта.

Code Modules — модули кода являются более всераспространенным местом, где мы храним макросы. Модули находятся в папке «Модули» в рабочей книжке.
Sheet Modules — у всякого листа в книжке есть объект листа в папке «Объекты Microsoft Excel». Двойной щелчок объекта листа открывает его модуль кода, в который мы можем добавить процедуры обработки событий (макросы). Эти макросы запускаются, когда юзер делает определенное действие на листе.
ThisWorkbook Module — любая книжка содержит один объект ThisWorkbook в нижней части папки объектов Microsoft Excel. Мы можем создавать макросы на базе событий, которые производятся, когда юзер делает деяния в / над книжкой.
Userforms — пользовательские формы — это интерактивные формы либо окна, в которые мы можем добавлять элементы управления, такие как раскрывающиеся меню, списки, флажки, клавиши и т. Д. Любая пользовательская форма хранится в папке «Формы» и имеет модуль кода, в который можно помещать макросы, которые будут запускаться. когда форма открыта и / либо юзер ведет взаимодействие с органами управления в форме.
Class Modules — классы хранятся в папке «Модули классов» и разрешают нам писать макросы для сотворения объектов, параметров и способов. Классы могут употребляться, когда мы желаем сделать пользовательские объекты либо коллекции, которые не есть в Библиотеке объектов.

Когда мы два раза щелкаем либо щелкаем правой клавишей мыши> Просмотр кода (сочетание кнопок: F7) на любом из этих объектов в окне Project Explorer, окно кода раскрывается справа от редактора VB. Окно кода смотрится идиентично для всякого из объектов. Это просто большенный пустой холст, где мы можем напечатать код.

Для этого поста мы собираемся сопоставить 1-ые 3 модуля, вышеперечисленные.

Code vs Sheet vs ThisWorkbook Module

Это, возможно, 3 более всераспространенных места, где мы можем хранить макросы (подпроцедуры) и функции (UDF) в наших проектах VBA.

Любой из этих модулей дозволяет нам хранить макросы, которые мы можем запустить нажатием клавиши либо из окна макроса. Но объекты Sheet и ThisWorkbook разрешают нам хранить процедуры событий (макросы), которые будут производиться, когда юзер делает действие в рабочей книжке.

Итак, давайте поглядим на любой объект наиболее тщательно.

Code Modules

Модули кода разрешают нам хранить обыденные макросы (подпроцедуры) и функции (пользовательские функции, пользовательские функции). Это обычное пространство, где мы начинаем писать и хранить наши макросы. Устройство записи макросов также делает код в модуле кода.

Когда у нас есть макрос в модуле кода, мы можем запустить его из окна макроса (вкладка «Разраб» либо «Просмотр вкладки»> «Макросы»). Мы также можем запустить макрос, назначив его кнопочке либо фигуре. Когда юзер надавливает клавишу, макрос запускается. Ознакомьтесь с моей статьей и видео о том, как сделать Индивидуальную книжку макросов и добавить клавиши на ленту для получения наиболее подробной инфы.

Мы можем добавить несколько модулей кода в папку «Модули», и это помогает сохранить наш код организованным в рамках проекта (рабочей книжки).

Таковым образом, модуль кода дозволяет нам хранить главные макросы, которые будут запускаться юзером нажатием клавиши. Но что, если мы желаем, чтоб наши макросы запускались автоматом, когда юзер открывает рабочую книжку, меняет рабочие таблицы либо выбирает определенную ячейку?

Sheet Modules & Event Procedure Macros

Любой лист в книжке имеет объект листа в редакторе VB. Они перечислены в папке «Объекты Microsoft Excel» для каждой открытой книжки в окне «Проект».

Объект листа показывает кодовое имя листа и имя вкладки в скобках. Имя вкладки — это имя, отображаемое в книжке, которое юзер может поменять.

Интересно почитать: Как создать кнопку в excel для макроса

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

Мы можем добавить обыденные макросы в объект листа.

Мы также можем добавить процедуры обработки событий, которые будут производиться, когда юзер делает действие на листе. Ах так добавить функцию обработки действия.

Изберите «Рабочий лист» в раскрывающемся меню «Объекты». Событие выбора будет автоматом добавлено в окно кода. Это событие по дефлоту, но мы НЕ должны употреблять это событие. Событие SelectionChange запускается всякий раз, когда юзер выбирает ячейку на листе.
Щелкните раскрывающийся перечень «Процедура», чтоб узреть перечень остальных процедур обработки событий.
Изберите одно из событий из перечня, и его код процедуры (макроса) будет добавлен в модуль кода листа. Сейчас мы можем добавить код снутри процедуры, которая будет производиться, когда юзер делает действие.

В видео я использую пример кода, который выбирает всю строчку и столбец ячейки, избранной на листе. Он выбирает/выделяет всю строчку и столбец для юзера. Поглядите видео, чтоб тщательно изучить, как это работает.

Модули листа могут содержать несколько процедур обработки событий. Он также может содержать обыденные процедуры (макросы) и функции.

На страничке справки по объекту MSDN Worksheet есть перечень всех событий рабочего листа и их справочных статей.

Модуль ThisWorkbook и макросы процедуры действия

Любая рабочая книжка содержит один объект с именованием ThisWorkbook. Объект ThisWorkbook хранится в нижней части папки объектов Microsoft Excel.

Основное предназначение модуля ThisWorkbook — пуск процедур обработки событий (макросов), когда юзер делает действие в книжке. Это может произойти, когда юзер открывает рабочую книжку, до этого чем закрыть рабочую книжку, когда он выбирает рабочую таблицу, спектр либо даже обновляет сводную таблицу.

Модуль ThisWorbook весьма похож на модули листа. Основное отличие состоит в том, что процедуры обработки событий в модуле ThisWorkbook могут производиться, когда деяния производятся во всей книжке. Действия модуля листа производятся лишь тогда, когда деяния производятся на определенном листе, в котором находится код.

Процесс прибавления процедур обработки событий в модуль ThisWorkbook аналогичен модулю листа.

Изберите Workbook из выпадающего меню Object.
Событие Workbook_Open автоматом добавляется в модуль. Этот макрос запускается при открытии книжки и включении макросов. Вы сможете удалить код этого действия, если вы не желаете его употреблять.
Изберите другое событие из выпадающего перечня Процедуры.
Код для данной для нас процедуры будет добавлен в модуль. Добавьте код в функцию, которая будет производиться, когда юзер выполнит это действие в книжке.

Действия рабочего листа также доступны в перечне событий ThisWorkbook. Это значит, что мы можем запускать действия листа на любом листе либо на отдельных листах заместо копирования / вставки кода меж модулями листа.

Какое наилучшее пространство для хранения моего кода?

Сейчас, когда вы понимаете, как работают разные модули, для вас быть может любопытно, где идеальнее всего хранить весь ваш код. И ответ такой: это вопросец личных предпочтений …

Расположите весь код в листовые модули

На техническом уровне вы сможете хранить все свои макросы и функции в модуле Sheet либо модуле ThisWorkbook. Неким разрабам нравится этот подход, поэтому что он привязывает все к определенному листу либо набору листов. Потом листы можно перемещать либо копировать в различные рабочие книжки, и код будет передвигаться совместно с ними. Принципиально отметить, что пользовательские функции должны храниться в модуле кода.

Организовать код в кодовых модулях

Остальным разрабам нравится организовывать все макросы в модулях кода. Если им необходимо употреблять действия листа либо книжки, они добавляют эти действия в соответственный модуль и потом вызывают макросы в модулях кода из этих событий. Преимущество в том, что весь ваш код просто узреть в одном месте. Вы сможете добавлять комменты к макросам, которые вызываются процедурами обработки событий, чтоб вы знали, как производятся макросы.

По данной для нас причине я предпочитаю хранить собственный код в модулях кода. Когда я открываю книжку в редакторе VB, просто узреть, что макрос содержит код, без необходимости два раза щелкать любой объект листа, чтоб узреть, существует ли код в модуле. Это может занять много времени, если в рабочей книжке много листов, и это может затруднить отладку проекта.

Интересно почитать: Как записать макрос в excel 2016

Планируйте свое наследство с мозгом!

У всякого способа есть свои плюсы и минусы, потому я предлагаю избрать тот, который подступает конкретно для вас. Если вы собираетесь передавать рабочую книжку либо проект кому-то другому для поддержки в будущем, постарайтесь облегчить им поиск вашего кода. Планирование вашего наследства заранее будет означать меньше работы вам в будущем …

Ошибка приложения Excel при открытии файла с макросами

Не так издавна очередной раз столкнулся с ошибками приложения Excel при попытке включения макросов опосля открытия файлов .xlsm. Вспомянул, что подобные задачи преследуют юзеров достаточно издавна, но почаще всего они наблюдались с Excel 2013 и Excel 2016. Соответствующие индивидуальности данной для нас группы ошибок последующие:

Приложение Excel запирается при открытии файла с макросами (при включенном режиме сохранности Включить все макросы );
Ошибка приложения Excel возникает при попытке включить содержимое (нажатии соответственной клавиши);
Ошибка приложения Excel возникает при сохранении файла с макросами;

Ну и по жарким следам еще одного инцидента, чтобы не откладывать на позже, решил себе собрать маленький хаб по ошибкам приложения Excel с следующей модификацией, чтобы снова не тратить время зря на поиск инфы в Сети и на составление облака обстоятельств.
Сущность в том, что в процессе открытия файла xlsm, и при отключенных макросах, в высшей части основного окна (над таблицей), высвечивается строчка извещения: ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ Запуск макросов отключен , с клавишей включения содержимого (макросов). Как юзер её надавливает, Excel просто аварийно заканчивается (падает) с ошибкой Программка Microsoft Excel не работает и соответствующим окном извещения:

excel ошибка макроса

При всем этом было увидено, что конкретно перед появлением ошибки приложения Excel никаких системных обновлений и обновлений пакета Office не устанавливалось. Может быть, каким-то образом задействованы крайние обновления на Office, но прямой связи я не увидел, а подробного исследования задачи не проводил. При всем этом зависимости от версии операционной системы (мною лично сбои наблюдались на Windows 10 LTSC и Windows 7 Professional) так же выявлено не было. При анализе аварийного дампа приложения ( *.hdmp ) обычно можно узреть схожую информацию исключения (вывод урезан):

обычно это NTSTATUS с кодом c0000005 — Access violation, доступ запрещен. И почаще всего в дампе можно узреть таковой вот стек потока (вывод оптимизирован для улучшения представления):

из которого единственное что понятно, так это то, что падение Excel происходит в недрах функций библиотеки vbe7.dll (среда выполнения VBA), подгруженной в адресное место процесса. Это показывает на задачи с обработчиком VBA-скриптов, в контексте Excel почаще называемых макросами.

Так что все-таки такое VBA и для что он предназначается?

В приложениях, входящих в набор MS Office, таковых как Excel, Word, PowerPoint и Access, VBA употребляется для автоматизации огромного количества рутинных задач (напр.: циклических однотипных действий), дозволяет создавать формы для общения с юзером и дает огромное количество другого обеспеченного функционала. С помощью VBA доступно управление электрической таблицей средством объектно-ориентированной модели кода/данных, с помощью VBA-кода входные данные таблиц могут быть обработаны и представлены в итоговых (результирующих) таблицах и диаграммах (графиках). Таблица становится интерфейсом кода, позволяя просто работать, изменять его и управлять расчётами. На выходе VBA проект, применяемый в структурах обрисованных выше приложений (электрические таблицы Excel, презентации PowerPoint, базы Access), компилируется в особый бинарный исполняемый файл, который располагается снутри файла основного формата. Применительно к Excel это файл vbaProject.bin , который размещается снутри *.xslm-файла в директории /xl , представляющий из себя бинарный исполняемый файл проекта, содержащий макрос в откомпилированном (готовом к выполнению) виде.

Решение 1: вставка новейшего листа

Сделайте приведенную последовательность действий:

Открываем [проблемный] .xlsm-файл (файл с макросами).
Не жмем клавишу Включить содержимое .
Добавляем в книжку Excel новейший лист: правая клавиша мыши на ярлычках Лист1/Лист2/Лист3 → Вставить → Лист .
Сохраняем электрическую таблицу. Закрываем Excel.
Открываем проблемный файл поновой и включаем макросы.

Решение 2: перекомпиляция проекта

Сделайте последовательность действий:

Запускаем и открываем новейшую книжку Excel (не ваш проблемный файл).
Открываем меню Файл — избираем Характеристики — дальше открываем Центр управления сохранностью и заходим в Характеристики центра управления сохранностью .
В разделе Характеристики макросов — выставляем чекбокс Отключить все макросы с извещением .
В разделе Надежные расположения — выставляем чекбокс Отключить все надежные расположения .
В разделе Надежные документы — выставляем чекбокс Отключить надежные документы .
Нажимаем всюду OK . Закрываем Excel.
Открываем [проблемный] .xlsm-файл (файл с макросами).
Не жмем клавишу Включить содержимое .
Открывает редактор Visual Basic с помощью композиции кнопок Alt + F11 . Или можно употреблять обходной маневр: в настройках включаем меню Разраб , опосля этого в показавшемся сверху в ленте меню Разраб избираем пункт Visual Basic .
В открывшемся окне редактора Visual Basic (VBA редактор) пересохраняем проект: для этого нажимаем на панели инструментов клавишу Сохранить (изображение дискетки либо композиция Ctrl + S ).
Избираем из меню Debug — избираем пункт меню Compile VBA Project :

Решение 3: добавление модуля

Доп решение состоит в том, что бы внести конфигурации в макрос без перекомпиляции.

Открываем [проблемный] .xlsm-файл (файл с макросами).
Не жмем клавишу Включить содержимое .
Открывает редактор Visual Basic (с помощью композиции кнопок Alt + F11 ).
Открываем меню Tools → пункт Options . В открывшемся окне перебегаем на вкладку General и деактивируем чекбокс Compile on Demand :

КАК: Внедрение Macro Recorder для сотворения обычных макросов в Excel 2003 — 2021

Урок №1. Макросы в Excel. Запись и пуск макросов (Октябрь 2021).

В этом руководстве описывается внедрение макрорекордера для сотворения обычного макроса в Excel. Учебник не обхватывает создание либо редактирование макроса при помощи редактора VBA.