VBA в Excel: объясняем главные понятия и принципы работы

Статья для тех, кто хочет разобраться в языке макросов. Рассказали, что такое VBA и для чего он нужен.

VBA — внутренний язык программирования для программ Microsoft Office. С помощью него пишут макросы — мини-программы, которые одним нажатием кнопки выполняют в документе сразу несколько действий.

В статье рассказано о том, как VBA используют в Excel и как он работает. Материал будет полезен всем, кто активно пользуется Excel и хочет понять, как устроено программирование внутри него.

Что такое VBA

VBA (Visual Basic for Applications) — язык программирования, упрощённая версия языка Visual Basic.

Его разработала компания Microsoft специально для линейки своих продуктов — Excel, Word, PowerPoint, Access, Outlook. Также VBA используют и в других программах — например, в AutoCAD, SolidWorks и CorelDRAW.

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

С помощью VBA создают маленькие программы — макросы, которые выполняют заданную последовательность действий в программах.

Например, можно написать макрос, который отсортирует данные в таблице по вашим правилам, удалит дублирующиеся значения и сформирует отчёт. Это удобно, когда нужно выполнять подобные задачи регулярно. VBA сделает их автоматически, сэкономит время и уменьшит риск ошибок.

Для чего нужен VBA в Excel

Чаще всего VBA в Excel используют, чтобы автоматизировать повторяющиеся действия, расширить возможности электронных таблиц и взаимодействовать с другими программами MS Office.

Автоматизация повторяющихся действий. Допустим, пользователю необходимо создать оглавление для Excel-файла. Это можно сделать вручную: создать новый лист в начале документа, выписать на него названия всех остальных листов и поставить ссылки на эти листы.

Если таких листов не много, это можно делать и вручную. Но если листов больше — например, 15 — или если оглавления нужно создавать для каждого Excel-файла, действия лучше автоматизировать, чтобы оглавление для любого числа листов появлялось после нажатия одной кнопки. С помощью обычных функций Excel это сделать невозможно. В таких случаях используют язык VBA и макросы.

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

Взаимодействие с другими программами Microsoft. Например, VBA позволяет объединить данные файлов разных программ MS Office. С его помощью можно перенести данные из текстового документа, созданного в Word, в файл Excel и настроить связь между ними. Это значит, что, если пользователи поменяют данные в Word-файле, эти данные сразу обновятся и в Excel.

С развитием Excel VBA стали использовать реже. Потому что, хотя он довольно прост в изучении, это всё-таки язык программирования. Чтобы им пользоваться, нужно изучить синтаксис языка, понять объектную модель Excel и так далее.

Сейчас большинство задач, для которых раньше использовали макросы, можно решить с помощью встроенных в Excel надстроек Power Querty и Power Pivot, а также новых функций, работающих с динамическими массивами (УНИК/UNIQUE, СОРТ/SORT, ФИЛЬТР/FILTER и других). Программирование макросов с помощью VBA применяют только для решения более сложных или уникальных задач.

Как работает VBA в Excel

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

Макрос можно создать тремя способами:

• записать с помощью макрорекордера;
• написать код самостоятельно в редакторе Visual Basic в Excel;
• скопировать готовый код — например, из разных справочников или форумов в интернете — и адаптировать его к своим задачам.

Запись макроса с помощью рекордера работает так. Пользователь, находясь в Excel-файле, нажимает кнопку записи и выполняет все действия, которые хочет включить в макрос, — например, создаёт оглавление.

После остановки записи Excel «переводит» каждое выполненное действие на язык VBA и выдаёт алгоритм в виде кода. При необходимости пользователь может зайти в редактор Visual Basic (Alt + F11) и отредактировать полученный код вручную — дописать самостоятельно или удалить некоторые фрагменты.

В этом же редакторе пишут код с нуля или вставляют готовые фрагменты и дорабатывают их.

Чаще всего пользователи комбинируют все три способа создания макроса. Например, чтобы не писать код с нуля, они начинают с записи макрорекордером или вставляют в редактор готовые части кода. А уже потом «допиливают» код под свои потребности.

Ещё для написания макросов удобно использовать нейросети. Например, можно подробно описать свою задачу в ChatGPT, и он выдаст готовый код. В некоторых случаях полученный код можно использовать даже без доработки.

Нужно ли быть программистом, чтобы разобраться с VBA в Excel

Язык VBA довольно дружелюбен по отношению к пользователю. Например, он автоматически выставляет регистр для названий свойств, методов и команд — в отличие от языка M, встроенного в Power Query. Также в нём много инструментов для поиска ошибок в коде и для его отладки.

Человек, который хорошо ориентируется в Excel, может легко освоить VBA, даже если до этого никогда не программировал. Например, с помощью инструмента для записи макросов. Он несовершенен, но позволяет посмотреть, как называются разные действия программы на языке VBA.

Ещё в работе с макросами помогает то, что в интернете можно найти много готовых примеров кода, которые можно изучать и адаптировать к своим задачам.

Главное о VBA в 5 пунктах

• VBA — язык программирования, созданный компанией Microsoft. С помощью VBA записывают макросы, которые выполняют заданную последовательность действий в программах после нажатия кнопки меню или сочетания клавиш.
• VBA используют, чтобы автоматизировать повторяющиеся действия пользователей, расширять возможности программ и объединять данные файлов разных программ MS Office.
• В Excel для решения большинства задач, которые раньше выполняли с помощью макросов, сейчас можно использовать надстройки Power Query и Power Pivot.
• Можно написать код на языке VBA с нуля, можно записать его с помощью макрорекордера, а можно взять готовый и при необходимости доработать.
• VBA — довольно простой язык. Если у человека нет опыта программирования, но он хорошо разбирается в Excel, освоить этот язык будет легко.