В этой книге мы не рассматриваем тему макросов подробно: ей посвящены отдельные издания (я рекомендую книгу «Профессиональное программирование на VBA», подробнее о ней — чуть ниже в ). Скажем несколько слов, чтобы у вас сложилось представление о макросах в первом приближении, если вы еще с ними не сталкивались.

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

Примеры можно приводить долго: сравнивать два файла, поменять шрифт у всех диаграмм на всех листах в десяти документах, исправить некорректно выгруженные данные из 1С, создать из одного сводного файла много отдельных за несколько секунд автоматически, создать оглавление — отдельный лист со ссылками на все остальные (пример такого макроса будет ниже) и многое другое.

Visual Basic был выпущен в 1991 году и стал стандартным языком для написания сценариев в приложениях Microsoft.

Поскольку макрос — это программа, а программа теоретически может выполнить вредоносные действия, с определенного момента Microsoft стал выделять отдельные форматы для документов Excel с макросами. Обычный формат последних версий Excel — XLSX — не предусматривает сохранение макросов. В нем можно их создать, но после этого придется сохранить в одном из специальных типов с макросами. Это XLSM или XLSB.

В версиях Excel 97–2003 файлы были с расширением xls и не имелось разделения на книги с макросами и без.

Создать макрос можно двумя способами: ввести код (или скопировать фрагменты готового кода из книг или интернета) или записать макрос с помощью средства записи (макрорекордера).

После нажатия на эту кнопку начнется запись.

Пока макрос не остановлен, все ваши действия будут фиксироваться.

Запись макроса — это своего рода магнитофон: все действия, которые вы производите, записываются на «пленку» в виде кода VBA.

Запись макроса имеет свои ограничения. Как правило, записанный макрос приходится дорабатывать, оптимизировать, не все действия можно записать в принципе (невозможно своими руками создать ссылку «на все листы в книге, сколько бы их ни было» — вы можете создать ссылки на одну, десять или сто книг, но это будет фиксированная история, в записи макросов нельзя использовать циклы или оперировать переменными).

Но этот инструмент очень удобен для обучения и знакомства с инструкциями: если вы не знаете, как называется тот или иной объект или свойство, запишите макрос, проделав действия с ним, и посмотрите на результат.

Актуальность макросов несколько снизилась после того, как в Excel появилась надстройка Power Query: если раньше для обработки данных и решения нетривиальных задач (собрать все книги Excel из одной папки, объединить данные, очистить от лишнего, поменять форматы и структуру при необходимости, построить на основе этого отчет со сводными таблицами, диаграммами и прочим) приходилось писать макрос, то сейчас есть более дружелюбный интерфейс Power Query, позволяющий автоматизировать многие подобные задачи, не прибегая к написанию кода.

Наглядно об этом говорит вот эта иллюстрация из книги «Приручи данные с помощью Power Query в Excel и Power BI» (Кен Пульс, Мигель Эскобар).

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

Давайте рассмотрим пример — решение простой задачи, которую нельзя быстро реализовать встроенными средствами.

В Excel (как и во многих других приложениях) можно вставить ссылку на место в документе с помощью сочетания Ctrl + K.

После чего выбрать слева «Связать с» — «Место в документе».

Вводим отображаемый в ячейке текст ссылки и адрес ячейки, внизу в списке выбираем лист.

И получаем ссылку для перехода на соответствующее место в документе.

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

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

Хотя, как правило, большинству хватает встроенных инструментов и функций для решения рабочих задач (тем более когда есть Power Query), но иногда могут возникнуть задачи вроде такой, для которой придется писать (или искать в Сети, на форумах готовый) макрос.

Следующий код создает новый лист с именем «Оглавление» и добавляет в первом столбце ссылки на все остальные листы в книге.

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

Личная книга — это книга, которая открывается в Excel всегда, она нужна, чтобы хранить те макросы, которые могут понадобиться вам в любой момент при работе с любой книгой, а не с отдельным файлом. Чтобы она у вас появилась, достаточно начать запись макроса (можно будет ее сразу закончить) и выбрать в списке «Сохранить в» соответствующий пункт.

После этого будет создана Личная книга макросов — вы увидите ее в редакторе Visual Basic (его можно открыть сочетанием Alt + F11).