- Понятие и задачи макросов VBA
- Способы создания макросов VBA
- Способ 1. Написание макросов в редакторе Visual Basic
- Способ 2. Запись макросов с помощью макрорекордера
- Применение созданных макросов VBA
- Часто задаваемые вопросы о макросах VBA
- Какое расширение используется для хранения файла с макросом?
- Где хранить файлы с макросами?
- Что такое режим отладки макроса?
25 рабочих гипотез для увеличения конверсии на 40%
Скачать материалы
О чем речь? Макросы VBA – запрограммированная последовательность операций, написанная на языке Visual Basic for Applications, специально разработанном для создания программ в приложениях Microsoft Office.
Зачем нужны? Использование макросов VBA позволяет автоматизировать действия, наладить взаимодействие с другими программами от Майкрософт или получить расширенные возможности офисных приложений.
Понятие и задачи макросов VBA
Visual Basic for Applications (VBA) – формальная система символов для написания компьютерных программ. VBA является более простым вариантом языка Visual Basic.
Разработчики Microsoft создали Visual Basic for Applications для интеграции с продуктами из своей линейки: Outlook, Word, Excel, PowerPoint, Access. Кроме того, этот язык применяется и в ряде других популярных программ: CorelDRAW, SolidWorks и Автокад.
Внедрение VBA позволяет существенно расширить функционал указанных программных продуктов. С его помощью можно в базовые версии программ добавлять ряд полезных для пользователей новых функций. Кроме того, данный язык позволяет выполнять повторяющиеся операции в автоматическом режиме.
На VBA разрабатывают макросы – компактные программные решения, выполняющие заданную очередность действий внутри программ.
Например, пользователю нужно отфильтровать табличные данные по определенным правилам и убрать повторяющиеся значения. Решить такую задачу можно, написав специальный макрос, который по запросу будет формировать нужный отчет. Такая функция обеспечивает дополнительные удобства при работе с программами в ситуациях, когда приходится часто выполнять повторяющиеся действия.
Используя макросы VBA, можно автоматизировать ряд операций, что сокращает время и снижает вероятность ошибок.
Наиболее часто макросы VBA применяются в программе Excel. Это дает возможность существенно увеличить функционал электронных табличных форм. Помимо автоматизации повторяющихся операций макросы упрощают задачи, возникающие при взаимодействии с другими продуктами MS Office:
- Макросы VBA помогают автоматизировать повторяющиеся задачи в Excel.
Например, создание оглавления для файла с большим количеством листов. Вместо того чтобы вручную добавлять новый лист, перечислять названия всех остальных листов и вставлять на них ссылки, можно написать макрос, который сделает все это автоматически по нажатию одной кнопки.
Особенно это полезно, если нужно обработать несколько файлов одновременно. Стандартные инструменты Excel такого функционала не предлагают, поэтому здесь на помощь приходят макросы VBA.
- Расширение функционала Excel.
Используя VBA, пользователи могут добавлять в Excel собственные функции, которых нет в базовом варианте программы. Например, можно написать макрос для подсчета уникальных параметров. В Excel такой инструмент отсутствует.
- Интеграция с другими программными продуктами.
VBA дает возможность объединить файлы из различных приложений пакета MS Office. Например, можно перенести данные из документа Word в таблицу Excel и прописать связи между ними. В результате любые изменения в текстовом документе будут автоматически отражаться и в таблицах.
Доработка возможностей Excel приводит к снижению популярности VBA. Несмотря на легкость изучения, Visual Basic for Applications все же является языком для написания программ. Для написания макросов нужно знать синтаксис и разбираться в объектной модели Excel.
На смену макросам все чаще приходят новые встроенные функции по работе с электронными таблицами. Это Power Query, Power Pivot, а также ряд других инструментов обработки меняющихся массивов данных (например, УНИК/UNIQUE, СОРТ/SORT, ФИЛЬТР/FILTER и др.). В последнее время макросы VBA актуальны лишь для решения более специфических и сложных задач.
Способы создания макросов VBA
Способ 1. Написание макросов в редакторе Visual Basic
Чтобы создать макрос VBA, следует нажать на специальное окно – редактор программы VBA, который интегрирован в программу Excel.
В более ранних версиях программы для работы с таблицами (до 2003 года включительно) эта процедура будет отличаться. Для запуска Visual Basic нужно сделать следующие шаги: открыть меню «Сервис», выбрать «Макросы» и нажать на «Редактор Visual Basic».
В новых версиях Excel (начиная с 2007 года) необходимо включить вкладку «Разработчик». Для этого следует нажать на кнопку «Файл», выбрать «Параметры» и «Настройка ленты». Там нужно поставить галочку напротив пункта Developer. После этого на ленте появится новая вкладка «Разработчик» с инструментами по использованию макросов и кнопка для открытия Visual Basic.
Следует учитывать, что все справочники и настройки в Visual Basic доступны только на английском языке. Автоматический перевод на русский здесь не предусмотрен.
Хранение набора команд в виде макросов обеспечивается в виде логически связанных фрагментов. В каждой книге Excel можно прописать неограниченное число таких модулей с макросами.
При этом их число в одном фрагменте также может быть любым. Чтобы зайти в библиотеку макросов VBA, следует воспользоваться комбинацией клавиш CTRL+R или окном Project Explorer (слева вверху дисплея).
Различают несколько видов программных модулей, которые используются в различных ситуациях:
- Стандартные: наиболее распространенный тип модулей, используемых при работе с макросами в VBA. Для их формирования следует перейти в меню на Insert и выбрать пункт «Модуль». После этого откроется новое окно, куда можно вводить команды на языке VBA вручную либо путем копирования их из других фрагментов, веб-сайтов или других источников.
- «Эта книга«: расположен слева вверху окна редактора в секции Project Explorer. Здесь, как правило, содержатся макросы, которые запускаются при наступлении определенных условий в рабочей книге: открытие, печать, сохранение и т. д.
- Модуль листа: открывается двумя способами. Первый – через Project Explorer, а второй – с использованием контекстного меню (нужно кликнуть правой кнопкой мыши по соответствующему ярлыку и выбрать команду «Исходный текст»). В этом модуле обычно размещают макросы, запускаемые при наступлении конкретных условий, включая изменение параметров в ячейках, копирование, пересчет или удаление листа.
Для наглядности рассмотрим макрос Zamena:
- Подобные подпрограммы всегда открывает оператор Sub. Далее указывается имя макроса и в скобках перечень входных параметров (аргументов). При отсутствии аргументов проставляются пустые скобки.
- В конце записи макроса всегда проставляется оператор End Sub.
- Между указанными выше операторами находятся команды, осуществляемые при активации макроса (тело макроса). В нашем примере подпрограмма выделяет ячейку и заливает желтым (код = 6) выделенный диапазон (Selection). Далее происходят циклические действия. Во всех ячейках вместо формул появляются значения. После этого появляется окно сообщения (MsgBox).
Понятно, что без специальной подготовки и опыта по написанию программ (в частности, на VBA) непросто разобраться, как и какие команды следует вносить, чтобы макрос в автоматическом режиме выполнял требуемые операции, например, для формирования ежедневной отчетности руководителю. С учетом этой ситуации перейдем к следующему методу создания макросов.
Способ 2. Запись макросов с помощью макрорекордера
Макрорекордер – это небольшой программный модуль, интегрированный в Excel. С его помощью можно автоматически преобразовывать действия пользователя в код на VBA.
При активации макрорекордера, когда мы начинаем работать над созданием ежедневного отчета, программа фиксирует каждую операцию пользователя и прописывает соответствующие команды в программный модуль.
В результате создается готовый макрос. Он выполняет все требуемые шаги, как будто составлен опытным разработчиком программ. Отметим, что этот способ создания макросов не требует специальных знаний в области программирования на VBA. В этом случае мы получаем некоторый аналог видеозаписи (включил запись, выполнил действие, перемотал ее и инициировал выполнение подобных операций еще раз).
Представленный метод имеет свои достоинства и недостатки:
- Макрорекордер фиксирует операции, которые осуществляются исключительно в окне Excel. Если оно закрыто или пользователь переходит в другой программный продукт, процесс записи прекращается.
- Рекордер способен фиксировать исключительно операции, выполняемые с использованием кнопок или меню Excel. В свою очередь, программист имеет возможность разработать макрос, который выполняет задачи, недоступные в стандартной версии Excel (сортировка по цвету или другие подобные операции).
- Если в процессе записи допущена ошибка, макрорекордер зафиксирует и это действие. В этом случае можно воспользоваться функцией отмены (Undo). Важно учесть, что при нажатии этой кнопки не только восстанавливается более раннее состояние, но и последняя записанная команда будет удалена из макроса.
Для активации записи выполняются следующие шаги:
- В Excel 2003 и более ранних версиях нужно зайти в раздел Tools, выбрать Macro и нажать на Record New Macro.
- При работе с Excel 2007 и новее необходимо кликнуть Record Macro (кнопка в разделе Developer).
После активации записи откроется окно настройки параметров создаваемого макроса. В окне Record Macro нужно задать следующие параметры:
- Имя макроса: должно начинаться с любого буквенного символа (русского или английского). Не допускается использование знаков пунктуации и пробелов.
- Комбинация клавиш: будет применяться для активации макроса. Этот параметр не является обязательным. Если комбинация клавиш не задана, запуск программы будет происходить через раздел Tools -> Macro -> Macros -> Run. Кроме того, можно использовать кнопки Macros на вкладке Developer или сочетание клавиш АLT+F8 (откроется список доступных макросов).
- «Сохранить в«: здесь сохраняется текст программы (совокупность команд, формирующих макрос).
- Эта книга: запись макроса в модуле используемой рабочей книги (он будет доступен и выполнится при ее открытии в программе Excel).
- Новая книга: программа будет сохранена в шаблоне, в соответствии с которым появляются новые пустые книги в Excel (макрос будет присутствовать во всех новых книгах, создаваемых на конкретном ПК).
- Личная книга: отдельная книга в Excel с названием Personal.xls, предназначенная для хранения подпрограмм. Все макросы, сохраненные здесь, автоматически загружаются при запуске Excel и могут всегда запускаться во всех открытых книгах.
Когда запись активирована и все требуемые действия по созданию макроса выполнены, для ее отключения используется кнопка Stop Recording.
Применение созданных макросов VBA
При назначении сочетания клавиш в ходе записи макроса с помощью такой комбинации можно быстро активировать такую подпрограмму. При этом следует учитывать место хранения макроса. Если он сохранен в рамках одной табличной формы, то запуск из других документов будет невозможен.
В случае отсутствия комбинации для активации нужно зайти в раздел «Разработчик» и там запустить макросы VBA. Будет открыт перечень созданных и доступных для конкретного документа команд. Здесь следует выбрать необходимый макрос и кликнуть «Выполнить». Это приведет к выполнению макрокоманды.
Специалисты рекомендуют задавать сочетания клавиш для макросов, которые будут включаться чаще всего, а менее востребованные макросы лучше запускать через меню.
Часто задаваемые вопросы о макросах VBA
Какое расширение используется для хранения файла с макросом?
В стандартном Excel макрос храниться не может. Для его сохранения в конкретном документе при отправке табличной формы нужно сохранить файл в расширении «. xslm», который поддерживает такие макрокоманды.
Где хранить файлы с макросами?
Для этой цели можно указать определенные папки, в которых допускается выполнение макрокоманд. Следует создать надежные расположения, документы или указать издателей. Третий вариант считается универсальным. Метод с «надежными издателями» удобен при обработке документов, заверенных цифровой подписью пользователя.
Что такое режим отладки макроса?
В случае выявления ошибок пользователь может вернуться к более ранним действиям и произвести отладку макрокоманд. Вначале такие действия могут показаться неудобными. Есть вероятность появления ошибок, но они не будут критическими.
Специалисты рекомендует первыми брать в работу документы, обработка которых не вызывает серьезных сложностей.
Если возникает необходимость в применении макросов, следует использовать наиболее простые и понятные действия, которые уже приводили к запланированному результату. Не следует отключать режим безопасной работы, в особенности при работе с файлами, которые загружены из непроверенных источников.
Изображение в шапке статьи: Racool_studio / Freepik
