Содержание:
Если вы еще не знакомы с макросами в Excel, то я вам немного завидую. Ощущение всемогущества и осознание того, что ваш Microsoft Excel можно проапгрейдить практически до бесконечности, которое придет к вам после знакомства с макросами, — приятное ощущение.
Однако эта статья для тех, кто уже «познал мощь» и начал использовать макросы (зарубежные или написанные самостоятельно — не важно) в своей повседневной работе.
Макрос — это код (несколько строк) на языке Visual Basic, который заставляет Excel делать то, что вам нужно: обрабатывать данные, формировать отчет, копировать и вставлять множество повторяющихся таблиц и т. д. Вопрос в том, где хранить эти несколько строк кода? Ведь от того, где хранится макрос, потом будет зависеть, где он сможет (или не сможет) работать.
Если макрос решает отдельную локальную задачу в отдельном файле (например, обрабатывает внесенные в конкретный отчет данные таким образом), то логично сохранять код внутри этого же файла. Без вопросов.
А если макрос должен быть относительно универсальным и нужным в любой книге Excel – как, например, макрос преобразования формул в значения? Почему бы не копировать его код Visual Basic каждый раз в каждую книгу? Кроме того, рано или поздно практически любой пользователь приходит к выводу, что было бы неплохо сложить все макросы в одну коробку, т.е. иметь их всегда под рукой. А может быть, даже запускать не вручную, а с помощью сочетаний клавиш? В этом случае Персональная книга макросов может оказаться очень полезной.
Как создать личную книгу макросов
По факту, Персональная книга макросов (ЛКМ) представляет собой обычный файл Excel в формате двоичной книги (Персональный.xlsb), который автоматически открывается в скрытом режиме одновременно с Microsoft Excel. Те. когда вы просто запускаете Excel или открываете любой файл с диска, на самом деле открываются два файла — ваш и Personal.xlsb, но второй мы не видим. Таким образом, все макросы, которые хранятся в ЛКМ, доступны для запуска в любой момент, пока открыт Excel.
Если вы никогда не пользовались ЛКМ, то изначально файла Personal.xlsb не существует. Самый простой способ ее создания — записать с помощью диктофона какой-нибудь ненужный бессмысленный макрос, но в качестве места его хранения указать «Личную книгу» — тогда Excel будет вынужден автоматически создать ее для вас. Для этого:
- Нажмите разработчик (Разработчик). Если вкладки разработчик не видно, то его можно включить в настройках через Файл – Параметры – Настройка ленты. (Главная страница — Параметры — Настройка Лента).
- На вкладке Дополнительно разработчик нажмите на Запись макроса (Запись макроса). В открывшемся окне выберите Персональная книга макросов. (Личная рабочая тетрадь макросов) как место для хранения написанного кода и нажатия OK:
- Остановить запись кнопкой Остановить запись (Остановить запись) таб разработчик (Разработчик)
Проверить результат можно, нажав на кнопку Визуальный Бейсик прямо здесь, на вкладке. разработчик – в открывшемся окне редактора в левом верхнем углу на панели Проект — Проект VBA наш файл должен появиться ЛИЧНОЕ. XLSB. Его ветка, которую можно расширить плюсиком слева, дойдя до Module1, где хранится код бессмысленного макроса, который мы только что записали:
Поздравляем, вы только что создали свою личную книгу макросов! Только не забудьте нажать на кнопку сохранения с дискетой в левом верхнем углу панели инструментов.
Как использовать персональную книгу макросов
Тогда все просто. Любой макрос, который вам нужен (например, фрагмент кода, начинающийся с ниже и окончание End Sub) можно безопасно скопировать и вставить либо в Module1, либо в отдельный модуль, добавив его предварительно через меню Вставка – Модуль. Хранить все макросы в одном модуле или размещать их в разных — только дело вкуса. Это должно выглядеть примерно так:
Запустить добавленный макрос можно в диалоговом окне, вызываемом кнопкой Макрос (Макросы) таб разработчик:
В этом же окне, нажав кнопку параметры (Параметры), вы можете установить сочетание клавиш для быстрого запуска макроса с клавиатуры. Будьте внимательны: сочетания клавиш для макросов различают раскладку (или английский язык) и регистр.
Помимо обычных макропроцедур в Личной Книге также можно хранить пользовательские макрофункции (UDF = определяемая пользователем функция). В отличие от процедур, код функции начинается с оператора Функцияor Общественная функцияи заканчивается End Function:
Код необходимо скопировать аналогичным образом в любой модуль книги PERSONAL.XLSB, после чего функцию можно будет вызвать обычным способом, как и любую стандартную функцию Excel, нажатием кнопки fx в строке формул и выборе функции в окне Мастера функций в категории User Defined (Определяемые пользователем):
Примеры таких функций можно найти в большом количестве в Интернете или здесь на сайте (сумма прописью, примерный поиск текста, ВПР 2.0, преобразование кириллицы в транслитерацию и т.д.)
Где хранится персональная книга макросов?
Если вы воспользуетесь Личной Книгой Макросов, то рано или поздно у вас возникнет желание:
- делитесь накопленными макросами с другими пользователями
- скопировать и перенести Личную Книгу на другой компьютер
- сделать резервную копию
Для этого вам нужно будет найти файл PERSONAL.XLSB на диске вашего компьютера. По умолчанию этот файл хранится в специальной папке автозагрузки Excel под названием XLSTART. Итак все, что нужно – это добраться до этой папки на нашем ПК. И тут возникает небольшая сложность, ведь расположение этой папки зависит от версии Windows и Office и может отличаться. Обычно это один из следующих вариантов:
- C:Программные файлыMicrosoft OfficeOffice12XLSTART
- C:Документы и настройкиКомпьютерДанные приложенияMicrosoftExcelXLSTART
- С: Пользователиимя-вашего-аккаунтаAppDataRoamingMicrosoft ExcelXLSTART
Альтернативно вы можете запросить расположение этой папки у самого Excel с помощью VBA. Для этого в редакторе Visual Basic (кнопка Визуальный Бейсик таб разработчик) открыть нужное окно немедленная сочетание клавиш Ctrl + G, введите команду ? Приложение.StartupPath и нажмите на Enter:
Полученный путь можно скопировать и вставить в верхнюю строку окна Проводника в Windows и нажать кнопку Enter — и мы увидим папку с файлом нашей Личной Книги Макросов:
PS
И несколько практических нюансов вдогонку:
- при использовании Персональной книги макросов Excel будет работать немного медленнее, особенно на слабых ПК
- стоит периодически очищать Личную Книгу от информационного мусора, старых и ненужных макросов и т.п.
- у корпоративных пользователей иногда возникают трудности с использованием Личной книги, т.к. это файл в скрытой папке системы
- Что такое макросы и как их использовать в работе
- Полезность для программиста VBA
- Тренинг «Программирование макросов на VBA в Microsoft Excel»