Личная книга макросов (PERSONAL.XLSB) Excel – руководство

Я начинал свой путь в программировании с простых макросов, записанных через встроенный макрорекордер Excel. Это было удобно: нажал кнопку — Excel сам записывает действия. Со временем я узнал о личной книге макросов — способе сохранять свои процедуры так, чтобы они были всегда под рукой, в любом файле. Это стало большим шагом вперёд.

Личные макросы

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

Я использую макросы ежедневно. Они значительно ускоряют работу и позволяют сосредоточиться на сути задачи, а не на рутине. Каждый такой макрос создаётся под конкретные нужды: под формат моих таблиц, под логику, с которой я работаю. Это и есть личные макросы — те, что используются конкретным пользователем в его собственной среде.

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

Возникает вполне логичный вопрос: где в Excel можно хранить личные макросы, чтобы они были всегда доступны? Как сделать так, чтобы они запускались в любой книге Excel, автоматически открывались при запуске, и при этом не мешали работе с документами?

Решением становится так называемая личная книга макросов. Это специальный файл, который:

• создаётся один раз,
• открывается и остается открыт, пока открыта хотя бы одна таблица Excel,
• при этом не отображается среди открытых книг (остаётся в фоне)
• сохраняет в себе пользовательские макросы, функции и другие данные.

В следующем разделе расскажу, как её создать и правильно настроить.

Создать личную книгу макросов

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

Например, можно записать изменение цвета заливки ячейки или изменение ширины столбца. Можно и вовсе ничего не выполнять и остановить запись сразу после старта записи. Главное — не само действие, а запуск механизма создания. Excel автоматически создаст файл PERSONAL.XLSB, добавит модуль в редактор VBA и предложит сохранить изменения при выходе из программы.

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

Сохранить макрос в личной книге

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

Второй способ — напрямую вставить код через редактор VBA. Для этого нужно открыть редактор (Alt+F11), найти в списке проектов PERSONAL.XLSB, добавить новый модуль (если нужно), и вставить в него процедуру. Здесь удобно хранить как простые, так и многофункциональные макросы, сгруппированные по задачам.

Каждое добавление макроса — это обновление файла PERSONAL.XLSB. Он сохраняется при выходе из Excel, поэтому важно при закрытии программы соглашаться на предложение о сохранении книги. В противном случае новые макросы не будут записаны в файл.

Где находится файл личной книги макросов PERSONAL.XLSB

После первого сохранения файл личной книги макросов будет храниться в специальной системной папке Excel. Файл называется PERSONAL.XLSB и лежит в директории автозагрузки Excel. Макросы, записанные в личную книгу макросов, вставляются именно в этот файл. Именно оттуда он автоматически загружается при каждом запуске Excel.

Этот путь можно узнать через редактор VBA или через параметры Excel — он зависит от версии операционной системы и самого MS Excel.

Путь к этой папке в операционной системе Windows чаще всего выглядит так:

C:\Users\Имя_пользователя\AppData\Roaming\Microsoft\Excel\XLSTART

В этом расположении Excel ищет и открывает все файлы, которые должны запускаться при старте. PERSONAL.XLSB загружается оттуда в фоне, оставаясь скрытым. Он не отображается среди обычных документов, хотя технически считается открытым. Если зайти в редактор VBA (Alt+F11), можно увидеть, что файл открыт и активен.

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

Универсальный путь к папке через переменную окружения %appdata% (вставить в путь проводника Windows):

%appdata%\Microsoft\Excel\XLSTART

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

Важно помнить: файл PERSONAL.XLSB должен всегда находиться именно в этой папке автозагрузки. Если он будет перемещён, переименован или удалён — личные макросы перестанут загружаться при открытии Excel.

Как открыть и сделать видимой личную книгу макросов

Личная книга макросов — это обычный файл Excel с расширением .xlsb. Он ничем не отличается от других рабочих книг: у него есть листы, можно добавлять данные, формулы, сохранять таблицы. Отличие только в том, что Excel при запуске скрывает этот файл по умолчанию.

Файл персональной книги макросов действительно открыт каждый раз, когда запускается Excel, но остаётся в фоновом режиме. Чтобы его отобразить, нужно перейти на вкладку «Вид» и нажать «Отобразить». В списке окон будет PERSONAL.XLSB. После выбора книга станет видимой, как и любая другая.

Если отобразить книгу PERSONAL.XLSB, с ней можно работать, как с обычной книгой: вводить данные на лист, вставлять справочные таблицы, сохранять заметки, использовать формулы. Всё это сохранится вместе с макросами. Хотя чаще всего в этой книге не хранят данные, технически это не запрещено — просто не принято.

Когда работа завершена, PERSONAL.XLSB можно снова скрыть через ту же команду «Скрыть». Excel запомнит это состояние и в следующий раз откроет файл в скрытом виде. Это удобно: макросы доступны, но книга не мешает на экране.

Редактировать макросы внутри PERSONAL.XLSB можно через редактор VBA (Alt+F11), как и в любой другой книге. При необходимости можно добавить модули, удалить процедуры, изменить код — всё это делается напрямую внутри файла.

Скопировать код из личной книги макросов

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

Для этого нужно

1. открыть редактор VBA (Alt+F11),
2. найти в списке проектов PERSONAL.XLSB, а в нём – список модулей, где хранятся макросы.
3. скопировать нужные функции и процедуры:
   • выделяем нужный код,
   • копируем Ctrl+C и
   • вставляем (Ctrl+V) в модуль другой книги.

Если в целевой книге модуля ещё нет, его можно добавить через меню «Вставка» → «Модуль».

Аналогично работает и обратный процесс: если есть готовый макрос в другой книге Excel, его можно вставить в личную книгу макросов, чтобы он был всегда доступен.

Вместо копирования отдельных процедур можно перенести целый модуль. Перенесите обычным перетаскиванием модуль в структуре проектов редактора VBA — например, из книги Book1.xlsm в PERSONAL.XLSB. Так сохраняется вся структура кода, включая вспомогательные процедуры и комментарии. При этом исходный код останется, но в результирующей книге будет создан идентичный модуль.

Если макрос нужно перенести на другой компьютер, я копирую файл PERSONAL.XLSB из системной папки автозагрузки (XLSTART) и переношу его в ту же папку на новом устройстве. Главное — убедиться, что Excel настроен на автозагрузку из этой папки, и макросы не блокируются настройками безопасности.

Удалить макрос из личной книги макросов

Если макрос в личной книге больше не нужен, его можно удалить напрямую через редактор VBA. Я обычно открываю редактор (Alt+F11), нахожу в проекте PERSONAL.XLSB нужный модуль, выбираю процедуру и просто удаляю её код. Если модуль содержит только один макрос, можно удалить весь модуль: правый клик по модулю → «Удалить модуль». Excel предложит сохранить копию на случай, если код может еще понадобиться. Рекомендую не пренебрегать возможностью.

Удаление макроса не требует пересохранения файла вручную — Excel спросит, сохранить ли изменения в PERSONAL.XLSB при выходе. Если согласиться, изменения вступают в силу.

Удалить личную книгу макросов

Если нужно удалить не отдельный макрос, а всю личную книгу:

1. закройте Excel,
2. перейдите в папку, где лежит файл PERSONAL.XLSB,
3. удалите его как обычный файл.

После этого Excel перестанет открывать личную книгу при запуске, и все сохранённые в ней макросы станут недоступны.

Заключение

Личная книга макросов — это удобное решение, если вы хотите, чтобы ваши макросы были доступны в любой книге Excel. Она позволяет записывать и хранить персональные процедуры, ускоряя повседневную работу. Когда функций и процедур немного, это идеальный вариант.

Но однажды мне перестало хватать возможностей встроенного макрорекордера и личной книги макросов. Процедур и функций стало слишком много, и вызывать их через линейный список стало затруднительно. Хотелось более user-friendly решение — с четкой иерархией процедур, меню, кнопочками, чекбоксами и т.д. :)

Так началась работа над надстройкой !SEMTools для Excel — инструментом, который вырос из потребности автоматизировать повседневные задачи и сделать Excel удобнее для себя и других.